getting specific records: architecture question

This is out of my league. I'm hoping to get some good advice from someone experienced in the area. My inquiry is how to best handle large amounts of records, say 500,000 records or so. I am web programming and can't send all this info from server to client.

Part of the problem is the manner in which the data gets stored. I cannot calculate what records I need to get for a distant page (i.e. if 10 items per page then where is my data getting page #512).

These are the very first five (5) records. First row is the primary key.

144510V52 vials1V58/10/20073204510V102 vials2V58/10/20073254510V52 vials1V58/15/20073264510V102 vials2V58/15/20073274510V402 vials8V58/15/20073

Because records 1 through thirteen had been deleted, the primary keys for the first (5) are no longer 1, 2, 3, 4, 5. Had this been the case, a person could easily retrieve page 512, by mathematical calculation.

Page 1 would have been Records 1..10# 2 = > 11..20# 512=> 5111..5120.

I already have a program that loads the entirety into an arraylist; then picks out the page of data from the arraylist location. I could rewrite things so that a temporary SQL Table is created – but I don't know is that a good idea?

All advice welcome - TIA

Check this article here:http://www.dotnetjunkies.com/Tutorial/975BE770-E5DC-4610-870B-A82BDB9B8845.dcik

The proc shows how to custom page and retrieve only the records from the particular page. The downside of it is, although you are only retrieving only 20 or 30 records, the proc is executed for each page.

|||

Hello DougV,

I personally prefer the temporary table idea. Since you have so many rows fetching all the rows in the ArrayList seems like a waste.

You can write a Stored proc that will fetch only the requested page, store the data in temp table and return the temp table.

If you are using SQL Server there is FETCH T-SQL command that you can use to achieve what you need

(http://msdn2.microsoft.com/en-us/library/aa258896(SQL.80).aspx)

Hope this helps

regards,

G

|||

Dinakar, I looked at your link and I think it describes my situation perfectly.
To Quote:"the most efficient method for implementing custom paging
would be to only return the rows of data from the data source that
are to be displayed on the currently displayed page."

But it still seems like the Server would be working hard because
the SQL database would have to generate a temp table which still contains everything.
Wouldn't that mean writing all those half million records each time? That's the part
I don't understand; maybe it is really not physically writing those all out.
Or maybe I am not reading that query correctly? Perhaps I am underestimating
the power of the Database to carry out this task.

G, If you have an arraylist and it had to memory page to disk would not that
still be about the same? Again, I'm really out of my league here.

G, Yes I am using SQL Server. I did not know about the "Fetch" command.
So far it seems like the most direct and "straight to the data" method.
Only drawback might be if I try to deliver/share/sell my code to someone else who is
using a different database and cannot use "Fetch" command. I was trying
to make a template that would work somewhat universally (I'm working through DAL).

Seems my choice should be creating something with "Fetch" command. However, if I knew
that creating a temp table with a million records takes less than a half
second, I'd probably change my mind about that.

Thanks for the replies - I have some good options to consider .

|||

DougV:

Dinakar, I looked at your link and I think it describes my situation perfectly.
To Quote:"the most efficient method for implementing custom paging
would be to only return the rows of data from the data source that
are to be displayed on the currently displayed page."

But it still seems like the Server would be working hard because
the SQL database would have to generate a temp table which still contains everything.
Wouldn't that mean writing all those half million records each time? That's the part
I don't understand; maybe it is really not physically writing those all out.
Or maybe I am not reading that query correctly? Perhaps I am underestimating
the power of the Database to carry out this task.

Pretty much. If your proc is going to be called often, you can perhaps create a physical table and use that for your paging. You can truncate the table every night.

Also, if you google for "stored proc + custom paging" and drill through some articles, you might find something that only queries those 20-30 records. I just posted the first link I found when I googled.