Sql-server – Why does elimination of an if-statement cause notable speed up in the function

azure-sql-databaseexecution-planoptimizationquery-performancesql server

I have the following table:

CREATE TABLE [dbo].[StateChanges]
(
    [RecordId] uniqueidentifier NOT NULL,
    [LinkedObjectId] uniqueidentifier NOT NULL,
    [ChangeType] TINYINT not null,
    [ChangeTime] DATETIME not null,
    -- some other columns too
)

with the following indices:

-- yes, clustered over GUID. I know it's not very good.
CREATE CLUSTERED INDEX StateChangesIndex ON
    [StateChanges](RecordId)
GO

CREATE INDEX LastChangeIndex_Object_Type_Time] ON 
    [StateChanges] (LinkedObjectId, ChangeType, ChangeTime)
    INCLUDE(SomeColumnsHere)
GO

And I have a function that goes like this initially:

CREATE FUNCTION [dbo].[ObjectHasInterestingChanges_v1]
(
    @objectId uniqueidentifier
)
RETURNS BIT
AS
BEGIN
    IF (SELECT TOP(1) 1 FROM StateChanges
        WHERE
    LinkedObjectId=@objectId AND
            (ChangeType = 1 OR ChangeType = 2 ) )
        IS NOT NULL
    BEGIN
        RETURN 1;
    END
    RETURN 0;
END

This function is being called for a large number of rows processed by an outer query – one thousand time for the very same three thousand rows.

If I try to SELECT ObjectHasInterestingChanges_v1(something) all I have is a "constant scan" in the plan which isn't interesting so I extract the contents into a separate query and look into the execution plan. The plan is as follows initially:

  • "index scan" over LastChangeIndex_Object_Type_Time – as expected
  • "top" of "index scan result"
  • "left outer join" result of "top" with some "constant scan" result
  • "compute scalar" of "left outer join result"
  • "cond with query" of "compute scalar"
  • that's it

I look into sys.dm_exec_query_stats table and note the number of execution for this function, number of reads and time taken.

I then change the function like this:

CREATE FUNCTION [dbo].[ufn_ObjectHasInterestingChanges_v2]
(
    @objectId uniqueidentifier
)
RETURNS BIT
AS
BEGIN
    DECLARE @result BIT
    SET @result = 0
    SELECT TOP(1) @result=1 FROM StateChanges
        WHERE LinkedObjectId=@objectId AND
           (ChangeType = 1 OR ChangeType = 2 )
    RETURN @result;
END

It yields exactly the same results. If I look into the plan it's the following now:

  • "index seek"
  • "top"
  • "compute scalar"
  • "select"
  • that's it

I run the very same outer query in the same database such that exactly the same data is being processed and exactly the same data is being read and the background workload is exactly the same (zero) and so I'm sure that the comparison is fair and reasonable.

I look into sys.dm_exec_query_stats again. I see that

  • the number of executions is the same for v1 and v2 – large number close to three millions
  • the number of logical reads, physical reads and physical writes is the same for v1 and v2
  • "worker time" is 53.5 seconds for v1 and 48.3 seconds for v2 – 10 percent speed up
  • "total time" is 86 seconds for v1 and 67 seconds for v2 – 22 percent speed up

Why is this? Even if "worker time" is decreased because if-statements are somehow time consuming why would "total time" decrease even further? "total time" also accounts for I/O and "total time" decreasing more than "worker time" would imply some I/O speedup which is completely unreasonable here.

Where does this notable difference in both "worker time" and "total time" come from after such minor code change?

Best Answer

Sample data, tests with the actual functions + possible rewrite can be found below

DECLARE @objectid uniqueidentifier
select top(1) @objectid = GUIDS
from #TEMP;

-- function 1 ,omitted the return 0

IF (SELECT TOP(1) 1 FROM StateChanges
        WHERE
    LinkedObjectId=@objectId AND
            (ChangeType = 1 OR ChangeType = 2 ) )
        IS NOT NULL
    BEGIN
      SELECT 1;
    END

