473,372 Members | 1,365 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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?

Dec 23 '05 #1
5 2983
wa********@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
--

Dec 23 '05 #2
Yeah, I see in 2000 you'd do an insert into a temp table to assign a #
to each row. In 2005 this is not necessary, so would there be any
reason speed wise to use a temp table?

Dec 23 '05 #3
(wa********@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, es****@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
Dec 23 '05 #4

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.

Dec 25 '05 #5
Alexander Kuznetsov (AK************@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, es****@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
Dec 25 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Rick Hein | last post by:
I have an application that I am working on that uses some small temp tables. I am considering moving them to Table Variables - Would this be a performance enhancement? Some background...
1
by: Jim | last post by:
For some reason the compiler is telling me that I must declarethe variable @costcenter_tmp on lines 74 and 98...but if i put a select statement in ther (for testing) before the loop I get data back...
3
by: imani_technology_spam | last post by:
We were trying to remove duplicates and came up with two solutions. One solution is similar to the one found in a book called "Advanced Transact-SQL for SQL Server 2000" by Ben-Gan & Moreau. This...
3
by: shumaker | last post by:
When I import data I first import it from a text file into a table of it's own, then using some logic insert some of the records into a permanent table. I am considering having the table that...
2
by: matt | last post by:
Hello I am developing a web based webshop with a ms sql back end, but I cannot figure out how to do connection based temp tables, so that each user gets their own temp table to hold the...
17
by: Jon Ole Hedne | last post by:
I have worked on this problem some hours now (read many-many...), and I can't solve it: In vba-code I create a table with Connection.Execute, and add some data to it. This table is saved in the...
1
by: Robert McEuen | last post by:
Using Access 97 on WinXP I have data in a DB2 table that I'm trying to get into an identical table in my backend db. Based on volume of data and frequency of download, I'm trying to avoid...
2
by: Burbletrack | last post by:
Hi All, Hope someone can help me... Im trying to highlight the advantages of using table variables as apposed to temp tables within single scope. My manager seems to believe that table...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.