SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 02:19 PM
| ||||
| ||||
 Temp Table Faster? 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?      |
|
02-29-2008, 02:19 PM
| |||
| |||
| Re: Temp Table Faster? wackyphill@yahoo.com wrote: > 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? Take a look here: http://www.aspfaq.com/show.asp?id=2120 -- David Portas SQL Server MVP -- |
|
02-29-2008, 02:19 PM
| |||
| |||
| Re: Temp Table Faster? (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/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
|
02-29-2008, 02:19 PM
| |||
| |||
| Re: Temp Table Faster? Erland Sommarskog wrote: > > 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 Depending on the actual practical needs, yet another variation of this technique is to save only the primary keys into the permanent table. Requires less disk space and displays changes made after the PK set was materialized. |
|
02-29-2008, 02:19 PM
| ||||
| ||||
| Re: Temp Table Faster? Alexander Kuznetsov (AK_TIREDOFSPAM@hotmail.COM) writes: > Erland Sommarskog wrote: >> >> 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 > > Depending on the actual practical needs, yet another variation of this > technique is to save only the primary keys into the permanent table. > Requires less disk space and displays changes made after the PK set was > materialized. Good point. There is a potential problem, though, if rows can be deleted. (But this could be indicated when returning the data.) There is also a risk for confusion, if the user selects data to be sorted by something which is not in the key, for instance price, and the price is updated while the user is paging. What this really boils down to is that to implement paging properly, you need to understand the business domain. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
Linear Mode
