I found the pivot table article very interesting, and decided to implement it on a page that gives web pageview statistics. The page had previously iterated through each hour for each server and issued 24*[number of servers] queries to the database.Much to my surprise, performance is far worse with the new pivot table query.It's a very simple schema:CREATE TABLE servers (i int identity, name varchar(15))CREATE TABLE log_running (i int, pagename varchar(50), i_servers int, datetime smalldatetime)
...and here's the pivot table query (which usually lives in an SP):SELECT servers.name, SUM(CASE WHEN DATEPART(hour,datetime) = 0 THEN 1 ELSE 0 END) AS '12am', SUM(CASE WHEN DATEPART(hour,datetime) = 1 THEN 1 ELSE 0 END) AS '1am', SUM(CASE WHEN DATEPART(hour,datetime) = 2 THEN 1 ELSE 0 END) AS '2am', SUM(CASE WHEN DATEPART(hour,datetime) = 3 THEN 1 ELSE 0 END) AS '3am', SUM(CASE WHEN DATEPART(hour,datetime) = 4 THEN 1 ELSE 0 END) AS '4am', SUM(CASE WHEN DATEPART(hour,datetime) = 5 THEN 1 ELSE 0 END) AS '5am', SUM(CASE WHEN DATEPART(hour,datetime) = 6 THEN 1 ELSE 0 END) AS '6am', SUM(CASE WHEN DATEPART(hour,datetime) = 7 THEN 1 ELSE 0 END) AS '7am', SUM(CASE WHEN DATEPART(hour,datetime) = 8 THEN 1 ELSE 0 END) AS '8am', SUM(CASE WHEN DATEPART(hour,datetime) = 9 THEN 1 ELSE 0 END) AS '9am', SUM(CASE WHEN DATEPART(hour,datetime) = 10 THEN 1 ELSE 0 END) AS '10am', SUM(CASE WHEN DATEPART(hour,datetime) = 11 THEN 1 ELSE 0 END) AS '11am', SUM(CASE WHEN DATEPART(hour,datetime) = 12 THEN 1 ELSE 0 END) AS '12pm', SUM(CASE WHEN DATEPART(hour,datetime) = 13 THEN 1 ELSE 0 END) AS '1pm', SUM(CASE WHEN DATEPART(hour,datetime) = 14 THEN 1 ELSE 0 END) AS '2pm', SUM(CASE WHEN DATEPART(hour,datetime) = 15 THEN 1 ELSE 0 END) AS '3pm', SUM(CASE WHEN DATEPART(hour,datetime) = 16 THEN 1 ELSE 0 END) AS '4pm', SUM(CASE WHEN DATEPART(hour,datetime) = 17 THEN 1 ELSE 0 END) AS '5pm', SUM(CASE WHEN DATEPART(hour,datetime) = 18 THEN 1 ELSE 0 END) AS '6pm', SUM(CASE WHEN DATEPART(hour,datetime) = 19 THEN 1 ELSE 0 END) AS '7pm', SUM(CASE WHEN DATEPART(hour,datetime) = 20 THEN 1 ELSE 0 END) AS '8pm', SUM(CASE WHEN DATEPART(hour,datetime) = 21 THEN 1 ELSE 0 END) AS '9pm', SUM(CASE WHEN DATEPART(hour,datetime) = 22 THEN 1 ELSE 0 END) AS '10pm', SUM(CASE WHEN DATEPART(hour,datetime) = 23 THEN 1 ELSE 0 END) AS '11pm'FROM log_runningJOIN servers on servers.i=log_running.iServersWHERE datetime>=dateadd(hour,-24,getdate())GROUP by servers.nameORDER by servers.name
...as you can see, it gives a running 24-hour window onto pageviews per server per hour. The query works fine, but *damn* is it slow.The main query cost seems to be coming from a clustered index scan (the clustered index being on the 'datetime' column of log_running).Is there any reasonable way to speed this up? Am I missing something?Thanks-b 
Pivot table query performance?
Posted - 2002-11-22 : 17:06:36