-- Function 2 changed the returns

    DECLARE @result BIT
    SET @result = 0
    SELECT TOP(1) @result=1 FROM StateChanges
        WHERE LinkedObjectId=@objectId AND
           (ChangeType = 1 OR ChangeType = 2 )
    SELECT @result;

Function V1:

enter image description here

A Nested loop left outer join is present in the plan, as to facilitate the IF ... IS NOT NULL check

Function V2

enter image description here

No nested loop operator, a more simple, straightforward plan

As seen, a nested loop left join was added because of the IF check, if we execute the query without the IF statement:

DECLARE @objectid uniqueidentifier
select top(1) @objectid = GUIDS
from #TEMP;
SELECT TOP(1) 1 FROM StateChanges
        WHERE
    LinkedObjectId=@objectId AND
            (ChangeType = 1 OR ChangeType = 2 );

enter image description here

No more Nested loops and the same plan as V2 is seen

Which should explain the increased worker time, since adding the NL join to check for the IS NULL will increase CPU usage.

To answer your question, yes the IF statement should increase your CPU usage, as a result of the additional operators.

Testing with the previous defined tables, indexes, functions and adding some data

CREATE TABLE #TEMP(GUIDS uniqueidentifier)
DECLARE @GUID uniqueidentifier 
declare @i int =1
WHILE @i <= 1000
BEGIN

SET @GUID = NEWID()
INSERT INTO #temp(GUIDS)
VALUES(@GUID)
SET @I += 1
END

SET NOCOUNT OFF;
INSERT INTO [dbo].[StateChanges]([RecordId],[LinkedObjectId], [ChangeType],[ChangeTime], SomeColumnsHere )
SELECT GUIDS, GUIDS , 5, getdate(), 'Blabla'
from   #TEMP

Testing the functions

SELECT [dbo].[ObjectHasInterestingChanges_v1](GUIDS)
FROM #temp;

SQL Server Execution Times: CPU time = 125 ms, elapsed time = 125 ms.

SELECT [dbo].ufn_ObjectHasInterestingChanges_v2(GUIDS)
FROM #temp ;

SQL Server Execution Times: CPU time = 125 ms, elapsed time = 132 ms.

they appear to get the same CPU time with a low dataset and no matches (nothing matches because Changetype is not 1 or 2 in my data).

On another note, you could change the function to outer apply

SELECT ISNULL(a.bla,0)
FROM #TEMP 
OUTER APPLY
(SELECT TOP(1) 1 as bla  FROM StateChanges
        WHERE
    LinkedObjectId=GUIDS AND
            (ChangeType = 1 OR ChangeType = 2 ) ) as a

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 5 ms.

Test 2, adding some mathing data 

DECLARE @GUID uniqueidentifier 
declare @i int =1
WHILE @i <= 1000
BEGIN

SET @GUID = NEWID()
INSERT INTO #temp(GUIDS)
VALUES(@GUID)
SET @I += 1
END

SET NOCOUNT OFF;
INSERT INTO [dbo].[StateChanges]([RecordId],[LinkedObjectId], [ChangeType],[ChangeTime], SomeColumnsHere )
SELECT GUIDS, GUIDS ,2, getdate(), 'Blabla'
from   #TEMP;

Testing the functions and the rewrite again

SELECT ISNULL(a.bla,0)
FROM #TEMP 
OUTER APPLY
(SELECT TOP(1) 1 as bla  FROM StateChanges
        WHERE
    LinkedObjectId=GUIDS AND
            (ChangeType = 1 OR ChangeType = 2 ) ) as a;

SQL Server Execution Times: CPU time = 31 ms, elapsed time = 21 ms.

SELECT [dbo].[ObjectHasInterestingChanges_v1](GUIDS)
FROM #temp;

SQL Server Execution Times: CPU time = 375 ms, elapsed time = 378 ms.

SELECT [dbo].ufn_ObjectHasInterestingChanges_v2(GUIDS)
FROM #temp ;

SQL Server Execution Times: CPU time = 344 ms, elapsed time = 352 ms.

Related Question