September 25, 2008 at 5:10 am
HI All,
I was reading an article a few weeks back but can't seem to find it anymore so thought I'd would ask the question:
Which would you say is the better performing statement within a trigger:
I've only given an example for an INSERT, but the question applies to UPDATE/INSERT and DELETE
[font="Courier New"]
--STATEMENT 1
INSERT INTO CTSTest2
SELECT RowNum
FROM dbo.CTSTest
WHERE EXISTS(SELECT * FROM dbo.CTSTest WHERE LEN(SomeString) >10)
--STATEMENT 2
IF EXISTS(SELECT * FROM dbo.CTSTest WHERE LEN(SomeString) >10)
BEGIN
INSERT INTO CTSTest2
SELECT RowNum
FROM dbo.CTSTest
END [/font]
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 25, 2008 at 7:31 am
I vaguely recall this article as well. I think performance wise they are similar.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 25, 2008 at 7:33 am
The additional check if there are records to insert will hurt performance in a couple of ways. First, it is an extra statement every time there is something to insert. Second, when there is nothing to insert, the check in the IF statement is just as much overhead as the insert that inserts no records - so you will get no benefit here. Finally, in anything that can cache an execution plan, the conditional will only cache a plan for the first time it runs - so if there are no records to insert on the first execution, you will get a bad plan every time there are records to insert.
September 25, 2008 at 7:43 am
Also, the possibility exists that data is present when the IF part is processed, but deleted from the table prior to the insert.
😎
September 25, 2008 at 7:51 am
HI Lynn,
Ok what you saying is ringing a bell,
If I use the IF and someone else is doing an update then there is a good change it will pass/fail the if when it shouldn't...
interesting feedback from all of you thanks...
Keep it coming if you feel still have something to add 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 25, 2008 at 12:27 pm
This kind of problems comes along usually because of a *bad* workflow design. You should know when to insert,delete,update. That way your opperations are atomic. IF you still can't fix your workflow you will be forced to use exclusive LOCKs or a less concurrent Isolation mode to prevent the race condition posted above.
Here is an example of the required "tricks" :
If you are lucky enough to be using SQL2008 then MERGE is the answer. Still slower and bulkier than fixing the workflow.
Cheers,
* Noel
September 25, 2008 at 12:51 pm
This was also the subject of QotD by Sergiy.
😎
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply