Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Optimizing for group by
Message
 
 
To
24/01/2011 18:47:35
General information
Forum:
Microsoft SQL Server
Category:
Other
Environment versions
SQL Server:
SQL Server 2008
Application:
Web
Miscellaneous
Thread ID:
01497285
Message ID:
01497293
Views:
50
This message has been marked as a message which has helped to the initial question of the thread.
>How can I create an index which would optimize this query:
>
>
>SELECT CONVERT(CHAR(4),YEAR(Hit.AddDate))+REPLACE(STR(MONTH(Hit.AddDate),2),' ','0')+
> REPLACE(STR(DAY(Hit.AddDate),2),' ','0') AS lcChampNumero,
> COUNT(*) AS Compte,MIN(Hit.AddDate) AS AddDate
> FROM Hit
> WHERE Hit.NoServer=3
> GROUP BY CONVERT(CHAR(4),YEAR(Hit.AddDate))+REPLACE(STR(MONTH(Hit.AddDate),2),' ','0')+
> REPLACE(STR(DAY(Hit.AddDate),2),' ','0')
> ORDER BY 1 DESC 
>
>
>I added an index NoServerAddDate which includes the NoServer and AddDate fields. I see that this has changed the execution plan. It does show that it uses it. However, my query is not any faster.
>
>Is there a way to make it faster?
>
>This is one on a Hit table having about two million records and all I need is a repartition on a daily basis with the count. The result returns presently 28 records.

In SQL Server just use CONVERT(char(6), Hit.AddDate, 112) instead of your complex expression. Index on AddDate should speed this query a bit.
Also, how selective the NoServer column is (e.g. how many values it may have)?
If it's not broken, fix it until it is.


My Blog
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform