Query Slower when using smaller date range?

Question

Post reply

Query Slower when using smaller date range?

leigh.nugent

SSC Rookie

Points: 49
More actions
February 23, 2009 at 6:06 am

#75422

Hi Guys
Just wondered if anyone can shed any light on this for me. I have the following query:
SELECT tb1.tar_fileno, CASE WHEN ISNULL(tb2.tar_fileno,'0') = 0 THEN 'NO' ELSE 'YES' END
FROM [TARSC Reports].dbo.tar_downloads_new tb1
LEFT OUTER JOIN ( SELECT tar_fileno FROM [TARSC Reports].dbo.tar_downloads_new
WHERE (tar_booked >= '01/02/2009') AND (tar_booked <= '28/02/2009') AND (tar_suboperator LIKE '%TM FEE%')
GROUP BY tar_fileno ) tb2 ON tb1.tar_fileno = tb2.tar_fileno
WHERE (tar_booked >= '01/02/2009') AND (tar_booked 'SEL')
AND ((tar_primary = 'CTA') OR (tar_primary = 'TAM') OR (tar_primary = 'LTA'))
GROUP BY tb1.tar_fileno, tb2.tar_fileno
This executes in under a second, however when I change the date range to >= 16/02/2009 and <= 28/02/2009 it takes almost 3 minutes?!
Looking at the execution plan there is a Sort/Distinct Sort which on the first query is at 0% cost, but then on the second its over 50%.
Does anyone have any ideas as to why this would be and what I can do to prevent this?
Thanks
Leigh

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184397 More actions · Answer 1

What are the indexes on the table?

Have you tried this:

SELECT

tb1.tar_fileno,

CASE

WHEN ISNULL(tb2.tar_fileno,'0') = 0 THEN 'NO'

ELSE 'YES'

END

FROM

[TARSC Reports].dbo.tar_downloads_new tb1 LEFT OUTER JOIN

[TARSC Reports].dbo.tar_downloads_new tb2 On

(tb2.tar_booked >= '01/02/2009') AND

(tb2.tar_booked <= '28/02/2009') AND

(tb2.tar_suboperator LIKE '%TM FEE%') AND

tb1.tar_fileno = tb2.tar_fileno

WHERE

(tb1.tar_booked >= '01/02/2009') AND

(tb1.tar_booked <= 'SEL') AND

(

(tb1.tar_primary = 'CTA') OR

(tb1.tar_primary = 'TAM') OR

(tb1.tar_primary = 'LTA')

)

GROUP BY

tb1.tar_fileno,

tb2.tar_fileno

This eliminates the derived table and I think you were getting the sort operator because of the Group By in the derived table.

Edit: moved the final closing parenthesis so that it was not a smiley.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

leigh.nugent SSC Rookie Points: 49 More actions · Answer 2

Hi Jack

Thanks for replying.

I ran the updated query, however, it is still taking an age to run for the 16th - 28th Feb but not for the 1st - 28th Feb and is showing a Sort / Distinct Sort Cost of 53%.

There is an index on the tar_booked column.

Jeff Moden SSC Guru Points: 1004666 More actions · Answer 3

Jack Corbett (2/23/2009)
Edit: moved the final closing parenthesis so that it was not a smiley.

That's definitely on my wish list to Steve to fix. In the meantime, I run it through a word processor and replace all occurances of ")" with "& # 0 4 1 ;" without the spaces. The spaces were added here just so it would display.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

leigh.nugent SSC Rookie Points: 49 More actions · Answer 4

I ran the query through the Index Tuning Wizard and added the following index and this has fixed the issue:

CREATE NONCLUSTERED INDEX [tar_downloads_new3]

ON [dbo].[tar_downloads_new] ([tar_booked] ASC,

[tar_fileno] ASC,

[tar_suboperator] ASC,

[tar_primary] ASC,

[tar_subtertiary] ASC )

Thanks again for your help 🙂