62366

Pivot vs Case T-sql efficiency

I am trying to figure out which way is more efficient. I tried to use Sql Fiddle but it didn't seem to recognize PIVOT or UNION What tools or statistics can I look at to determine which is more efficient.

CREATE TABLE T ( PersonNum INT ,WeekOf DATETIME ,ActivityType1 INT ,ActivityType2 INT ,Hours INT ) INSERT INTO T VALUES(1,'11/2/2014',5,0,40) INSERT INTO T VALUES(1,'11/2/2014',1,0,5) INSERT INTO T VALUES(1,'11/2/2014',1,0,8) INSERT INTO T VALUES(1,'11/2/2014',2,1,6) INSERT INTO T VALUES(1,'11/2/2014',2,2,2) INSERT INTO T VALUES(1,'11/2/2014',2,3,9) INSERT INTO T VALUES(1,'11/2/2014',2,4,7) INSERT INTO T VALUES(1,'11/9/2014',5,0,40) INSERT INTO T VALUES(1,'11/9/2014',1,0,2) INSERT INTO T VALUES(1,'11/9/2014',1,0,6) INSERT INTO T VALUES(1,'11/9/2014',2,1,7) INSERT INTO T VALUES(1,'11/9/2014',2,2,2) INSERT INTO T VALUES(1,'11/9/2014',2,3,3) INSERT INTO T VALUES(1,'11/9/2014',2,4,5)

Method 1

SELECT PersonNum , WeekOf , SUM(CASE WHEN ActivityType1 = 5 THEN Hours ELSE 0 END) AS Beginning , SUM(CASE WHEN ActivityType1 = 1 THEN Hours ELSE 0 END) AS Plus , SUM(CASE WHEN ActivityType1 = 2 AND ActivityType2 <> 3 THEN Hours ELSE 0 END) AS Minus , SUM(CASE WHEN ActivityType1 = 2 AND ActivityType2 = 3 THEN Hours ELSE 0 END) AS MinusSpecial FROM T GROUP BY PersonNum , WeekOf

Method 2

SELECT PersonNum , WeekOf , [Beginning] , [Plus] , [Minus] , [Minus Special] FROM ( SELECT PersonNum , WeekOf , 'Beginning' AS ColumnType , Hours FROM T WHERE ActivityType1 = 5 UNION SELECT PersonNum , WeekOf , 'Plus' AS ColumnType , Hours FROM T WHERE ActivityType1 = 1 UNION SELECT PersonNum , WeekOf , 'Minus' AS ColumnType , Hours FROM T WHERE ActivityType1 = 2 AND ActivityType2 <> 3 UNION SELECT PersonNum , WeekOf , 'Minus Special' AS ColumnType , Hours FROM T WHERE ActivityType1 = 2 AND ActivityType2 = 3 ) Data PIVOT (SUM(Hours) FOR ColumnType IN ([Beginning] ,[Plus] ,[Minus] ,[Minus Special]) ) pvt

Answer1:

You can do this

set statistics time on --first query set statistics time off set statistics time on --second query set statistics time off

You can look in the Messages window to see the execution times

Answer2:

May I suggest a rewrite of your PIVOT query so you don't have to hit the table 5 times.

select P.PersonNum, P.WeekOf, P.Beginning, P.Plus, P.Minus, P.MinusSpecial from ( select T.PersonNum, T.WeekOf, T.Hours, case ActivityType1 when 5 then 'Beginning' when 1 then 'Plus' when 2 then case ActivityType2 when 3 then 'MinusSpecial' else 'Minus' end end as ColumnType from T ) as T pivot (sum(T.Hours) for T.ColumnType in (Beginning, Plus, MinusSpecial, Minus)) as P

With that you will probably get the same query plan as your group by query and about the same performance.

Answer3:

The method 1 will be faster for sure . This is because there is only 1 select statement. From the other side there are 5 select statement in Method 2. The general rule is that the more statements query contains the longer it will take to execute.

Recommend

  • Entity Framework 7 Beta7 has no ColumnType anymore
  • e.GetListSourceFieldValue returns null in the CustomUnboundColumnData event handler
  • How can I select and copy a chart from a spreadsheet to a doc using Google Script?
  • RouterLink not working in grid in angular 2
  • Updating and removing unique join relationships in CakePHP
  • PHP multiple file uploads
  • Android cannot disable cut copy paste
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • Calculate time difference in hh:mm:ss with simple javascript/jquery
  • Approximate Order-Preserving Huffman Code
  • Mysql - How to search for 26 records that each begins with the letter of the alphabet?
  • Grails calculated field in SQL
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • formatting the colorbar ticklabels with SymLogNorm normalization in matplotlib
  • Illegal mix of collations for operation for date/time comparison
  • PHP - How to update data to MySQL when click a radio button
  • Is possible to count alias result on mysql
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • Trying to switch camera back to front but getting exception
  • Convert array of 8 bytes to signed long in C++
  • How to make Safari send if-modified-since header?
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • How to pass list parameters for each object using Spring MVC?
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Free memory of cv::Mat loaded using FileStorage API
  • How do you join a server to an Active Directory (domain)?
  • Angular 2 constructor injection vs direct access
  • Understanding cpu registers
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • How does Linux kernel interrupt the application?
  • JaxB to read class hierarchy
  • Programmatically clearing map cache
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • UserPrincipal.Current returns apppool on IIS
  • java string with new operator and a literal