Software

Ask something
Discussion Locked

Build temporary tables to optimize queries

0 helpful
4 replies

by maryweilage

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

This week's SQL Server e-newsletter describes how to build a table from a list of values and then join the table to the target table. Please let us know whether you plan to use the methods described in this tip to optimize queries.

If you aren't subscribed to the free SQL Server e-newsletter, you can automatically subscribe to this e-newsletter by pasting the following URL into your browser:
http://nl.com.com/MiniFormHandler?brand=builder &subs_channel=bldr_front_door&list_id=e046&tag=fb

* Please delete any extra spaces that appear when you paste this URL into your browser.

4 replies

by sergey

Interesting trick. Probably should be usefull sometimes but seems it must be REALLY-REALLY big IN() list to gain performance. Here is a test
(SQL 2000):
1. SELECT * from usertbl where e_mail IN(...)
(table has approx. 2.5 million records, e_mail field has non-clustered index, with 200 email addresses in list)
2. the same 200 addresses were inserted in perftst
table with prim. key on e_mail column and usertbl
was joined to it :
select u.* from usertbl u,perftst p where u.e_mail=p.e_mail
The results of execution plan:
1.Query cost (relative to the batch) 47.76% (for "direct list" query)
2. Query cost (relative to the batch) 52.24% (for "join" query)

Doesn't look like optimization! Well, not exactly true 'cause join wasn't against "in-memory" table but still almost 5% difference in query cost is way too big....

by xtrac222

I'm a relative novice and what this posting has reiterated to me is how important it is to scrutinize *every* part of a query to maximize performance (which is ultimately the goal).

It's often too easy to accept a query is right because it returns the correct results however postings like this remind us how granular we can be with SQL Server so thanks guys.

by ehess

Yes, I will use the temp table idea. Thank you and keep giving us these tips and tricks.

by karizmo

It is an excellent idea, I use it most ofter, but with one major variation : add to the temp table the domain/username combination so as to minimize collisions. The retrieval part is done usually using a stored procedure using a join of the target table against the temp table and operating on the domain/username combination as an additional selection criterion.

This discussion is closed to new replies.