Oct 14, 2013

Dynamic ORDER BY clause in sql server


Dynamic ORDER BY clause in sql server: Query performance

Sometime we need to sort the data of a table according to parameter of a stored procedure. For example I am creating a table and inserting some records into it:

--Deleting tblEmployee table if it already present.
IF OBJECT_ID('tblEmployee') IS NOT NULL
     DROP TABLE tblEmployee

--Creating tblEmployee.
CREATE TABLE tblEmployee(
     ntEmpID BIGINT PRIMARY KEY IDENTITY,
     vcName VARCHAR(Max),
     ntAge INTEGER
)

--Create Non clustered index
CREATE NONCLUSTERED INDEX NCI_Age ON tblEmployee(ntAge)

--Inserting some records into it 
INSERT INTO tblEmployee
SELECT [Text], severity FROM Sys.messages 

Now I want to create a stored procedure, when we will pass 1 then it will sort according to ntAge column otherwise it will sort according to ntEmpID columns. It is very easy to write:

CREATE PROC uspGetEmp(
     @ntSort_Order AS INTEGER
)
AS
BEGIN
     SELECT
          ntAge, ntEmpID
     FROM tblEmployee
     ORDER BY
          CASE
              WHEN @ntSort_Order = 1 THEN ntAge
               ELSE  ntEmpID
          END
END
  
To execute:

EXECUTE uspGetEmp 1

Or

EXECUTE uspGetEmp 2

It works fine.

Let's check its plan how this sorting is performed dynamically. So I am enabling the show plan:

SET SHOWPLAN_ALL ON

Now I am executing the stored procedure :

EXECUTE uspGetEmp 1

Consider on the 4th row of StmtText:

Compute Scalar (DEFINE :( [Expr1003]=
CASE
WHEN [@ntSort_Order]=(1)
THEN CONVERT_IMPLICIT (bigint, [master].[dbo].[tblEmployee].[ntAge],0)
ELSE [master].[dbo].[tblEmployee].[ntEmpID]
END))

I have few problems in this execution plan:

1. We know the value of variable @ntSort_Order is 1 and for this select query its value will never change. Then why sql server is checking the value of variable @ntSort_Order is 1 or not for each row? If table has 1000k records then it will check 1000k times. Seems, It is unnecessary query cost.
  
2.  For each row sql server is type casting the column ntAge to BIGINT by using operator CONVERT_IMPLICIT since data type of column ntEmpID is BIGINT. But in this case there is not my need to type cast the ntAge to BIGINT.

So I am writing the above stored procedure in some different way:

CREATE PROC uspGetEmp_Second(
     @ntSort_Order AS INTEGER
)
AS
BEGIN
     IF @ntSort_Order = 1 BEGIN
          SELECT
              ntAge, ntEmpID
          FROM tblEmployee
          ORDER BY ntAge
     END ELSE BEGIN
          SELECT
              ntAge, ntEmpID
          FROM tblEmployee
          ORDER BY ntEmpID
     END
END

Now I am comparing the execution cost of above two stored procedures by using the actual execution plan:

EXECUTE uspGetEmp 1
EXECUTE uspGetEmp_Second 1

Actual execution plan:


Here execution cost of first procedure is 97% compare to the second procedure. It is due to second procedure has not any sorting cost since it is using the sorting of non clustered index NCI_Age.

Let's check it for different parameter:

EXECUTE uspGetEmp 2
EXECUTE uspGetEmp_Second 2

Actual execution plan:


Here execution cost of the first procedure is 85% compare to second procedure which is only 15%. It is due to second procedure has not any sorting cost since it is using the sorting of clustered index.

So we can conclude that first procedure is not able to reuse the sorting of index. 

No comments:

Post a Comment