This is a weird one. SLOW query.

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)

This is a weird one. SLOW query.

Author

Topic

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-10-28 : 12:00:40

Was wondering if anyone can shed some light on this.

This query takes 3 seconds when also opening the cursor and doing the FETCH NEXT:

DECLARE crSubscriber CURSOR FOR 
SELECT BillToID, Percentage, Taxable 
FROM ClaimSubscription 
WHERE ClaimID = @ClaimID 
ORDER BY Percentage DESC

However, this query takes only 0.001 seconds:

SELECT BillToID, Percentage, Taxable 
FROM ClaimSubscription 
WHERE ClaimID = @ClaimID 
ORDER BY Percentage DESC

And this one also takes only 0.001 seconds when opening the cursor and doing the FETCH NEXT:

DECLARE crSubscriber CURSOR FOR 
SELECT BillToID, Percentage, Taxable 
FROM ClaimSubscription 
WHERE ClaimID = @ClaimID

I looked at the execution path and in the slow query, SQL Server chooses to use the index on the column Percentage, rather than the Index on column ClaimID.

So I can't understand why it would not choose the optimum index. And I can't understand why it chooses a different index bewteen the select with the cursor and the select without the cursor - after all, it is the exact same select statement?

To me, I wonder if it is a bug in SQL Server? Or can anyone shed some light on this?

In any event, I was able to repair the slow query by using an index hint (with (Index(claimID))

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-28 : 12:08:50

Is index on Percentage a clustered index?

No, you're never too old to Yak'n'Roll if you're too young to die.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-10-28 : 12:19:53

quote:
Originally posted by webfred

Is index on Percentage a clustered index?

No, the clustered index is on the column 'ID', which is not part of this query. I really can't see why it would use the index on column percentage.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-28 : 12:23:26

Only to see what happens...
can you do something like this: ORDER BY Percentage+0 DESC

No, you're never too old to Yak'n'Roll if you're too young to die.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-10-28 : 12:45:15

quote:
Originally posted by webfred

Only to see what happens...
can you do something like this: ORDER BY Percentage+0 DESC

Thanks. Tried that. Still slow at 3 seconds and still used Percentage Column Index. Then tried ORDER BY Percentage * 2. Still slow.

Then tried for the heck of it 'Order by Percentage * BillToID'. That was fast, around 0.001 seconds and SQL Server used the Claim ID index.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources