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 «««23456

Best Practice Expand / Collapse
Author
Message
Posted Monday, September 8, 2008 12:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:05 AM
Points: 2,842, Visits: 3,875
This is really useful information, thanks guys!

Best Regards,
Chris Büttner
Post #565224
Posted Monday, September 8, 2008 3:35 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 22, 2014 10:57 AM
Points: 868, Visits: 1,135
I looked at this as more of a which one performs better question rather than the nature of the operation hence I chose 1.
I guess the absence of a transactin in the first statement has pulled the wool over everyones eyes!

Very good question this one, tip top!
Post #565803
Posted Monday, September 8, 2008 6:53 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 22, 2014 10:57 AM
Points: 868, Visits: 1,135
Very well put on the tree falling in the woods comment, in my experience triggers appear to go by rows affected but also by statements in the execution i.e. if it reads the word "UPDATE" in a piece of code and there is an ON Update trigger on that table...f***en Bang.
Post #565846
Posted Monday, September 8, 2008 7:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:58 PM
Points: 20,705, Visits: 32,356
Sergiy (9/7/2008)
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.


Look on what you are choosing from:
1. A solution which may cause errors in production environment but easy on resources;
2. A solution which is error safe but potentially causes some overhead.

Which one wold you choose for your car?
Are those airbags an overhead?
Have you ever been in a situation when you really needed it?
Have any of you friends?

So when you choose a car - will you prefer the one without airbags?
Why?


Having walked away from an accident where I slammed my car into a ditch at 70 MPH, I want both seat belts (lap and shoulder) and air bags.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #565850
Posted Monday, September 8, 2008 11:10 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,375, Visits: 1,391
Excellent question...


Post #565903
Posted Wednesday, September 10, 2008 6:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
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
If you are looking for less deadlock the answer is #2
But if the target is INSERT a new record, both solutions works.


Isn't 'correctness' favoured first ?
i.e. you look to ensure that you achieve what was intended and then look to optimise (secondary). The correct thing in my mind would be option 2 as explained in the solution.
Let the definition of 'correctness' commence...
Post #566829
Posted Monday, October 20, 2008 6:35 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:23 PM
Points: 528, Visits: 1,266
good question followed by interesting discussion. :)
Post #588897
Posted Friday, October 24, 2008 3:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 24, 2008 3:24 AM
Points: 37, Visits: 50
Good Question But not clear in the sense of Blocking the record.
Post #591046
Posted Thursday, December 13, 2012 6:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:09 AM
Points: 1,916, Visits: 2,337
good question
+3



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1396161
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse