Query Performance: PIVOT vs CASE

October 10, 2016

A discussion came up at my job on the most efficient way to return subtotals and totals in one row, something like:

Total A Total B Total C Grand Total
1 2 3 6

I had assumed that PIVOT would be the most straightforward method, but others preferred using CASE statements. So which method is more efficient?

I posted a script on GitHub to build a table with 6 million records to run both kinds of queries against. The results of each will be:

Red Blue White Total
3000000 2000000 1000000 6000000

The PIVOT query:

select [Red], [Blue], [White], ([Red] + [Blue] + [White]) as Total
from (
select RecordColor from dbo.TestPivotCase
) as a
pivot (
count(RecordColor) for RecordColor in ([Red], [White], [Blue])
)as p;

The CASE query:

  sum(case when RecordColor = 'Red' then 1 else 0 end) as [Red],
  sum(case when RecordColor = 'Blue' then 1 else 0 end) as [Blue],
  sum(case when RecordColor = 'White' then 1 else 0 end) as [White],
  count(*) as [Total]
from dbo.TestPivotCase;

The plans were almost exactly similar in cost, with the PIVOT with a slightly less cost (49.7 % vs. 50.3%).
The execution plans were very similar.
Case Plan:

Pivot Plan:

So both methods have a similar execution plan and run time, with a slight edge to the PIVOT method.

SQL Server Plan Comparison Tool

September 30, 2016

MSSQL Tiger Team – Plan Comparison Tool
I ran across the above link detailing the execution plan comparison tool included as part of SSMS(SQL Server Management Studio). This tool gives you a way to view two execution plans at the same time, along with displaying statistics from each plan.
Once you produce the first execution plan, you can right-click and select ‘Save Execution Plan As…’ and save the plan as a .sqlplan file. Once the second execution plan is generated, you can right click on the plan and select the ‘Compare Showplan’ option. The dialog opened will allow you to select the file with the first plan, and will open the comparison tool to display the two plans.

On the topic of execution plans, SQL Sentry announced recently that their Plan Explorer tool is now free, If you haven’t used this tool, it is a great improvement on viewing SQL Server execution plans.
SQL Sentry Plan Explorer

Inner Join vs Outer Join Performance

August 29, 2016

At work, a colleague and I discussed the performance of inner joins and against outer joins, particularly in the case where both types of joins would return the same number of rows. So if it turned out that you always had a match for each inner record, would you pay a penalty for having a left join? In most cases an outer join would return more records, since you would usually have unmatched records.
I posted a script on GitHub to create two tables that would be joined on RecordId.

create table dbo.Test1(
RecordId int not null identity(1,1) primary key clustered,
CreatedDate datetime not null default getdate()

create table dbo.Test2(
RecordId int null,
ItemId tinyint null,
CreatedDate datetime not null default getdate()

I created two million records in Test1, and then 5 Test2 records for each one in Test1. The Test2.RecordId column was left nullable, so we wouldn’t give the optimizer any hints as far as unmatched records.
So next we return all records in two queries, one with an inner join and one with an outer join.

select *
from dbo.Test1 as a
join dbo.Test2 as b
	on b.RecordId = a.RecordId;

select *
from dbo.Test1 as a
left join dbo.Test2 as b
	on b.RecordId = a.RecordId;

The two queries run in the same amount of time with virtually the same execution plan. Since Test1 has a clustered index, the key column is already sorted, so a Merge Join is used. So in this case, we’re running down the clustered index and finding a match for each record in the Test2 table, so it makes sense that the same amount of work would be done.
Dropping the clustered primary key on Test1 results in a different execution plan, where a Hash join is used instead of the Merge join, but the run time is still the same for both queries.
Deleting every 4th row from the detail table resulted in the inner join running slightly faster, but it was a 52-49 split in the execution plan.
I was a little surprised at first there wouldn’t be a difference in query performance between the two join types, but now it makes sense that the optimizer would go through the same plan for both types of join in this case. Inner vs Outer join does make a lot of difference logically in the records returned, but if both joins will return the same number of records then the plans turn out to be the same and the execution time is the same as well.

SQL Server 2016 – Memory Optimization Advisor

August 8, 2016

Right-clicking on a table in SSMS 2016 includes an option for the ‘Memory Optimization Advisor’. This starts a wizard to assist in migrating a table to an In-Memory table structure. If the base table has any issues that would prevent it from being migrated to an in-memory structure, the wizard will identify and help to resolve those issues. Here’s a link to some issues that would prevent the migration.
A big issue will be tables with foreign keys defined. The keys will have to be dropped and then re-created, they won’t be migrated.
If all checks pass, then the table can be migrated. There is an option to migrate the data as well.

I posted on In-Memory tables for SQL Server 2014, although several changes were made for 2016. 2016 supports foreign keys, check contraints, outer joins, among other features.

SQL Server 2016 – Sample Databases

August 6, 2016

Microsoft has updated their sample database, retiring the old Adventure Works. The new set is for Wide World Importers.
Backups for the new database is at:

There is a standard database, plus a database warehouse version, as well as backups for different versions (Standard, Enterprise, etc.).

SQL Server 2016 – Query Store

July 9, 2016

Just a quick introduction to the Query Store feature. Query Store is a feature new to SQL Server 2016 that will capture data on query performance. Once it is enabled, we’ll have access to dashboards to track query plans as well as statistics on query execution.
I’m using SQL Server 2016 RTM Developer Edition.

Set Up:
Query Store is enabled at the database level. Going to the database properties you’ll see a ‘Query Store’ page. Setting ‘Operation Mode (requested)’ from ‘Off’ to ‘Read Write’ will enable this functionality. You’re also able to set the max space taken up, among other settings.
Once enabled, you can go back to SSMS and you’ll see a ‘Query Store’ tree under the database.

Under the ‘Query Store’ tree in SSMS, we’ll see four entries. These are for available dashboards to return data on our query performance and query plans.
– Regressed Queries: Returns queries that have become worse performing over time. We can select from certain criteria and view query plans to determine issues.
– Overall Resource Consumption: A summary of query stats for Duration, Execution Count, CPU Time and Logical Read over a set period of time.
– Top Resource Consuming Queries: We can select our criteria, such as duration, CPU Time, Physical Reads, etc. and see the worst performing queries by that criteria.
– Tracked Queries: Takes a query ID as a parameter – Returns query plans, the time the query was executed along with the average duration.

Simple Talk

SQL Server 2016 – More New T-SQL Features

June 30, 2016

An earlier post went over some new T-SQL features for SQL Server 2016. Here are two more new string functions that could be useful. I’m using SQL Server 2016 RTM Developer Edition.

The String_Split function will take a delimited list and return each value in a one column table.

declare @List varchar(100) = 'Matt Ryan,Julio Jones,Vic Beasley,Desmond Trufant';
select value from string_split(@List, ',');

String_Split – MSDN

This function will take a string and escape special characters for a certain format. Currently, the only format supported is for JSON.

select STRING_ESCAPE('She said "OK"', 'json');

String_Escape – MSDN