September 4, 2008 at 5:26 pm
Sergiy (9/4/2008)
Ric Sierra (9/4/2008)
I'm not completly agree with the answer, because depends of the context:If you are looking for the best execution plan the answer is #1
Can you prove it?
Open QA, run the query from the answer and see that execution plans are identical.
Just 1st option include 2 statements in execution plan, and 2nd one is all in one.
Estimation cost is also 50% for both.
If you are looking for less deadlock the answer is #2
I don't see any difference in regards to deadlocks.
Can you prove your point?
But if the target is INSERT a new record, both solutions works.
You did not get the explanation for the answer.
The point of the question was to illustrate that #1 does not always work.
Using #1 you embed into your code a chance for unexpected application crashes.
Yes.
Yes.
I agree with your POV but both solutions are efective to insert a new record without duplicates (in a simple way, not in a high volume of transactions), and I'm not trying to give a deep explanation just my POV.
September 4, 2008 at 6:12 pm
Ric Sierra (9/4/2008)
Yes.
I'd like to see how.
I guess I'm not alone here.
Yes.
Same question - how?
I agree with your POV but both solutions are efective to insert a new record without duplicates (in a simple way, not in a high volume of transactions), and I'm not trying to give a deep explanation just my POV.
POV without any grounds is worthless.
_____________
Code for TallyGenerator
September 5, 2008 at 1:31 am
definitely 1 option has low cost then 2nd, but if we talk about the locks on table then definitely 2nd option is better then 1st one,
to minimize the lock on table other option may a use of select hints ie with(nolock) .... in 1st option ....
quotes are welcome on it
September 5, 2008 at 2:13 am
Both options are identical on cost.
Both perform same SELECT and both perform INSERT only if SELECT returns no records.
The only important difference s that 2nd option escalates shared locks applied by SELECT to "Insert range" lock if a record needs to be inserted. No existing rows are locked, all other processes trying to access existing records won't be affected.
1st options releases all locks applied by SELECT before applying Insert range lock.
And this allows another process to get in between and spoil the party.
Both options don't use any resources if there is nothing to insert.
_____________
Code for TallyGenerator
September 5, 2008 at 2:28 am
A further thing to bear in mind - if there's an Insert trigger on the table, then it will be fired in response to option 2, but not option 1. Depending on what it does (& how smart you've been in limiting the processing for a zero row insert) then there may be a further performance hit and/or lock escalation to take into account. Of course you may have good reasons for wanting it to fire for even an unsuccessful insert attempt.
In general I prefer to anti-join the value list back to the insert table since that's much more easily expandable for multi-row inserts & compound keys.
My opinion is that there's rarely a truly right or wrong answer to this kind of question - the real skill lies in understanding the strengths & weaknesses of each approach & then choosing the appropriate option for the system it's being applied to.
September 5, 2008 at 5:46 am
icocks (9/5/2008)
A further thing to bear in mind - if there's an Insert trigger on the table, then it will be fired in response to option 2, but not option 1. Depending on what it does (& how smart you've been in limiting the processing for a zero row insert) then there may be a further performance hit and/or lock escalation to take into account. Of course you may have good reasons for wanting it to fire for even an unsuccessful insert attempt.In general I prefer to anti-join the value list back to the insert table since that's much more easily expandable for multi-row inserts & compound keys.
My opinion is that there's rarely a truly right or wrong answer to this kind of question - the real skill lies in understanding the strengths & weaknesses of each approach & then choosing the appropriate option for the system it's being applied to.
You right icocks!
This comment closes this subject for me. 😉
September 5, 2008 at 6:23 am
Sergiy (9/4/2008)
Ric Sierra (9/4/2008)
Yes.
I'd like to see how.
I guess I'm not alone here.
Yes.
Same question - how?
I agree with your POV but both solutions are efective to insert a new record without duplicates (in a simple way, not in a high volume of transactions), and I'm not trying to give a deep explanation just my POV.
POV without any grounds is worthless.
Same order...
Yes, I'm sure of that, but not today.
Yes, you are not alone.
Same answer - Not today!
Nice sentence.
:Whistling:
"La vida es muy seria como para tomarla en serio"
September 5, 2008 at 6:56 am
My opinion is that there's rarely a truly right or wrong answer to this kind of question - the real skill lies in understanding the strengths & weaknesses of each approach & then choosing the appropriate option for the system it's being applied to.
My thoughts exactly.
September 5, 2008 at 8:30 am
icocks (9/5/2008)
A further thing to bear in mind - if there's an Insert trigger on the table, then it will be fired in response to option 2, but not option 1. Depending on what it does (& how smart you've been in limiting the processing for a zero row insert) then there may be a further performance hit and/or lock escalation to take into account. Of course you may have good reasons for wanting it to fire for even an unsuccessful insert attempt.In general I prefer to anti-join the value list back to the insert table since that's much more easily expandable for multi-row inserts & compound keys.
My opinion is that there's rarely a truly right or wrong answer to this kind of question - the real skill lies in understanding the strengths & weaknesses of each approach & then choosing the appropriate option for the system it's being applied to.
I must not fully understand INSERT triggers, but I was always under the impression that if nothing was inserted, an insert trigger WON'T fire. I have never had to write an insert trigger to handle a zero row insert; multi-row inserts, yes. I guess this is something I am going to have to investigate.
😎
September 5, 2008 at 9:03 am
Lynn Pettis (9/5/2008)
I must not fully understand INSERT triggers, but I was always under the impression that if nothing was inserted, an insert trigger WON'T fire. I have never had to write an insert trigger to handle a zero row insert; multi-row inserts, yes. I guess this is something I am going to have to investigate.😎
I hadn't thought about wheter an INSERT trigger would fire if there was no insert, so wrote a quick test.create table ins (num int)
create table inslog (ins int,del int, whn datetime)
go
CREATE TRIGGER dbo.instrigger
ON dbo.ins
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
insert inslog (ins, del, whn)
select
(select count(*) from inserted),
(select count(*) from deleted),
getdate()
END
go
;with n as (
select 1 as n union all
select 2 as n union all
select 2 as n union all
select 3 as n union all
select 3 as n union all
select 3 as n union all
select 4 as n union all
select 4 as n union all
select 4 as n union all
select 4 as n)
insert ins select n.n from n;
update ins set num=6 where num=4;
delete ins where num=2;
;with n as (
select 12 as n)
insert ins select n.n from n where n = 4;
select * from inslog
The result was:
insdelwhn
1002008-09-05 15:58:24.280
442008-09-05 15:58:24.280
022008-09-05 15:58:24.280
002008-09-05 15:58:24.280
So it seems that an insert trigger will fire even if nothing is inserted!
Another new item learned from QotD. 🙂
Derek
September 5, 2008 at 9:04 am
Lynn Pettis (9/5/2008)
I must not fully understand INSERT triggers, but I was always under the impression that if nothing was inserted, an insert trigger WON'T fire. I have never had to write an insert trigger to handle a zero row insert; multi-row inserts, yes. I guess this is something I am going to have to investigate.😎
Took me ages before I realised this was the case (I only found out thanks to some odd problems on a customer db). The same is true of Update/Delete triggers.
If a tree falls in a forest & no-one hears it, it may or may not make a sound. But it will cause a trigger to fire 🙂
September 5, 2008 at 12:24 pm
Lynn Pettis (9/5/2008)
I must not fully understand INSERT triggers, but I was always under the impression that if nothing was inserted, an insert trigger WON'T fire. I have never had to write an insert trigger to handle a zero row insert; multi-row inserts, yes. I guess this is something I am going to have to investigate.😎
As others have already poitned out, the trigger stil fires even if no rows are affected. That's why many developers make it a habit to start each trigger with this line:
IF @@ROWCOUNT = 0 RETURN;
Do make sure that it's the very first line of the trigger, though. I once have spent way too long staring at mysterious results until I realised that I had put this statement after the SET NOCOUNT ON, so I checked the @@ROWCOUNT from that SET statement (which is always 1). :Whistling:
September 5, 2008 at 12:32 pm
I would also guess it depends on what you are doing inside the trigger as well. As I stated earlier, when I did write triggers (not that I don't now when appropriate) I did n't do anything that would have issues (problems) if no records were inserted. Never had any anomilies in my databases that could be the result of a zero record insert.
Having learned this, however, is definately an asset for future activities.
😎
September 5, 2008 at 1:01 pm
Hugo Kornelis (9/5/2008)
That's why many developers make it a habit to start each trigger with this line:
IF @@ROWCOUNT = 0 RETURN;Do make sure that it's the very first line of the trigger, though.
DO NOT use this in a trigger to test if any rows need to be processed by the trigger.
Although this technique has been documented in independent books (e.g., Ken Henderson) the value could be zero from some other action performed by some other trigger. I.e., the value of @@ROWCOUNT does not always reflect the number of rows affected in the table that the trigger is attached to.
From BOL:
Returns the number of rows affected by the last statement.
Trust me on this as I have had this experience!
September 5, 2008 at 2:46 pm
JohnG (9/5/2008)
Hugo Kornelis (9/5/2008)
That's why many developers make it a habit to start each trigger with this line:
IF @@ROWCOUNT = 0 RETURN;Do make sure that it's the very first line of the trigger, though.
DO NOT use this in a trigger to test if any rows need to be processed by the trigger.
Although this technique has been documented in independent books (e.g., Ken Henderson) the value could be zero from some other action performed by some other trigger. I.e., the value of @@ROWCOUNT does not always reflect the number of rows affected in the table that the trigger is attached to.
From BOL:
Returns the number of rows affected by the last statement.
Trust me on this as I have had this experience!
Hi John,
Good point. Thanks for the warning.
I had completely forgotten about this because I never declare more than one trigger for the same action. Frankly, I never really understood why people want to have multiple triggers for an action; I always code all checks and changes required in a single trigger.
(And if you do have a single trigger, then you can safely check @@ROWCOUNT as the first statement in the trigger, as the last statement will THEN always be the statement that caused the trigger to fire.
Viewing 15 posts - 31 through 45 (of 58 total)
You must be logged in to reply to this topic. Login to reply