SQL Server Performance Forum – Threads Archive
How to speed up query with count function
Hi, I need to get some monthly, weekly report. But my query is very slow when it was run my local sql server. Anybody can help? below is my sql statement select convert(datetime, substring(convert(char(11),date,106),4,11)),count(target) as monthlyTotal, substring(target,1,5) as mytargetfrom iislog
where (date >= ‘2/1/2005’ and date <= ‘5/31/2005’ ) and
((target like ‘/elig%’)or
(target like ‘/clai%’)or
(target like ‘/prov%’))
group by convert(datetime,substring(convert(char(11),date,106),4,11)),substring(target,1,5)
Thanks!
first the table iislog should have an index on on (date,target),
or better yet, a computed index on DATEPART(yy,date), DATEPART(mm,date), substring(target,1,5) also try a nested query where the inner query only identifies the rows, and the outer query applies the transformation logic, ie, converting the mon part back to a date format, assumming everything is with one year SELECT mon, monthlyTotal, mytarget
(SELECT DATEPART(mm,date)AS mon, count(target) as monthlyTotal, substring(target,1,5) as mytarget
from iislog
where (date >= ‘2/1/2005’ and date <= ‘5/31/2005’ ) and
((target like ‘/elig%’)or
(target like ‘/clai%’)or
(target like ‘/prov%’))
) x
Hi,<br />The query is going to run slow because of<br />lot of substring operations and where clause with like operations with OR condition.<br />If table has lot of rows it will take time.<br />replace the date condition in where clause with between operator.<br />Also paste your query in QA and run ITW.<br />ITW will surely help you tune your query.[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by LindaLi</i><br /><br />Hi,<br /><br /> I need to get some monthly, weekly report. But my query is very slow when it was run my local sql server. Anybody can help? below is my sql statement<br /><br />select convert(datetime, substring(convert(char(11),date,106),4,11)),count(target) as monthlyTotal, substring(target,1,5) as mytarget<br />from iislog<br />where (date >= ‘2/1/2005’ and date <= ‘5/31/2005’ ) and<br /> ((target like ‘/elig%’)or<br /> (target like ‘/clai%’)or<br /> (target like ‘/prov%’))<br />group by convert(datetime,substring(convert(char(11),date,106),4,11)),substring(target,1,5)<br /><br />Thanks!<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
Another method might be
SELECT DATEADD(month,DATEDIFF(month,0,GETDATE()),0), COUNT(target), SUBSTRING(target,1,5)
FROM iislog
WHERE ([date] >= ‘20050102’ AND [date]<=’20050531′)
AND ((target LIKE ‘/elig%’)
OR (target LIKE ‘/clai%’)
OR (target LIKE ‘/prov%’))
GROUP BY DATEADD(month,DATEDIFF(month,0,GETDATE()),0), SUBSTRING(target,1,5)
< shameless plug mode on >
This might be interesting for you:http://www.sql-server-performance.com/fk_datetime.asp
< / shameless plug mode off > —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
In any case you may gain performance creating index on date and target in that order. You can also try Joe’s method using union in inner query instead of ors and index mentioned.
Thanks. The performance was approved.
What did you do? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
]]>