Skip to main content
deleted 214 characters in body
Source Link
Randi Vertongen
  • 16.5k
  • 4
  • 35
  • 64

When running the query outside of the function to look at the query plans, some differences are found

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

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

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

A difference between function 1 and 2 appears as the dataset + matches increases!


When running the query outside of the function to look at the query plans, some differences are found

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

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

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

A difference between function 1 and 2 appears as the dataset + matches increases!


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

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

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

added 80 characters in body
Source Link
Randi Vertongen
  • 16.5k
  • 4
  • 35
  • 64

RunningWhen running the query outoutside of the functionsfunction to look at the query plans, sample data and other tests + possible rewrite can besome differences are found below

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 11;
    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;

Here it gets more clear what extra operations are happening on one side

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

While the second function ,Function V2 has none of that

ANo 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:

The same result as V2 is seen

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 nested loop operators to check for the IS NULL will increase CPU usage. To

To answer your question, yes the IF statement should increase your CPU usage, as a result of the added NESTED LOOPS operatoradditional operators.

Running the query out of the functions to look at the query plans, sample data and other tests + 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;

Here it gets more clear what extra operations are happening on one side

Nested loop left outer join to facilitate the IF check

While the second function , V2 has none of that

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

The same result as V2 is seen

enter image description here

Which should explain the increased worker time, since adding the nested loop operators 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 added NESTED LOOPS operator

When running the query outside of the function to look at the query plans, some differences are found

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;

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

Function V2

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:

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 nested loop operators 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.

Source Link
Randi Vertongen
  • 16.5k
  • 4
  • 35
  • 64

Running the query out of the functions to look at the query plans, sample data and other tests + 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;

Here it gets more clear what extra operations are happening on one side

Function V1:

enter image description here

Nested loop left outer join to facilitate the IF check

While the second function , V2 has none of that

enter image description here

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 );

The same result as V2 is seen

enter image description here

Which should explain the increased worker time, since adding the nested loop operators 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 added NESTED LOOPS operator


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.

A difference between function 1 and 2 appears as the dataset + matches increases!