Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IF EXISTS vs if @@rowcount Expand / Collapse
Author
Message
Posted Monday, November 3, 2008 2:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:40 PM
Points: 159, Visits: 454
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 ?
Post #596120
Posted Monday, November 3, 2008 8:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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...


(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.


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... :D

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!". :P


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #596275
Posted Monday, November 3, 2008 9:34 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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() ...


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #596293
Posted Tuesday, November 4, 2008 3:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:40 PM
Points: 159, Visits: 454
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.
Post #596960
Posted Tuesday, November 4, 2008 4:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #596980
Posted Thursday, November 6, 2008 12:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:40 PM
Points: 159, Visits: 454
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
Post #598487
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse