| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
AjarnMark
SQL Slashing Gunting Master
USA
3246 Posts |
 Posted - 09/10/2001 : 11:46:35
|
Great article, Garth! Thanks very much for simplifying this. I've used Pivot tables in Excel quite a bit, but doing them in SQL was a bit confusing, until now. Again, Thanks!
-------------------------------------------------------------------
Just because you CAN do something does not mean that you SHOULD! |
 |
|
|
aiken
Aged Yak Warrior
USA
525 Posts |
Posted - 11/05/2002 : 23:57:27
|
I'm going to dredge this one up more than a year later.
I found this article helpful, however I find that while the data is more clearly presented, performance (in my application) is actually worse than issuing a bunch of individual queries.
My application is a logfile that I want to show per-hour stats on, very much like this article uses.
My schema is thus (simplified, but all that's relevant to this):
CREATE TABLE log
(i int identity,
i_users int,
datetime datetime,
i_servers tinyint,
i_sites tinyint)
CREATE INDEX [idx_log_run_datesite] ON [dbo].[log] ([i_sites], [datetime] desc )
CREATE UNIQUE
INDEX [PK_log] ON [dbo].[log] ([i])
CREATE CLUSTERED
INDEX [idx_log_iusers] ON [dbo].[log] ([i_users])
...My crosstab query is this:
SELECT i_servers,
SUM(CASE WHEN DATEPART(hour,datetime) = 0 THEN 1 ELSE 0 END) AS '0',
SUM(CASE WHEN DATEPART(hour,datetime) = 1 THEN 1 ELSE 0 END) AS '1',
SUM(CASE WHEN DATEPART(hour,datetime) = 2 THEN 1 ELSE 0 END) AS '2',
SUM(CASE WHEN DATEPART(hour,datetime) = 3 THEN 1 ELSE 0 END) AS '3',
SUM(CASE WHEN DATEPART(hour,datetime) = 4 THEN 1 ELSE 0 END) AS '4',
SUM(CASE WHEN DATEPART(hour,datetime) = 5 THEN 1 ELSE 0 END) AS '5',
SUM(CASE WHEN DATEPART(hour,datetime) = 6 THEN 1 ELSE 0 END) AS '6',
SUM(CASE WHEN DATEPART(hour,datetime) = 7 THEN 1 ELSE 0 END) AS '7',
SUM(CASE WHEN DATEPART(hour,datetime) = 8 THEN 1 ELSE 0 END) AS '8',
SUM(CASE WHEN DATEPART(hour,datetime) = 9 THEN 1 ELSE 0 END) AS '9',
SUM(CASE WHEN DATEPART(hour,datetime) = 10 THEN 1 ELSE 0 END) AS '10',
SUM(CASE WHEN DATEPART(hour,datetime) = 11 THEN 1 ELSE 0 END) AS '11',
SUM(CASE WHEN DATEPART(hour,datetime) = 12 THEN 1 ELSE 0 END) AS '12',
SUM(CASE WHEN DATEPART(hour,datetime) = 13 THEN 1 ELSE 0 END) AS '13',
SUM(CASE WHEN DATEPART(hour,datetime) = 14 THEN 1 ELSE 0 END) AS '14',
SUM(CASE WHEN DATEPART(hour,datetime) = 15 THEN 1 ELSE 0 END) AS '15',
SUM(CASE WHEN DATEPART(hour,datetime) = 16 THEN 1 ELSE 0 END) AS '16',
SUM(CASE WHEN DATEPART(hour,datetime) = 17 THEN 1 ELSE 0 END) AS '17',
SUM(CASE WHEN DATEPART(hour,datetime) = 18 THEN 1 ELSE 0 END) AS '18',
SUM(CASE WHEN DATEPART(hour,datetime) = 19 THEN 1 ELSE 0 END) AS '19',
SUM(CASE WHEN DATEPART(hour,datetime) = 20 THEN 1 ELSE 0 END) AS '20',
SUM(CASE WHEN DATEPART(hour,datetime) = 21 THEN 1 ELSE 0 END) AS '21',
SUM(CASE WHEN DATEPART(hour,datetime) = 22 THEN 1 ELSE 0 END) AS '22',
SUM(CASE WHEN DATEPART(hour,datetime) = 23 THEN 1 ELSE 0 END) AS '23'
FROM log WITH(NOLOCK)
WHERE DATETIME>=dateadd(hour,-24,getdate()) and i_sites=1
group by i_servers
Performance isn't completely miserable, but it's pretty bad. Can I improve things with, say, an index on a computed column based on datepart(hour,datetime)? Any other suggestions?
Thanks
-b
In case it's useful, here's the showplan_all from that query:
SELECT i_servers, SUM(CASE WHEN DATEPART(hour,datetime) = 0 THEN 1 ELSE 0 END) AS '0', SUM(CASE WHEN DATEPART(hour,datetime) = 1 THEN 1 ELSE 0 END) AS '1', SUM(CASE WHEN DATEPART(hour,datetime) = 2 THEN 1 ELSE 0 END) AS '2', SUM(CASE WHEN DATEPART(hour,datetime) = 3 THEN 1 ELSE 0 END) AS '3', SUM(CASE WHEN DATEPART(hour,datetime) = 4 THEN 1 ELSE 0 END) AS '4', SUM(CASE WHEN DATEPART(hour,datetime) = 5 THEN 1 ELSE 0 END) AS '5', SUM(CASE WHEN DATEPART(hour,datetime) = 6 THEN 1 ELSE 0 END) AS '6', SUM(CASE WHEN DATEPART(hour,datetime) = 7 THEN 1 ELSE 0 END) AS '7', SUM(CASE WHEN DATEPART(hour,datetime) = 8 THEN 1 ELSE 0 END) AS '8', SUM(CASE WHEN DATEPART(hour,datetime) = 9 THEN 1 ELSE 0 END) AS '9', SUM(CASE WHEN DATEPART(hour,datetime) = 10 THEN 1 ELSE 0 END) AS '10', SUM(CASE WHEN DATEPART(hour,datetime) = 11 THEN 1 ELSE 0 END) AS '11', SUM(CASE WHEN DATEPART(hour,datetime) = 12 THEN 1 ELSE 0 END) AS '12', SUM(CASE WHEN DATEPART(hour,datetime) = 13 THEN 1 ELSE 0 END) AS '13', SUM(CASE WHEN DATEPART(hour,datetime) = 14 THEN 1 ELSE 0 END) AS '14', SUM(CASE WHEN DATEPART(hour,datetime) = 15 THEN 1 ELSE 0 END) AS '15', SUM(CASE WHEN DATEPART(hour,datetime) = 16 THEN 1 ELSE 0 END) AS '16', SUM(CASE WHEN DATEPART(hour,datetime) = 17 THEN 1 ELSE 0 END) AS '17', SUM(CASE WHEN DATEPART(hour,datetime) = 18 THEN 1 ELSE 0 END) AS '18', SUM(CASE WHEN DATEPART(hour,datetime) = 19 THEN 1 ELSE 0 END) AS '19', SUM(CASE WHEN DATEPART(hour,datetime) = 20 THEN 1 ELSE 0 END) AS '20', SUM(CASE WHEN DATEPART(hour,datetime) = 21 THEN 1 ELSE 0 END) AS '21', SUM(CASE WHEN DATEPART(hour,datetime) = 22 THEN 1 ELSE 0 END) AS '22', SUM(CASE WHEN DATEPART(hour,datetime) = 23 THEN 1 ELSE 0 END) AS '23' FROM log WITH(NOLOCK) WHERE DATETIME>=dateadd(hour,-24,getdate()) and i_sites=1 group by i_servers 1 1 0 NULL NULL 1 NULL 6.1503868 NULL NULL NULL 39.658604 NULL NULL SELECT 0 NULL
|--Stream Aggregate(GROUP BY:([log].[i_Servers]) DEFINE:([Expr1002]=SUM([partialagg1028]), [Expr1003]=SUM([partialagg1029]), [Expr1004]=SUM([partialagg1030]), [Expr1005]=SUM([partialagg1031]), [Expr1006]=SUM([partialagg1032]), [Expr1007]=SUM([partialagg1033]), [Expr1008]=SUM([partialagg1034]), [Expr1009]=SUM([partialagg1035]), [Expr1010]=SUM([partialagg1036]), [Expr1011]=SUM([partialagg1037]), [Expr1012]=SUM([partialagg1038]), [Expr1013]=SUM([partialagg1039]), [Expr1014]=SUM([partialagg1040]), [Expr1015]=SUM([partialagg1041]), [Expr1016]=SUM([partialagg1042]), [Expr1017]=SUM([partialagg1043]), [Expr1018]=SUM([partialagg1044]), [Expr1019]=SUM([partialagg1045]), [Expr1020]=SUM([partialagg1046]), [Expr1021]=SUM([partialagg1047]), [Expr1022]=SUM([partialagg1048]), [Expr1023]=SUM([partialagg1049]), [Expr1024]=SUM([partialagg1050]), [Expr1025]=SUM([partialagg1051]))) 1 2 1 Stream Aggregate Aggregate GROUP BY:([log].[i_Servers]) [Expr1002]=SUM([partialagg1028]), [Expr1003]=SUM([partialagg1029]), [Expr1004]=SUM([partialagg1030]), [Expr1005]=SUM([partialagg1031]), [Expr1006]=SUM([partialagg1032]), [Expr1007]=SUM([partialagg1033]), [Expr1008]=SUM([partialagg1034]), [Expr1009]=SUM([partialagg1035]), [Expr1010]=SUM([partialagg1036]), [Expr1011]=SUM([partialagg1037]), [Expr1012]=SUM([partialagg1038]), [Expr1013]=SUM([partialagg1039]), [Expr1014]=SUM([partialagg1040]), [Expr1015]=SUM([partialagg1041]), [Expr1016]=SUM([partialagg1042]), [Expr1017]=SUM([partialagg1043]), [Expr1018]=SUM([partialagg1044]), [Expr1019]=SUM([partialagg1045]), [Expr1020]=SUM([partialagg1046]), [Expr1021]=SUM([partialagg1047]), [Expr1022]=SUM([partialagg1048]), [Expr1023]=SUM([partialagg1049]), [Expr1024]=SUM([partialagg1050]), [Expr1025]=SUM([partialagg1051]) 6.1503868 0.0 9.7483629E-5 110 39.658604 [log].[i_servers], [Expr1002], [Expr1003], [Expr1004], [Expr1005], [Expr1006], [Expr1007], [Expr1008], [Expr1009], [Expr1010], [Expr1011], [Expr1012], [Expr1013], [Expr1014], [Expr1015], [Expr1016], [Expr1017], [Expr1018], [Expr1019], [Expr1020], [Expr1021], [Expr1022], [Expr1023], [Expr1024], [Expr1025] NULL PLAN_ROW 0 1.0
|--Sort(ORDER BY:([log].[i_servers] ASC)) 1 3 2 Sort Sort ORDER BY:([log].[i_servers] ASC) NULL 24.601547 1.1261261E-2 2.7744425E-4 110 39.658508 [log].[i_servers], [partialagg1028], [partialagg1029], [partialagg1030], [partialagg1031], [partialagg1032], [partialagg1033], [partialagg1034], [partialagg1035], [partialagg1036], [partialagg1037], [partialagg1038], [partialagg1039], [partialagg1040], [partialagg1041], [partialagg1042], [partialagg1043], [partialagg1044], [partialagg1045], [partialagg1046], [partialagg1047], [partialagg1048], [partialagg1049], [partialagg1050], [partialagg1051] NULL PLAN_ROW 0 1.0
|--Parallelism(Gather Streams) 1 4 3 Parallelism Gather Streams NULL NULL 24.601547 0.0 2.8999325E-2 110 39.646969 [log].[i_servers], [partialagg1028], [partialagg1029], [partialagg1030], [partialagg1031], [partialagg1032], [partialagg1033], [partialagg1034], [partialagg1035], [partialagg1036], [partialagg1037], [partialagg1038], [partialagg1039], [partialagg1040], [partialagg1041], [partialagg1042], [partialagg1043], [partialagg1044], [partialagg1045], [partialagg1046], [partialagg1047], [partialagg1048], [partialagg1049], [partialagg1050], [partialagg1051] NULL PLAN_ROW -1 1.0
|--Hash Match(Partial Aggregate, HASH:([log].[i_servers]) DEFINE:([partialagg1028]=SUM(If (datepart(hour, Convert([log].[datetime]))=0) then 1 else 0), [partialagg1029]=SUM(If (datepart(hour, Convert([log].[datetime]))=1) then 1 else 0), [partialagg1030]=SUM(If (datepart(hour, Convert([log].[datetime]))=2) then 1 else 0), [partialagg1031]=SUM(If (datepart(hour, Convert([log].[datetime]))=3) then 1 else 0), [partialagg1032]=SUM(If (datepart(hour, Convert([log].[datetime]))=4) then 1 else 0), [partialagg1033]=SUM(If (datepart(hour, Convert([log].[datetime]))=5) then 1 else 0), [partialagg1034]=SUM(If (datepart(hour, Convert([log].[datetime]))=6) then 1 else 0), [partialagg1035]=SUM(If (datepart(hour, Convert([log].[datetime]))=7) then 1 else 0), [partialagg1036]=SUM(If (datepart(hour, Convert([log].[datetime]))=8) then 1 else 0), [partialagg1037]=SUM(If (datepart(hour, Convert([log].[datetime]))=9) then 1 else 0), [partialagg1038]=SUM(If (datepart(hour, Convert([log].[datetime]))=10) then 1 else 0), [partialagg1039]=SUM(If (datepart(hour, Convert([log].[datetime]))=11) then 1 else 0), [partialagg1040]=SUM(If (datepart(hour, Convert([log].[datetime]))=12) then 1 else 0), [partialagg1041]=SUM(If (datepart(hour, Convert([log].[datetime]))=13) then 1 else 0), [partialagg1042]=SUM(If (datepart(hour, Convert([log].[datetime]))=14) then 1 else 0), [partialagg1043]=SUM(If (datepart(hour, Convert([log].[datetime]))=15) then 1 else 0), [partialagg1044]=SUM(If (datepart(hour, Convert([log].[datetime]))=16) then 1 else 0), [partialagg1045]=SUM(If (datepart(hour, Convert([log].[datetime]))=17) then 1 else 0), [partialagg1046]=SUM(If (datepart(hour, Convert([log].[datetime]))=18) then 1 else 0), [partialagg1047]=SUM(If (datepart(hour, Convert([log].[datetime]))=19) then 1 else 0), [partialagg1048]=SUM(If (datepart(hour, Convert([log].[datetime]))=20) then 1 else 0), [partialagg1049]=SUM(If (datepart(hour, Convert([log].[datetime]))=21) then 1 else 0), [partialagg1050]=SUM(If (datepart(hour, Convert([log].[datetime]))=22) then 1 else 0), [partialagg1051]=SUM(If (datepart(hour, Convert([log].[datetime]))=23) then 1 else 0))) 1 5 4 Hash Match Partial Aggregate HASH:([log].[i_servers]), DEFINE:([partialagg1028]=SUM(If (datepart(hour, Convert([log].[datetime]))=0) then 1 else 0), [partialagg1029]=SUM(If (datepart(hour, Convert([log].[datetime]))=1) then 1 else 0), [partialagg1030]=SUM(If (datepart(hour, Convert([log].[datetime]))=2) then 1 else 0), [partialagg1031]=SUM(If (datepart(hour, Convert([log].[datetime]))=3) then 1 else 0), [partialagg1032]=SUM(If (datepart(hour, Convert([log].[datetime]))=4) then 1 else 0), [partialagg1033]=SUM(If (datepart(hour, Convert([log].[datetime]))=5) then 1 else 0), [partialagg1034]=SUM(If (datepart(hour, Convert([log].[datetime]))=6) then 1 else 0), [partialagg1035]=SUM(If (datepart(hour, Convert([log].[datetime]))=7) then 1 else 0), [partialagg1036]=SUM(If (datepart(hour, Convert([log].[datetime]))=8) then 1 else 0), [partialagg1037]=SUM(If (datepart(hour, Convert([log].[datetime]))=9) then 1 else 0), [partialagg1038]=SUM(If (datepart(hour, Convert([log].[datetime]))=10) then 1 else 0), [partialagg1039]=SUM(If (datepart(hour, Convert([log].[datetime]))=11) then 1 else 0), [partialagg1040]=SUM(If (datepart(hour, Convert([log].[datetime]))=12) then 1 else 0), [partialagg1041]=SUM(If (datepart(hour, Convert([log].[datetime]))=13) then 1 else 0), [partialagg1042]=SUM(If (datepart(hour, Convert([log].[datetime]))=14) then 1 else 0), [partialagg1043]=SUM(If (datepart(hour, Convert([log].[datetime]))=15) then 1 else 0), [partialagg1044]=SUM(If (datepart(hour, Convert([log].[datetime]))=16) then 1 else 0), [partialagg1045]=SUM(If (datepart(hour, Convert([log].[datetime]))=17) then 1 else 0), [partialagg1046]=SUM(If (datepart(hour, Convert([log].[datetime]))=18) then 1 else 0), [partialagg1047]=SUM(If (datepart(hour, Convert([log].[datetime]))=19) then 1 else 0), [partialagg1048]=SUM(If (datepart(hour, Convert([log].[datetime]))=20) then 1 else 0), [partialagg1049]=SUM(If (datepart(hour, Convert([log].[datetime]))=21) then 1 else 0), [partialagg1050]=SUM(If (datepart(hour, Convert([log].[datetime]))=22) then 1 else 0), [partialagg1051]=SUM(If (datepart(hour, Convert([log].[datetime]))=23) then 1 else 0)) [partialagg1028]=SUM(If (datepart(hour, Convert([log].[datetime]))=0) then 1 else 0), [partialagg1029]=SUM(If (datepart(hour, Convert([log].[datetime]))=1) then 1 else 0), [partialagg1030]=SUM(If (datepart(hour, Convert([log].[datetime]))=2) then 1 else 0), [partialagg1031]=SUM(If (datepart(hour, Convert([log].[datetime]))=3) then 1 else 0), [partialagg1032]=SUM(If (datepart(hour, Convert([log].[datetime]))=4) then 1 else 0), [partialagg1033]=SUM(If (datepart(hour, Convert([log].[datetime]))=5) then 1 else 0), [partialagg1034]=SUM(If (datepart(hour, Convert([log].[datetime]))=6) then 1 else 0), [partialagg1035]=SUM(If (datepart(hour, Convert([log].[datetime]))=7) then 1 else 0), [partialagg1036]=SUM(If (datepart(hour, Convert([log].[datetime]))=8) then 1 else 0), [partialagg1037]=SUM(If (datepart(hour, Convert([log].[datetime]))=9) then 1 else 0), [partialagg1038]=SUM(If (datepart(hour, Convert([log].[datetime]))=10) then 1 else 0), [partialagg1039]=SUM(If (datepart(hour, Convert([log].[datetime]))=11) then 1 else 0), [partialagg1040]=SUM(If (datepart(hour, Convert([log].[datetime]))=12) then 1 else 0), [partialagg1041]=SUM(If (datepart(hour, Convert([log].[datetime]))=13) then 1 else 0), [partialagg1042]=SUM(If (datepart(hour, Convert([log].[datetime]))=14) then 1 else 0), [partialagg1043]=SUM(If (datepart(hour, Convert([log].[datetime]))=15) then 1 else 0), [partialagg1044]=SUM(If (datepart(hour, Convert([log].[datetime]))=16) then 1 else 0), [partialagg1045]=SUM(If (datepart(hour, Convert([log].[datetime]))=17) then 1 else 0), [partialagg1046]=SUM(If (datepart(hour, Convert([log].[datetime]))=18) then 1 else 0), [partialagg1047]=SUM(If (datepart(hour, Convert([log].[datetime]))=19) then 1 else 0), [partialagg1048]=SUM(If (datepart(hour, Convert([log].[datetime]))=20) then 1 else 0), [partialagg1049]=SUM(If (datepart(hour, Convert([log].[datetime]))=21) then 1 else 0), [partialagg1050]=SUM(If (datepart(hour, Convert([log].[datetime]))=22) then 1 else 0), [partialagg1051]=SUM(If (datepart(hour, Convert([log].[datetime]))=23) then 1 else 0) 24.601547 0.0 2.2327244 110 39.61797 [log].[i_servers], [partialagg1028], [partialagg1029], [partialagg1030], [partialagg1031], [partialagg1032], [partialagg1033], [partialagg1034], [partialagg1035], [partialagg1036], [partialagg1037], [partialagg1038], [partialagg1039], [partialagg1040], [partialagg1041], [partialagg1042], [partialagg1043], [partialagg1044], [partialagg1045], [partialagg1046], [partialagg1047], [partialagg1048], [partialagg1049], [partialagg1050], [partialagg1051] NULL PLAN_ROW -1 1.0
|--Clustered Index Scan(OBJECT:([database].[dbo].[log].[idx_log_iusers]), WHERE:([log].[i_sites]=1 AND Convert([log].[datetime])>=dateadd(hour, -24, getdate()))) 1 6 5 Clustered Index Scan Clustered Index Scan OBJECT:([database].[dbo].[log].[idx_log_iusers]), WHERE:([log].[i_sites]=1 AND Convert([log].[datetime])>=dateadd(hour, -24, getdate())) [log].[i_sites], [log].[datetime], [log].[i_servers] 968224.06 33.929432 1.8276123 75 35.757042 [log].[i_sites], [log].[datetime], [log].[i_servers] NULL PLAN_ROW -1 1.0
|
|
|
|
graz
Chief SQLTeam Crack Dealer
USA
4128 Posts |
Posted - 11/06/2002 : 00:03:57
|
(I edited the previous post to remove the CODE tag. That may not be more readable but all the rest of the posts are)
-graz
===============================================
Creating tomorrow's legacy systems today.
One crisis at a time. |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 11/13/2002 : 14:24:38
|
To increase performance:
Remember that DATEPART is evaluated over and over. You may get better performance by creating a subquery returning your data, grouped by Hour in a field called 'Hour'. Then, do a cross-tab using that field. I do this all the time for Utilization reports here at the office, where we list employees as rows and different types of labor as columns and hours as values.
BTW -- this also makes your query shorter and easier to edit and read.
SELECT Server,
SUM(CASE WHEN Hr = 1 THEN Value ELSE 0 END) as '01',
SUM(CASE WHEN Hr = 2 then Value ELSE 0 END) as '02',
..etc...
FROM
(SELECT server, DatePart(hour,datetime) Hr, SUM(1) as value FROM
log
GROUP BY server, DatePart(hour,datetime)) A
I have had good luck really increasing query performance by using subqueries this way.
|
|
|
| |
Topic |
|
|
|
Article: Counting Transactions per Hour using a Pivot Table
