SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

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

qman
Constraint Violating Yak Guru

USA
422 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!

Thanks


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

from aBillingTable
where 
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))

jimf
Flowing Fount of Yak Knowledge

USA
2868 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.

Jim

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

1530 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

visakh16
Very Important crosS Applying yaK Herder

India
47189 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
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.03 seconds. Powered By: Snitz Forums 2000