How to use the result of a complicated query in self-join?

views:

answers:

+1 Q:

How to use the result of a complicated query in self-join?

I'm writing a stored procedure on SQL Server 2000.
I've written a complicated select statement that takes 18 seconds to run.
The question is, how can I self-join the result correctly and efficiently?

Is it possible to manage it in a single select statement without repeating the big query statement that I currently have? Or should I save the result in a table variable or temp table?
Many thanks.

+4 A:

If the original query runs a long time, but the result is small, yes, you could consider a temporary / helper table, and run your second query (the self-join) on that table.

This may also help keep your approach modular / maintainable. And you'll be able to tune performance on that long-running query without your second one staring you in the face in the meantime.

Tobiasopdenbrouw 2010-08-04 07:18:20

The original query runs slow and the result is currently about 4000 records. Is this small or big? Is there the grammar allowing to get the query result into a table variable? Or must I use a temp table?

phoenies 2010-08-04 07:31:42

For 4000 records a temp table may well be the best option anyway. No statistics are kept on table variables and the optimiser always assumes that it will contain one row which can lead to some bad joining strategies.

Martin Smith 2010-08-04 07:36:24

phoenies, if you want to investigate further, look at OPENROWSET/Insert into ... EXEC (http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure), or a more verbose description of alternatives at http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Tobiasopdenbrouw 2010-08-04 07:42:49

Thanks, Martin. I also want to know in case I integrate all into a single select statement, does it mean I have to repeat the existing query text and the server will run it twice? Is it dumb to think like this?

phoenies 2010-08-04 07:46:43

@phoenies I think in theory the optimiser would be free to materialise it if you wrote it as a derived table with a self join but in practice I'm not sure it ever does. You could verify whether it is accessing your base tables twice by looking at the actual execution plan.

Martin Smith 2010-08-04 07:50:24

Erm...I've heard of the words execution plan and performance tuning. That's all I know. :( I'll figure it out if it's not too hard.

phoenies 2010-08-04 07:59:36

Thanks a lot, Tobiasopdenbrouw and Martin. Temp table is handy.

phoenies 2010-08-05 02:08:36

ansaurus

tags:

views:

answers:

How to use the result of a complicated query in self-join?

related questions