Where should I store a million-record array?

Question

I have a question in the field of optimization and application design. I am building a web application using asp.net and sql server.

In one of my screens I must perform an action that generates a random number of user ids. I present the viewer with some statistics about the selected users. If the viewer likes the statistics I want to save them.

So basically I need to save the temporary random data, and if user likes it keep it.

Should I store the generated ids in the database or should I store them in the session?

The title is very misleading and not relevant to your real question. — Shadow The GPT Wizard, Jun 22 '11 at 06:34
It's relevant, because the size of the array is implied as big. — Gleno, Jun 22 '11 at 06:35

score 3 · Accepted Answer · edited May 23 '17 at 12:27

Well, since you are generating random ids, you are using some kind of pseudorandom generator. Have you considered the possibility of just storing the seed for that generator? I've recently had a similar issue. In fact, very similar. Have a look:

My Post about Random(int seed)

EDIT: In comments you suggest you want to do much of this on the SQL server. Have a look at the following post. In addition you may want to consider the special case of new user being added whilst your admin or whatever ponders if he "likes" to save this or not. In that case you'd need to additionally store the number of users when the request was made, and adjust your random selection function accordingly. In the even more special case of removed user, this approach, admittedly, is useless.

Seeding SQL

I am actually using an sql query to generate a random Id, in t-sql you can do so by adding the " order by newId " clause. — vondip, Jun 22 '11 at 06:36

score 0 · Answer 2 · answered Jun 22 '11 at 06:36

It depends on your case and requirements. If you store the data in the session, then that is fine, but you will lose the data once the session is abandoned, or ended. But if that is not important, meaning that you don't want to keep the data there forever, then storing in the session (temporary) is better. But you will need to look into performance issues, specially if multiple users do the same thing at once, that will decrease performance.

If you choose to store in a database, then that will also work but you will need to decide on having ViewState enabled or disabled, again for the sake of performance.

score 0 · Answer 3 · answered Jun 22 '11 at 07:19

0

if i have to do so , i store data in temp table in sql which will be drop on every page load event and recreate, i show the data in a grid where from selected user id can be deleted and saved

answered Jun 22 '11 at 07:19

Vikky

752
3
15
35

Where should I store a million-record array?

3 Answers3