2

I have a list of int that's stored in the DB in the form of a string with commas in between (4345,324,24,2424,64567,33...). This string could become quite large and contain 2-3 thousand numbers. It's stored in the DB and used quite frequently.

I'm thinking that instead of reading it from the DB every time it's needed, it'd be better to store it in the session after it's loaded the first time.

How much memory would a list of 1,000 int require? Does the memory size also depend on the int itself such that storing a larger int (234,332) takes more space than a smaller int (544)?

Is it going to be better to read once and store in the session at the cost of memory space or better to read often and discard from memory after render.

Thanks for your suggestions.

Alex Aza
  • 76,499
  • 26
  • 155
  • 134
frenchie
  • 51,731
  • 109
  • 304
  • 510

6 Answers6

8

I think you are heading in wrong direction. Storing in DB will likely be a better option, not in comma separated format, but as a table of int values.

Storing data in session will reduce scalability significantly. You might start having OutOfMemory exception and wondering why this is happening.

So my suggestion is read from DB when needed, apply appropriate indexes and it will be very fast.

The way you are heading is:

Day #1, 1 user - Hmm, should I store data in Session, why not. Should work fast. No need to query DB. Also easy to do.

Day #10, 5 users - Need to store another data structure, will put this to the session too, why not? Session is very fast.

Day #50, 10 users - There is a control that is haeavy to render, I will make it smart, render once and than put to the Session, will reuse it on every postback.

Day #100, 20 users - Sometimes the web site slow, don't know why. But it is just sometimes, so not a big deal.

Day #150, 50 users - It's got slow. Need better CPU and memory? We need to buy a better server, the hardware is old.

Day #160, 60 users - Got a new server, works much faster. Problem solved.

Day #200, 100 users - slow again, why? This is the newest the most expensive server!

Day #250, 150 users - application pool is getting recylced all the time. Why? OutOfMemoryException? what is this? I will google.

Day #300, 200 users - Users complain, we lose customers. I read about WinDbg, need to try using it.

Day #350, 200 users - Should we start using network load balancing, we can buy two servers! Bought server, tried to use, didn't work, a lot of dependencies on Session.

Day #400, 200 users - Can't get new customers, old customers go away. Started using WinDbg found out that almost all the memory is used by Session.

Day #450, 200 users - Starting a big project called 'Get rid of Session'.

Day #500, 250 users - The server is so fast now.

I've been there seen that. Basically my advice - don't go this way.

Alex Aza
  • 76,499
  • 26
  • 155
  • 134
  • I tend to disagree. The memory usage (about 4K) is nothing compared to the streams, buffers, I/O cost, etc of storing it in the database. It probably is a wrongheaded approach, but not for scalability reasons. – Chris Shain Jun 08 '11 at 02:29
  • @Chris Shain whatever number you got, multiply the users number. This is 100% scalability issue. – Alex Aza Jun 08 '11 at 02:31
  • I was assuming he is going to share the same array across multiple users. If not, as the use of Session implies, then yeah, it won't scale. But neither will querying the database each time he would have used the array. – Chris Shain Jun 08 '11 at 02:37
  • Is 4K * 1,000 users 4MB? If the server has 16GB of RAM, will scalability really be an issue? @Chris, how expensive are those data transfers going to be; looks like 4MB for 1,000 users might be worth the cost if the downside is scalability on 1,000 users making constant reads. – frenchie Jun 08 '11 at 02:41
  • @Chris Shain - database engine is the one that `kind of` guarantees certain level scalability. It was designed to STORE data and to retrieve data efficiently. – Alex Aza Jun 08 '11 at 02:42
  • @frenchie - Don't forget that if this ASP.NET and 32 bit IIS, you have around 800MB to use. If it is 64bit, you have much more memory to use, but... you web site will slow down. – Alex Aza Jun 08 '11 at 02:45
  • @Alex, my goal is to get the first build off the ground. I decided to monitor the memory usage problem from day 0 onward and to make changes to this list if anything seems slow. One of the change I have in mind is switching from InProc session to storing the session in the DB. – frenchie Jun 08 '11 at 22:58
  • @frenchie - if you are thinking about using InProc Session, it would probably make even more sense to store the data directly to a table. If you store data in the table than it will be `Data from memory => Database` if you use Session with InProc it will be `Data from memory => Serialization => Database`. Serialization might become a bottleneck if the data volume is high. But I agree that using `InProc` will alleviate scalability issue. – Alex Aza Jun 08 '11 at 23:30
4

An int in C# is always 4 bytes (no matter what the value). A list of 1,000 ints is therefore ~4,000 bytes. I say approximately because the list structure will add some overhead. A few thousand ints in a list shouldn't be a problem for a modern computer.

Nik
  • 7,113
  • 13
  • 51
  • 80
2

I would not recommend storing it in the session, since that's going to cause memory pressure. If you have a series of integers tied to a single record, it sounds like you have a missing many to one relationship - why not store the ints in a separate table with a foreign key to the original table?

Bob Palmer
  • 4,714
  • 2
  • 27
  • 31
1

Integers are of a fixed size in .NET. Assuming you store it in an array instead of a List (since you are probably not adding to or removing from it), it would take up roughly 32 bits * the number of elements. 1000 ints in an array = roughly 32000 bits, or a little under 4 KB.

Chris Shain
  • 50,833
  • 6
  • 93
  • 125
0

An int usually takes 32 bits (4 bytes), so 1000 of them would take about 4KB.

It doesn't matter how large the number is. They're always stored in the same space.

trutheality
  • 23,114
  • 6
  • 54
  • 68
0

Is this list of int's unique to a session? If not, cache it at the server level and set an expiration on it. 1 copy of the list.

context.Cache.Add(...

I do this and refresh it every 5 minutes with a large amount of data. This way it's pretty "fresh" but only 1 connection takes the hit to populate it.

Matt Dawdy
  • 19,247
  • 18
  • 66
  • 91