Kronos: Improving the performance of ad-hoc reports up to 3600%

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.