You are here: Re: Temp Table Faster? « MsSQL Server « IT news, forums, messages
Re: Temp Table Faster?

Posted by Erland Sommarskog on 10/08/57 11:35

(wackyphill@yahoo.com) writes:
> If you were doing paging of results on a web page and were interested
> in grabbing say records 10-20 of a result set. But also wanted to know
> the total # of records in the result set (so you could know the total #
> of pages in the set).
>
> Would it be better to query the DB table 2X. Once for Count(*). And
> again for the records for the current page?
>
> Or better to create a temp table, select the records into it, and then
> get count(*) and the page results from the temp table?
>
> I saw an example in a book that made a temp table to do this and to me
> it seemed like it would be slower. I don't get the reason for a temp
> table. Anyone have any ideas?

A temp table could be slower because of recompilations.

An alternative is to use a permanent table, that would have some session
key and an IDENTITY column (in SQL 2000). When the user makes his first
search, you get all data into that table. Then as he pages on, you retrieve
the rows from this table. This means you don't have to redo the query for
subsequent pages, but can get it from the table. This is likely to give
better performance, and another advantage: a fixed result. If the result
can change as the user browse, he may miss a row that initially was row
101, but now is row 100.

Finally, don't design pages where the user only can get 10 rows at a
time. I hate those. Give me at least 100 at a time.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация