Results 1 to 3 of 3
Thread: Page Spanning which is best?
Feb 25, 2005, 11:06 #1
Page Spanning which is best?
I read this article:
I'm developing a multi-user community site which will host many hundreds of thousands of users. The paging will be implemented in search pages (user search), mailbox, etc etc.
What are your thoughts on this article? I was told that using temp table would actually be more scalable, but the author of the article seems to rule it out completely.
Feb 25, 2005, 14:19 #2
Temp table scalable? No, it is not scalable at all. If you use global temp tables (##table) then all threads must queue for access as the table gets locked. So you have absolutely no concurrency.
If you have #local temp tables then you can't use them from several sprocs, and they must be continually recreated and so this is just simply a performance drain.
1) Don't do paging if you can avoid it. If you are doing an alphabetical list of members, for example, offer premade pages like: Aaa - Ard | Arf - Asi etc... Build the keys used in the range in a job on the server, and then just dump all rows in the range to the user.#
2) Consider implementing caching in the webserver. Dump the entire range to a web server DataSet or HashList or whatever and cache it there, the middle tier handing off pages of its data to service requests.
3) If you can't avoid it, consider dumping a larger range to the client and do client side processing. This is what we used to do when client - server was all the rage.
4) Now if you really want to do SQL Server paging it is not too difficult:
a) forget about cursors. they are too slow
b) forget about temp tables as noted above
c) do not use dynamic SQL - you need to have the server cache the query plans
d) use a table variable
e) severely restrict the order by options your application can use when querying the data, for each different sort you will need to store a cache id list. If necessary do client side sorting
f) use code like the code posted in the article above, in the last comment about "false assertion".
Feb 26, 2005, 16:01 #3
- Join Date
- Feb 2005
- 0 Post(s)
- 0 Thread(s)
I would say server paging is a very good solution because, ADO.NET for example is quite slow in fetching the data to client (I guess you are using .NET since you posted link to ASP.NET article), co I would limit the data your stored procedure returns to as few as needed. You can implement paging using a subquery (select top 1000 ... where id not in (select top 900 from ...)) where its preformance degrades with the page number (the higer the page number the slower the stored procedure) but it surely is faster then fetching 1000 rows to client.
And most users don't actually go to page 100 in search so it is not so big issue.Martin Pernecky