IF EXISTS vs if @@rowcount

  • IF EXISTS vs if @@rowcount:

    With IF EXISTS,

    Clustered Index Update cost is 304%,

    Clustered Index Insert cost is 100%,

    view below:

    -- Example 1: (IF EXISTS, update)

    --sp_ins_tblEntity_Add_Info

    declare

    @Entity_Id char(8), @Add_Info_Id int, @Info_Value varchar(100),

    @userid varchar(50), @Update_date datetime

    IF EXISTS(Select * from tblEntity_Add_Info_Data

    Where Entity_Id = 'SIB00040' and Add_Info_Id = 34)

    BEGIN

    update tblEntity_Add_Info_Data

    Set

    Info_Value = 26,

    UserId = 'test' ,

    Update_date = '2008-09-21'

    WHERE Entity_Id = 'SIB00040'

    and Add_Info_Id = 34

    END

    ELSE

    BEGIN

    insert tblEntity_Add_Info_Data

    (

    --tblEntity_Add_info_PK,

    Entity_Id,

    Add_Info_Id,

    Info_Value,

    UserId,

    Update_date

    )

    values

    (

    --@tblEntity_Add_info_PK,

    'SIB00040' ,

    34 ,

    26 ,

    'test' ,

    '2008-09-23'

    )

    END

    ==================

    ***

    With IF EXISTS,

    Clustered Index Update cost is 404%,

    Clustered Index Insert cost is 304%,

    view below:

    -- Example 2: (IF EXISTS, update)

    DECLARE @rc INT

    BEGIN TRAN

    IF EXISTS (SELECT * FROM t1 WHERE id = 6)

    BEGIN

    UPDATE t1

    SET name1 = 'name 6'

    WHERE id = 6

    END

    ELSE

    BEGIN

    INSERT INTO t1

    SELECT 6, 'name 6'

    END

    ROLLBACK

    GO

    ==================

    ***

    With if @@rowcount,

    Clustered Index Update cost is 75%,

    Clustered Index Insert cost is 100%,

    view below:

    -- Example 3: (if @@rowcount = 0, insert)

    --sp_ins_tblEntity_Add_Info

    declare

    @Entity_Id char(8), @Add_Info_Id int, @Info_Value varchar(100),

    @userid varchar(50), @Update_date datetime

    BEGIN

    update tblEntity_Add_Info_Data

    Set

    Info_Value = 26,

    UserId = 'test' ,

    Update_date = '2008-09-21'

    WHERE Entity_Id = 'SIB00040'

    and Add_Info_Id = 34

    END

    if @@rowcount = 0

    BEGIN

    insert tblEntity_Add_Info_Data

    (

    --tblEntity_Add_info_PK,

    Entity_Id,

    Add_Info_Id,

    Info_Value,

    UserId,

    Update_date

    )

    values

    (

    --@tblEntity_Add_info_PK,

    'SIB00040' ,

    34 ,

    26 ,

    'test' ,

    '2008-09-23'

    )

    END

    ==================

    ***

    With if @@rowcount,

    Clustered Index Update cost is 75%,

    Clustered Index Insert cost is 100%,

    view below:

    -- Example 4: (if @@rowcount > 0, update)

    --sp_ins_tblEntity_Add_Info

    declare

    @Entity_Id char(8), @Add_Info_Id int, @Info_Value varchar(100),

    @userid varchar(50), @Update_date datetime

    if @@rowcount > 0

    update tblEntity_Add_Info_Data

    Set

    Info_Value = 26,

    UserId = 'test' ,

    Update_date = '2008-10-16'

    WHERE Entity_Id = 'SIB00040'

    and Add_Info_Id = 34

    ELSE

    insert tblEntity_Add_Info_Data

    (

    --tblEntity_Add_info_PK,

    Entity_Id,

    Add_Info_Id,

    Info_Value,

    UserId,

    Update_date

    )

    values

    (

    --@tblEntity_Add_info_PK,

    'SIB00040' ,

    34 ,

    26 ,

    'test' ,

    '2008-09-23'

    )

    ==================

    ***

    With if @@rowcount,

    Clustered Index Update cost is 100%,

    Clustered Index Insert cost is 100%,

    view below:

    -- Example 5: (if @@rowcount = 0, insert)

    DECLARE @rc INT

    BEGIN TRAN

    -- update the row with id = 6.

    UPDATE t1

    SET name1 = 'name 6'

    WHERE id = 6

    SELECT @rc = @@ROWCOUNT

    IF @rc = 0

    BEGIN

    INSERT INTO t1

    SELECT 6, 'name 6'

    END

    ROLLBACK

    GO

    ***

    Query performance relating to index usage (estimated query plan) is more efficient using if @@rowcount than IF EXISTS

    Question:

    What do you suggest ?

  • I would suggest you stop looking at costs in either the estimated or actual execution plans because it is frequently very wrong.

    For example, given the following test data...

    DROP TABLE JBMTest

    GO

    --===== Create and populate a 1,000,000 row test table.

    -- Column "RowNum" has a range of 1 to 1,000,000 unique numbers

    -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

    -- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

    -- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

    -- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

    -- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'

    -- for all rows.

    -- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

    -- Jeff Moden

    SELECT TOP 1000000

    RowNum = IDENTITY(INT,1,1),

    SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

    SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

    + CHAR(ABS(CHECKSUM(NEWID()))%26+65),

    SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),

    SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

    SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),

    SomeHex12 = RIGHT(NEWID(),12)

    INTO dbo.JBMTest

    FROM Master.dbo.SysColumns t1

    CROSS JOIN Master.dbo.SysColumns t2

    --===== A table is not properly formed unless a Primary Key has been assigned

    ALTER TABLE dbo.JBMTest

    ADD PRIMARY KEY CLUSTERED (RowNum)

    ... and given the following test code...

    SET STATISTICS TIME ON

    SELECT TOP 10 *

    FROM dbo.JBMTest

    ORDER BY SomeLetters2

    SET ROWCOUNT 10

    SELECT *

    FROM dbo.JBMTest

    ORDER BY SomeLetters2

    SET ROWCOUNT 0

    SET STATISTICS TIME OFF

    ... both sections of the test code show identical estimated execution plans, actual execution plans, and exact matches on all comparable costs. But, when you run it, you'll notice that the second snippet takes much longer to run and is documented as using more than 3 times the CPU resources by the STATISTICS TIME...

    [font="Courier New"]

    (10 row(s) affected)

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 2547 ms, elapsed time = 2837 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    (10 row(s) affected)

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 9078 ms, elapsed time = 11398 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    [/font]

    If you really want to see something totally out of whack for % of Batch and "costs", examing the following code... each section creates 30 years of dates and dumps it into a variable to test true speed without the display getting involved. Talk about a reversal of fortune!!! Check out the execution plans and "% of Batch" on these babies... 😀

    SET NOCOUNT ON

    --=======================================================================================

    -- Recursive method shown by (Name with-held)

    --=======================================================================================

    PRINT '========== Recursive method =========='

    --===== Turn on some performance counters ===============================================

    SET STATISTICS IO ON

    SET STATISTICS TIME ON

    DECLARE @Bitbucket DATETIME --Holds display output so display times aren't measured.

    --===== Execute the code being tested ===================================================

    DECLARE @DateVal DATETIME

    SET @DateVal = '2008-01-01'

    ;with mycte as

    (

    select @DateVal AS DateVal

    union all

    select DateVal + 1

    from mycte

    where DateVal + 1 < DATEADD(yy, 30, @DateVal)

    )

    select @Bitbucket = d.dateval

    from mycte d

    OPTION (MAXRECURSION 0)

    --===== Turn off the performance counters and print a separator =========================

    SET STATISTICS TIME OFF

    SET STATISTICS IO OFF

    PRINT REPLICATE('=',90)

    GO

    --=======================================================================================

    -- Tally table method by Jeff Moden

    --=======================================================================================

    PRINT '========== Tally table method =========='

    --===== Turn on some performance counters ===============================================

    SET STATISTICS IO ON

    SET STATISTICS TIME ON

    DECLARE @Bitbucket DATETIME --Holds display output so display times aren't measured.

    --===== Execute the code being tested ===================================================

    DECLARE @StartDate AS DATETIME

    SET @StartDate = '2008-01-01'

    SELECT TOP (DATEDIFF(dd,@StartDate,DATEADD(yy,30,@StartDate)))

    @Bitbucket = @StartDate-1+t.N

    FROM Tally t

    ORDER BY N

    --===== Turn off the performance counters and print a separator =========================

    SET STATISTICS TIME OFF

    SET STATISTICS IO OFF

    PRINT REPLICATE('=',90)

    Of course, you'll need a Tally table with 11,000 rows in it to do the above 30 year test...

    --===== Create and populate the Tally table on the fly

    SELECT TOP 11000 --equates to more than 30 years of dates

    IDENTITY(INT,1,1) AS N

    INTO dbo.Tally

    FROM Master.dbo.SysColumns sc1,

    Master.dbo.SysColumns sc2

    --===== Add a Primary Key to maximize performance

    ALTER TABLE dbo.Tally

    ADD CONSTRAINT PK_Tally_N

    PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

    --===== Allow the general public to use it

    GRANT SELECT ON dbo.Tally TO PUBLIC

    Bottom line is... use the estimated and actual execution plans to see what is being used. Don't use it for how it is being used or for determining costs because a lot of times, it's just flat out wrong. As good ol' Pat might say... "Must Look Eye!". 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • As I said the first time that you asked this question:

    clive (10/31/2008)


    What is the preferred option, if exists or if @@rowcount.

    If @@ROWCOUNT is already set then you should use it. Otherwise, use EXISTS() ...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Hi Jeff, thanks for the thorough explanation.

    I usually use execution plans to check for table scans, if I find a table scan relating to a table, the table normally does not have an index, after adding index to table, I use execution plan again, this time no table scan, query performance is much improved. I use execution plan for single stored procedure.

    CREATE TABLE Phone (

    Phone_ID int NOT NULL IDENTITY(1,1),

    Contract_No int NOT NULL,

    Make varchar(20) NULL,

    Model_No int NULL,

    Year smalldatetime NULL,

    Warranty varchar(20) NULL,

    User_ID varchar(20) NULL,

    City varchar(20) NULL,

    Status varchar(10) NULL

    )

    SELECT * FROM Phone

    WHERE Contract_No = @Contract_No

    AND Status = 'Active'

    Question1:

    when creating an index on phone table, which columns should be used by clustered index,

    and which columns should be used by non-clustered index.

    Question2:

    Can you run all the application stored procedures against execution plan in one go, or must I test every stored procedure against execution plan separately.

  • I'd probably make a Clustered PK on Phone_ID.

    I'd seriously question why User_ID is going to be stored as a VARCHAR(20) but believe it would also need an index.... depends on the queries.

    I know of no way to test for indexes for all queries unless they are all in the same batch for testing.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • CREATE TABLE Phone (

    Phone_ID int IDENTITY(1,1) NOT NULL,

    Contract_No int NOT NULL,

    Make varchar(20) NULL,

    Model_No int NULL,

    Year smalldatetime NULL,

    Warranty varchar(20) NULL,

    User_ID varchar(10) NULL,

    City varchar(20) NULL,

    Status varchar(10) NULL

    )

    Query1:

    SELECT Contract_No, Make, Model_No, Year

    FROM Phone

    WHERE Contract_No = @Contract_No AND Status = 'Active'

    Query2:

    SELECT Contract_No, Make, Model_No, Status

    FROM Phone

    WHERE Contract_No = @Contract_No AND Year > '2007-12-31'

    Query3:

    SELECT Contract_No, Make, Model_No

    FROM Phone

    WHERE Contract_No = @Contract_No

    Since Phone_ID has a primary key, it has Clustered index already.

    I assume non-clustered index will contain columns Contract_No, Make, Model_No

    Regards

    Kevin

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply