0

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.

Aly Elhaddad
  • 1,913
  • 1
  • 15
  • 31
  • The easy way is to execute both stored procedure in the same batch and check the execution plan. You should get an indication for each query what precentage of the total run time it was. – Zohar Peled May 09 '17 at 13:08
  • @ZoharPeled For `sp_Order_Read1`, Query cost (relative to the batch): 61%. For `sp_Order_Read2` it was 39%. It's quite embarrassing how didn't I think of that; however, this is still kinda strange. How come could the dynamic query be faster than the static one. If you post your comment as an answer, I'd accept it, unless some answer explain the "why" further. – Aly Elhaddad May 09 '17 at 13:37
  • You should not rely on % shown in the execution plan as it's estimated only. You'd better set statistics io, time on and compare the real values, not "estimated". – sepupic May 09 '17 at 19:48
  • @sepupic IO parameters are the same. Time parameters are variable but close. That's because the generated query is exactly the same as the static one. That's why I tested it in the way I explained in the question, because I'm deciding between the `exec(@query)` syntax and the static one. – Aly Elhaddad May 09 '17 at 20:40

0 Answers0