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 12345»»»

Best Practice Expand / Collapse
Author
Message
Posted Wednesday, September 3, 2008 9:36 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:10 PM
Points: 4,576, Visits: 8,351
Comments posted to this topic are about the item Best Practice
Post #563553
Posted Thursday, September 4, 2008 12:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 1, 2014 6:55 AM
Points: 2,366, Visits: 1,845
oops... Nice question sergiy. I was surprised with the number of incorrect answers myself included.:)

"Keep Trying"
Post #563587
Posted Thursday, September 4, 2008 12:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:05 AM
Points: 2,842, Visits: 3,875
Nice question, but not "totally" deterministic in my opinion.
What If I ensure consistency through the transaction isolation level already?

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
IF NOT EXISTS... INSERT...
COMMIT

Edit: Deleted wrong information from me regarding Performance without indexes


Best Regards,
Chris Büttner
Post #563590
Posted Thursday, September 4, 2008 1:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:36 PM
Points: 6,002, Visits: 8,267
Christian Buettner (9/4/2008)
Nice question, but not "totally" deterministic in my opinion.
What If I ensure consistency through the transaction isolation level already?

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
IF NOT EXISTS... INSERT...
COMMIT

In this case, both options seem to be equal(from a performance perspective) if there is
an index on Name. If no index is defined, the first one performs better in my tests.

Input on this is highly welcome.


Hi Chris,

Setting a higher transaction isolation level will indeed prevent problems from a different connection inserting the row in just the wrong moment. (I'm not sure at the moment if REPEATABLE READ suffices or if you need SERIALIZABLE though - maybe after some coffee).

I still consider the other option the better one, because it allows the optimizer to come up with a more efficient plan. After all, the check for existence can only be done by finding the clustered index page where the row should be - and that same page has to be found for actually inserting it, so there's no need to traverse the B-tree twice. I don't know if it really works this way in any current version of SQL Server, but even if it doesn't, a future version mght implement such an optimization. And that alone is sufficent reason for me to prefer the second option.

Great question, Sergiy!

Best, Hugo

PS: Of course, with SQL Server 2008, neither of these options is the "best" anymore; this is a typical scenario for the new MERGE statement. :)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #563597
Posted Thursday, September 4, 2008 2:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 10, 2008 7:34 AM
Points: 38, Visits: 41
Good question, but I think it is being confused, including by me, with an UPSERT (as the previous posts' referral to MERGE indicates too).

This question needs more information to answer correctly: How is the action dealt with if the INSERT is not performed? An UPDATE instead? Maybe we actually want a unique constraint on the Name column to report an error if a name is attempted to be inserted twice? If we do need to update instead if the record already exists, how do we control concurrency in that case?

In high concurrency designs these and many more questions must be answered first and I completely agree a more deterministic approach would be required.

A big misconception I keep coming across is that there must be a perfect singleton solution for all situations.... but why not perfect the solution for each singleton situation? A welcome side-effect of this is that it keeps us all in jobs!

Post #563611
Posted Thursday, September 4, 2008 6:05 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, March 31, 2011 1:40 AM
Points: 1,938, Visits: 208
Chris,

Doesn't the second solution have to do the same B-tree traversal?

Good question, today, and good discussions!!

Joe
Post #563709
Posted Thursday, September 4, 2008 6:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:36 AM
Points: 1,199, Visits: 574
i thought this was like which one will perform better
Post #563712
Posted Thursday, September 4, 2008 6:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:36 PM
Points: 6,002, Visits: 8,267
dunnjoe (9/4/2008)
Chris,

Doesn't the second solution have to do the same B-tree traversal?

Good question, today, and good discussions!!

Joe


Hi Joe,

I assume you meant me, not Chris. :)

The second solution has to do the B-tree traversal at least once, to find out iff the row already exists. If not, it doesn't have to (*) repeat the traversal to perform the actual insertion.

The first solution on the other hand has two statements, that are executed after each other. So the B-tree has to be traversed for the IF, and if the NOT EXISTS is found to be true, the same B-tree traversal will then have to be repeated for the actual INSERT. Since it's two statements, there's no way the optimizer can improve on this.

(*) Disclaimer - I don't know what optimizations are actually in place. This describs theoretically possible behavior; I have not done any tests to assess what the actual behavior is like.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #563713
Posted Thursday, September 4, 2008 6:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 12, 2010 7:37 AM
Points: 15, Visits: 143
side note: we shouldn't really be doing "select *" to check for existence. If that's a LOONNNNGGGGG row there could be a lot of overhead to no good purpose. It may be more self-documenting to code "select 'exists' from ....."
Post #563731
Posted Thursday, September 4, 2008 6:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:05 AM
Points: 2,842, Visits: 3,875
dunnjoe (9/4/2008)
Chris,

Doesn't the second solution have to do the same B-tree traversal?

Good question, today, and good discussions!!

Joe

Hi Joe,

yes it does.
I assume I have not cleared the table between my tests.
Therefore, the first option was obviously more cost efficient
in the actual plan, since the insert was not applied due to the
customer being already in the table.

I have adjusted my post above to remove the wrong information.


Best Regards,
Chris Büttner
Post #563735
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse