I wrote 2 stored procedures that read the same data depending on their parameters, one using static query sp_Order_Read1
, and the other using a dynamic one sp_Order_Read2
. They read the data of Orders
table in Northwind
sample database; on SQL Server 2012
.
sp_Order_Read1
:
create proc sp_Order_Read1 @OrderID int, @page int, @pageLength int
as begin
if @OrderID is null
begin
if @page is not null and @pageLength is not null
select OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry
from Orders o
order by o.OrderID
offset (@page - 1) * @pageLength rows
fetch next @pageLength rows only
else
select OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry
from Orders o
end
else
begin
if @page is not null and @pageLength is not null
select OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry
from Orders o
where o.OrderID = @OrderID
order by o.OrderID
offset (@page - 1) * @pageLength rows
fetch next @pageLength rows only
else
select OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry
from Orders o
where o.OrderID = @OrderID
end
end
sp_Order_Read2
:
create proc sp_Order_Read2 @OrderID int, @page int, @pageLength int
as begin
declare @query nvarchar(max) = ''
if @OrderID is not null
begin
declare @OrderIDExpression nvarchar(max)
if IsNumeric(@OrderID) = 1
set @OrderIDExpression = @OrderID
else
set @OrderIDExpression = '''' + @OrderID + ''''
set @query += 'declare @OrderID int = ' + @OrderIDExpression + ';
'
end
if @page is not null and @pageLength is not null
begin
set @query += 'declare @page int = ' + convert(nvarchar(max), @page) + ';
declare @pageLength int = ' + convert(nvarchar(max), @pageLength) + ';
'
end
set @query +=
'select OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry
from Orders o'
if @OrderID is not null
set @query += '
where'
if @OrderID is not null
set @query += '
OrderID = @OrderID'
if @page is not null and @pageLength is not null
set @query += '
order by o.OrderID
offset (@page - 1) * @pageLength rows
fetch next @pageLength rows only'
exec(@query)
end
* Note that I wrote a tool that generates the static/dynamic syntax for me, so writing either won't be a big deal.
I couldn't decide which syntax will run faster so I decided to test them. I created temp table #comparison
to hold the results.
create table #comparison
(
ID int identity,
One int,
Two int
)
And the test was this script:
declare @counter int = 1
declare @count int = 50
declare @beginning1 datetimeoffset
declare @end1 datetimeoffset
declare @beginning2 datetimeoffset
declare @end2 datetimeoffset
while(@counter <= @count)
begin
set @beginning1 = sysdatetimeoffset()
exec sp_Order_Read1 null, 5, 50
set @end1 = sysdatetimeoffset()
set @beginning2 = sysdatetimeoffset()
exec sp_Order_Read2 null, 5, 50
set @end2 = sysdatetimeoffset()
insert #comparison (One, Two) values (datediff(microsecond, @beginning1, @end1), datediff(microsecond, @beginning2, @end2))
set @counter += 1
end
I ran it 5 times, after each one I watched the results with this query:
select count(ID) as [Row Count], (sum(One) - sum(Two)) as [Difference] from #comparison
Then I switched the order in the test script to be like that:
declare @counter int = 1
declare @count int = 50
declare @beginning1 datetimeoffset
declare @end1 datetimeoffset
declare @beginning2 datetimeoffset
declare @end2 datetimeoffset
while(@counter <= @count)
begin
set @beginning2 = sysdatetimeoffset()
exec sp_Order_Read2 null, 5, 50
set @end2 = sysdatetimeoffset()
set @beginning1 = sysdatetimeoffset()
exec sp_Order_Read1 null, 5, 50
set @end1 = sysdatetimeoffset()
insert #comparison (One, Two) values (datediff(microsecond, @beginning1, @end1), datediff(microsecond, @beginning2, @end2))
set @counter += 1
end
I have also ran it 5 times, and after each one I watched the results using the same query. The test results was as the following:
Row Count Difference
---------------------------
100 439119
200 35963
300 2211699
400 14443
500 -2666605
--Reversed Excution Order--
600 5846095
700 13221704
800 21094312
900 28361390
1000 37526611
The results were quite weird and unsatisfying for me; so, if anyone could help me decide which syntax should run faster giving satisfying reasoning, I'd be thankful.