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.
| Author |
Topic |
|
Leigh79
Starting Member
28 Posts |
 Posted - 2009-02-23 : 07:45:13
|
Hi GuysJust 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 <= '28/02/2009') AND (tar_subtertiary <> '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?ThanksLeigh |
|
|
Leigh79
Starting Member
28 Posts |
Posted - 2009-02-23 : 09:34:30
|
Hi GuysI've managed to fix this, I ran the query through the Index Tuning Wizard and added the following index: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 ) |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-02-23 : 09:46:59
|
| I had a few questions about your where clause when I saw your query. Not that it's the source of your problem but just some observations:(tar_booked >= '01/02/2009') AND (tar_booked <= '28/02/2009') --Why not use between when evaluating your dates?in your where clause for tar_primary, why not use IN: AND ((tar_primary in('CTA','TAM') or tar_primary='LTA'))Maybe try these changes out and see what exec plan and times come out to be.Mike"oh, that monkey is going to pay" |
|
|
|
souLTower
Starting Member
39 Posts |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-02-24 : 09:07:08
|
| Interesting, thanks for posting that. It's all going to depend on your setup and query whether it will work for you or not. Like Rob said in that link you'll have to try different ways to find what works best. Ugh....I just quoted Rob...I'm never going to hear the end of it...Mike"oh, that monkey is going to pay" |
|
|
|
|
|
|
|
|
Query runs slower with smaller date range?