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.
| Author |
Topic |
|
fwdtech
Starting Member
11 Posts |
 Posted - 2009-11-23 : 12:24:45
|
| I've been handed a table with a few million menu clicks that has the following layout:Page (varchar - the URL requested minus the domain)Request (datetime - uses getdate() as its default field)Address (varchar - IP address of the requester)Account (varchar - not needed for this query).I've been asked to generate a list of menu clicks based on the weeks of the year. I came up with this simple query:SELECT Page,COUNT(Page) AS nCount,DATEPART(wk,Request) AS nWeek FROM MenuTrackingWHERE Page LIKE '%story%' GROUP BY Page,DATEPART(wk,Request) ORDER BY DATEPART(wk,Request);But running against the database takes longer than I would have expected (over 81% in the clustered index scan according to the profiler) taking about 2 minutes to run through 2.5 million clicks.Is there a better way to run this query? Something that I may be doing poorly in the query that is duplicating SQL work? Does my use of "LIKE" cause the problem? |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-11-23 : 19:48:07
|
| The "like '%story%'" will need to scan your entire table looking for a match. If the page name started with the key word 'story' then you could drop the leading wildcard.The DATEPART() will also require you to process the entire table. Is there any hope that you could limit the time range to, for instance, the past twelve months?One thought, thoroughly UNtested, would be to pull the proper pages into a temp table, index the table on the date column and then select the grouped values.=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
 |
|
|
|
|
|
Group by Week with Count