One of Kronos’ features is the ability to build ad-hoc reports. With SQL Server, the ability for a power-user to generate almost any type of report exists. The issue often arises that while you get your report — eventually; the performance time is not ideal. One part of the solution is the use of read-committed snapshot isolation (RCSI), which eliminates waiting for locks to be released on the tables. Another part of the solution is improving the TSQL used to generate the report.
Kronos reports often include hierarchical rollups that are recursive operations (some developers would say re-cursing). Last week, Shaun Tinline-Jones posted a very nice technical post on one way of optimizing a recursive query, Optimize Recursive CTE Query. Before you panic over the jargon term “CTE”, I should explain it.
CTE stands for Common Table Expression. This is equivalent to a classic #TEMP table with many extra features and easier to use. A simple example may help illustrate the similarities.
The classic #Temp table approach would be something like this:
-- Definte the TEMP Table Create table #Sales_Temp (SalesPersonID int, SalesOrderID int, SalesYear int) -- Populate the Temp Table INSERT INTO #Sales_Temp (SalesPersonID, SalesOrderID, SalesYear) -- Define the data fo SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL -- Definte the reporting query SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear FROM #Sales_Temp GROUP BY SalesYear, SalesPersonID ORDER BY SalesPersonID, SalesYear; -- dispose of the temp table Drop table #Sales_Temp
The CTE version would be much shorter and simpler:
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS -- Define the CTE query. ( SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL ) -- Define the outer query referencing the CTE name. SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear FROM Sales_CTE GROUP BY SalesYear, SalesPersonID ORDER BY SalesPersonID, SalesYear;
The post is a technical post and may take a little slugging through but with a reward of taking just 4 seconds to execute a report that took 144 seconds prior, it is worth the effort.
If you are interested in a series illustrating CTE usage more, just leave a comment below.