For the past few days, I've been working on a stored procedure that was the top offender for CPU usage. I tried various index changes, played around with index hints, etc. Everything I tried either made very little improvement or no improvement.

I was ready to throw in the towel, but then I decided to give the code one last look. I don't have the business knowledge for this particular system, so I had been avoiding the complex stored procedure that has over 200 lines of code. One of the queries has 8 joins, and 2 of those are to views that also contain joins. My head about exploded when I first looked at it.

But then I took a step back and narrowed down where the performance problem was. It was a view that was using SUM, MIN, MAX and ROW_NUMBER functions. It was performing those functions on a large data set, and then the stored procedure filtered it using the input parameters.

My code change was to remove the functions from the view and instead calculate those in the stored procedure.

This is a very simple example:

Original

CREATE VIEW View1
AS 
SELECT 
    SUM(Column3) OVER(PARTITION BY Column6, Column7) AS SumColumn3, 
    ROW_NUMBER() OVER(PARTITION BY Column6, Column7) AS ViewRowNumber,
    MIN(Column4) OVER(PARTITION BY Column8) AS MinColumn4, 
    MAX(Column4) OVER(PARTITION BY Column8) AS MaxColumn4 
FROM Table1 
WHERE Column5 = 'SomeValue' AND Column9 = 0
CREATE PROC Proc1 (@var1 varchar(10), @var2 varchar(10))
AS
SELECT SumColumn3, ViewRowNumber, MinColumn4, MaxColumn4
FROM View1
WHERE Column1 = @var1 AND Column2 = @var2

Refactored

CREATE VIEW View1
AS 
SELECT Column3, Column4, Column6, Column7, Column8
FROM Table1  
WHERE Column5 = 'SomeValue' AND Column9 = 0
CREATE PROC Proc1 (@var1 varchar(10), @var2 varchar(10))
AS
SELECT
SUM(Column3) OVER(PARTITION BY Column6, Column7) AS SumColumn3,
ROW_NUMBER() OVER(PARTITION BY Column6, Column7) AS ViewRowNumber,
MIN(Column4) OVER(PARTITION BY Column8) AS MinColumn4,
MAX(Column4) OVER(PARTITION BY Column8) AS MaxColumn4
FROM View1
WHERE Column1 = @var1 AND Column2 = @var2

Here are my actual results:

BOOM!