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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Why is the query so SLOWWWW?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

442 Posts

Posted - 03/07/2013 :  07:48:08  Show Profile  Reply with Quote
Could anyone please help me optimize this suedo query? The source table contains about 10 million records. This query takes about two minutes to return. I have tried adding indexes, using subquries and also a CTE. Any help would be appreciated!


select aDate, aName, aInvoice,
'aCount' = CASE WHEN aType = 'AA' and aName in ('B','C','D','E','F') Then '1'
    Else '0'

from aBillingTable
aType = 'AA'
and AName in ('B','C','D','E','F')
and aDate >= convert(VARCHAR(10), '02/01/2013', 101) and aDate < DATEADD(dd,1,convert(VARCHAR(10), '03/05/2013', 101))

Flowing Fount of Yak Knowledge

2875 Posts

Posted - 03/07/2013 :  08:41:54  Show Profile  Reply with Quote
is aDate a varchar or a date? This aDate >= convert(VARCHAR(10), '02/01/2013', 101) says it's a varchar, but
this aDate < DATEADD(dd,1,convert(VARCHAR(10), '03/05/2013', 101)) says it's a date.


P.S. there's no need to convert your 10 character strings to varchar(10)s, or are those values actually variables?

Everyday I learn something that somebody else already knew
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 03/07/2013 :  10:09:04  Show Profile  Reply with Quote
Along the same lines as what Jim was alluding to , assuming aDate is of datetime type, use your WHERE clause like shown below:
WHERE  aType = 'AA'
       AND AName IN ('B', 'C', 'D', 'E', 'F')
       AND aDate >= '20130201'
       AND aDate < '20130305'
That may not be the source of your slow performance though. What indexes did you add? Also, out of the 10 million rows, how many rows are you expecting given this where clause? If it is close to 10 million, there may not be much you can do about it. But on the other hand, if it is only a few rows, indexes should help.
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 03/07/2013 :  11:11:37  Show Profile  Reply with Quote
in any case analyzing your execution plan will give us some pointers on where exactly is the performance issue. Would you mind posting it? You can enable it by clicking on display actual estimation plan button from top.

SQL Server MVP

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000