November 3, 2008 at 2:09 pm
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 ?
November 3, 2008 at 8:42 pm
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
Change is inevitable... Change for the better is not.
November 3, 2008 at 9:34 pm
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]
November 4, 2008 at 3:42 pm
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.
November 4, 2008 at 4:30 pm
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
Change is inevitable... Change for the better is not.
November 6, 2008 at 12:59 pm
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