Saturday, April 12, 2008

Dynamic TSQL vs. CASE

One of the major purposes I have seen people using dynamic SQL is to have dynamic sorting operations. This means, the user should have options to sort a dataset using any column he desires and order (either ascending or descending) of his choice. I have seen many developers using EXEC to solve this, and in turn end up solutions that don’t cache and complicated. I will cover the EXEC vs. sp_executesql in another post.

Following is an example how you can use CASE to replace dynamic sql. I send the following script as an example to one of my friends. The intention was to have a CASE solution for a dynamic sort.

use Tempdb
go
create table MyEmp(
EmpFirstName varchar(25), EmpLastName varchar(25), EmpLocation varchar(25)
)
go

insert into MyEmp values ('TOM', 'JOHN', 'COK')
insert into MyEmp values ('MOHIT', 'MATHEW', 'TRV')
insert into MyEmp values ('JERRY', 'EAPEN', 'TRV')
insert into MyEmp values ('PETER', 'DANIEL', 'TRV')
insert into MyEmp values ('BIJU', 'KURIAN', 'BOM')
insert into MyEmp values ('JOHN', 'MATHEW', 'BOM')
insert into MyEmp values ('TOM', 'PAUL', 'BOM')
insert into MyEmp values ('TOM', 'DANIEL', 'MAA')
insert into MyEmp values ('TOM', 'EAPEN', 'MAA')
go

if exists(select name from sys.objects where type = 'P' and name = 'SortTest')
DROP PROC SortTest
go
create procedure SortTest @SortBy varchar(25), @SortDir varchar(4)
as
begin
select EmpFirstName, EmpLastname, EmpLocation from MyEmp
order by
case @SortDir
when 'ASC' then
case @SortBy
when 'EmpFirstName' then EmpFirstName
when 'EmpLastName' then EmpLastName
when 'EmpLocation' then EmpLocation
else EmpFirstName
end
end ASC,
case @SortDir
when 'DESC' then
case @SortBy
when 'EmpFirstName' then EmpFirstName
when 'EmpLastName' then EmpLastName
when 'EmpLocation' then EmpLocation
else EmpFirstName
end
end
DESC
End
go

EXEC SortTest @SortBy = 'EmpLocation', @SortDir = 'DESC'
go

It gave me the result set as below.

MOHIT MATHEW TRV
JERRY EAPEN TRV
PETER DANIEL TRV
TOM DANIEL MAA
TOM EAPEN MAA
TOM JOHN COK
BIJU KURIAN BOM
JOHN MATHEW BOM
TOM PAUL BOM

Now he came back to me saying that in his application he is getting erros as follows –
“Msg 8115, Level 16, State 2, Procedure sspGetExceptionReportListn, Line 40
Arithmetic overflow error converting expression to data type smalldatetime.”
My bad, I didn’t think about the data type. I rewrote the example and send as follows.
create table EmpDetails
(EmpId int, EmpFirstName varchar(25), EmpLastName varchar(25), EmpJoiningDate datetime)
GO
INSERT INTO EmpDetails VALUES(1, 'TOM', 'JOHN', '2007-08-01 00:00:00')
INSERT INTO EmpDetails VALUES(2, 'MATHEW', 'JOHN', '2007-08-01 00:00:00')
INSERT INTO EmpDetails VALUES(3, 'SYRIAC', 'JOHN', '2007-10-01 00:00:00')
INSERT INTO EmpDetails VALUES(4, 'TOM', 'CHANDY', '2006-07-01 00:00:00')
INSERT INTO EmpDetails VALUES(5, 'IYPE', 'JOHN', '2005-05-01 00:00:00')
INSERT INTO EmpDetails VALUES(6, 'TOM', 'JOSEPH', '2006-09-01 00:00:00')
INSERT INTO EmpDetails VALUES(7, 'LOPUS', 'ALEXANDER', '2001-01-01 00:00:00')
INSERT INTO EmpDetails VALUES(8, 'JIM', 'MATHEW', '2007-08-01 00:00:00')
INSERT INTO EmpDetails VALUES(9, 'ANIL', 'MATHEW', '2007-08-01 00:00:00')
INSERT INTO EmpDetails VALUES(10, 'JOSEPH', 'THOMAS', '2007-08-01 00:00:00')
INSERT INTO EmpDetails VALUES(10, 'ANTONY', 'GOMAS', '2007-08-01 00:00:00')
INSERT INTO EmpDetails VALUES(10, 'CHRISTOPHER', 'THOMAS', '2007-08-01 00:00:00')
GO

IF EXISTS(SELECT NAME FROM SYS.OBJECTS WHERE TYPE = 'P' AND NAME = 'SortEmpDetails')
DROP PROC SortEmpDetails
GO
CREATE PROCEDURE SortEmpDetails @SortColumn varchar(25), @SortDirection varchar(4) AS
SELECT EmpId, EmpFirstName, EmpLastName, EmpJoiningDate FROM EmpDetails
ORDER BY
CASE @SortDirection
WHEN 'DESC' THEN
CASE @SortColumn
WHEN 'EmpId' THEN CAST(EmpId AS INT)
WHEN 'EmpFirstName' THEN EmpFirstName
WHEN 'EmpLastName' THEN EmpFirstName
WHEN 'EmpJoiningDate' THEN CAST(EmpJoiningDate AS DATETIME)
END
END DESC,
CASE @SortDirection
WHEN 'ASC' THEN
CASE @SortColumn
WHEN 'EmpId' THEN CAST(EmpId AS INT)
WHEN 'EmpFirstName' THEN EmpFirstName
WHEN 'EmpLastName' THEN EmpFirstName
WHEN 'EmpJoiningDate' THEN CAST(EmpJoiningDate AS DATETIME)
END
END ASC
GO

EXEC SortEmpDetails @SortColumn = 'EmpJoiningDate', @SortDirection = 'DESC'
I got a result set as follows –
3 SYRIAC JOHN 10/1/07 12:00 AM
1 TOM JOHN 8/1/07 12:00 AM
2 MATHEW JOHN 8/1/07 12:00 AM
8 JIM MATHEW 8/1/07 12:00 AM
9 ANIL MATHEW 8/1/07 12:00 AM
10 JOSEPH THOMAS 8/1/07 12:00 AM
10 ANTONY GOMAS 8/1/07 12:00 AM
10 CHRISTOPHER THOMAS 8/1/07 12:00 AM
6 TOM JOSEPH 9/1/06 12:00 AM
4 TOM CHANDY 7/1/06 12:00 AM
5 IYPE JOHN 5/1/05 12:00 AM
7 LOPUS ALEXANDER 1/1/01 12:00 AM

Yes, the next obvious question was what is the cost involved with the cast we do; yes nothing comes for free. I did a small test on how costly will the cast become. The results I got showed that the cast can be costly if the column that is being casted has a clustered index, the cast operation will override the advantages of a direct index pages read. This is because it scans the index to get the data first, and then does the cast and then does a sort operation to get the final results. The test I did was with a dummy table that has 55,000 records. Following were the readings –

Column - Without Cast - With Cast
Column with Clustered Index - 0.22 milliseconds - 4.18 milliseconds
Column with Non- Clustered Index - 4.17 milliseconds - 4.18 milliseconds

So, this means, casting and sorting for a clustered index reduces performance, while a non clustered index will not be affected much. Anyways, the total performance depends upon how large the table is.

No comments: