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 2008 Forums
 Transact-SQL (2008)
 Why is the query so SLOWWWW?

Author  Topic 

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-03-07 : 07:48:08
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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-03-07 : 08:41:54
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-07 : 10:09:04
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

52326 Posts

Posted - 2013-03-07 : 11:11:37
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
   

- Advertisement -