asked on

Paging in the database

I have complex SQL statement to produce a result set which is needed paging to display in the web interface. I don't want every time the user request another page, it runs the SQL again. I just think the solution to create a persistent table for every session and then delete the table in the Page_unload event (I used ASP.NET)

Any common practice to do in this situation? I am wondering how google or yahoo does the search paging on so many users queries

Thanks

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Torus

ASKER

well, my point is I have quite complex statements which will call another stored proc also. In the example, it has just simple select statement which does not executed much time. If my complex statements is applied, it takes time to run again every time chagne the page.

chapmandew

Right...there is no getting around having to rerun the statement,but it does prevent you from having to go through the results each time to determine which page to display.

If you are worried about continuously running it, can you try putting it into a datatable.

Torus

ASKER

What do you mean datatable? you mean create a new table in the database to store the result?

If so, I have already thought to put the result to a persistent table. Delete it after page_unload. But if many users query at the same time, many persistent tables will be created at run time to store different result set for different users. I just want to know if there are other good solutions getting round of it.

I am not sure if google or yahoo statements are complex, or they also rerun the statements every time changing the page.

chapmandew

http://www.aspnettutorials.com/tutorials/controls/data-table-vb.aspx

Torus

ASKER

I got what you mean? You mean use the System.data.DataTable. But I need to save all the records in the DataTable, rite? it is just a saving all the data in the DataView only.

chapmandew

Yeah....if you can save the records it would keep you from having to go to the db to get them each time.

Torus

ASKER

Well, if doing so, I just put all the records to the memory only. Why need to keep going to the db to get them each time?

chapmandew

yep

Torus

ASKER

hmm.... really not good since my records may over 30000. If no other good solution, I prefer creating persistent table at run time.

chapmandew

Sounds good...make sure you have a unique identifier for your session to put into the table....maybe a GUID.

Torus

ASKER

I try to use session ID in the web. What my most concern is the page unload not always work so that can't delete the table after use. The drawback of this solution may need a schedule job to delete the table.

chapmandew

Yes, you probably will...remove anything older than say....1 hr.

Torus

ASKER

so , can't find a better solution.

chapmandew

NOt w/ bringing back that much data.

Torus

ASKER

well, can't actually. up to user input searching citeria.

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Torus

ASKER

ScottPletcher, your method is actually paging. I didn't see any tricky on your method. As I said, because my SQL statement is quite complex, I hope that the query is not executed again every press the next or previous page