General discussion


Build temporary tables to optimize queries

By MaryWeilage Editor ·
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: &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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Is it really an optimization?

by sergey In reply to Build temporary tables to ...

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....

Collapse -

Just goes to show...

by xtrac222 In reply to Is it really an optimizat ...

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.

Collapse -

Good Trick

by ehess In reply to Build temporary tables to ...

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

Collapse -

What about Multi-User ?

by karizmo In reply to Build temporary tables to ...

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.

Related Discussions

Related Forums