Another Slow Query - weird one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Another Slow Query - weird one

Author

Topic

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-07-20 : 13:25:25

My query in a Stored Proc is like this, takes over 10 minutes:

select
*
from
Table1 T1, #Temp1 T2
where
...
...
...

The #Temp1 has only 1 record!
The query has several where clauses but I have left them out to simplify things but a few of these where clauses go to a table of 20 million records.

When I replace the Temp table like this (in an attempt to trouble-shoot the speed problem), it takes about 1 second:

select
*
from
Table1 T1,
(select 1607643 ID, 'MCHUGHJ0' AdjusterID, 'KITC' BranchID, '2007-05-12 17:15:00.000' DiaryDate, 0 AssistStatus) T2
where
...
...
...

Anyone have any insight or and ideas how to speed up first query? The First query (ie. the slow one) uses an Index Spool/ Eager Spool, the second one doesn't.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 14:16:29

Change the order of tables and see if it works.
Without seeing the execution plans, I am guessing that you have a INNER HASH JOIN which is taking a long time.

N 56°04'39.26"
E 12°55'05.63"

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-07-20 : 14:31:32

quote:
Originally posted by Peso

Change the order of tables and see if it works.

Thankyou.

Changing the Order of the Tables? I thought SQL Server chooses the path of least estimated cost, regarless of the order in the 'From'?

In the execution Plan, it is the Index Spool/ Eager Spools which take up the highest %. I've searched to find out how to avoid them, but no luck yet. As the query runs, I can see the Cached Pages Count rise non-stop for the table with the 20 million rows.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 14:34:09

I've seen this before and changing the order of tables often works.
Sometimes I even have had to add OPTION (FORCE ORDER) to the query.

This is one of the few times you actually can beat the SQL Optimizer.

N 56°04'39.26"
E 12°55'05.63"

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-07-20 : 15:36:04

quote:
Originally posted by Peso

I've seen this before and changing the order of tables often works.
Sometimes I even have had to add OPTION (FORCE ORDER) to the query.

This is one of the few times you actually can beat the SQL Optimizer.

Thank you. I will keep the trick in mind.

I ended up fixing it (ie. speed improve) by re-writing. In those where clauses there was an 'OR' with both sides of the 'OR' going to that Table with the 20-million rows. I guess I didn't put that part from the begining because I was trying to get around the spooling issue. But if anyone has another solution (ie. I wish there was something like 'Force No spool'), let me know...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources