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

Time Bomb Coding Expand / Collapse
Author
Message
Posted Tuesday, February 9, 2010 9:27 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 2,907, Visits: 1,830
Comments posted to this topic are about the item Time Bomb Coding

LinkedIn Profile
Newbie on www.simple-talk.com
Post #862916
Posted Tuesday, February 9, 2010 11:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 11:03 PM
Points: 143, Visits: 550
Excellent Article !! covered almost all points for time bomb coding.

--Divya
Post #862953
Posted Tuesday, February 9, 2010 11:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 2,046, Visits: 370
thanks David ... really good article... i m facing the archiving issue now...
Post #862956
Posted Tuesday, February 9, 2010 11:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 2, 2011 9:50 PM
Points: 189, Visits: 335
If you are really fighting for microseconds
If Exists ( Select Null from bigtable Where ...) also is a good test with minimal Overhead instead of Select 1
Begin
.... fancy code
End

But I still find the best time bombs are what I refer to as 'Surrogate Key' processing where software allows customisations to things like account codes and part numbers and financial processing is carried on a conglomerate of individual elements with no key contraints.

Thanks David, a timely article to point out to my new Developers.

CodeOn
Post #862959
Posted Wednesday, February 10, 2010 12:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, September 20, 2014 11:31 PM
Points: 56, Visits: 1,044
Not that I want to encourage bad habits, but I seem to recall a discussion (I thought it was here @ SQL Server Central, but I can't find it) regarding COUNT(*) vs EXISTS, and tests were carried out (in SQL 2005 or 2008) where the compiler was smart enough to recognize that a programmer really intended to use EXISTS not COUNT(*), and so actually generated the same execution plan for both - the SQL Server engineering team compensating for the huge amount of abuse of COUNT(*) that goes on.

...But as I said, that's still no excuse for bad programming - the right tool for the job is EXISTS


EDIT: Forgot to say 'thanks for the cool list of things to look out for' - so, David, thanks for the cool list of things to look out for
Post #862968
Posted Wednesday, February 10, 2010 12:32 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 17,710, Visits: 15,580
Nice compilation of some of the practices that make for poorly performing queries.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #862973
Posted Wednesday, February 10, 2010 12:38 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 1:22 AM
Points: 325, Visits: 234
lovely article thx
Post #862978
Posted Wednesday, February 10, 2010 1:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 7:57 AM
Points: 5, Visits: 88
Brilliant article! opened my eyes to a few practices I hadn't realised were soo poor.
Post #862992
Posted Wednesday, February 10, 2010 1:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 12, 2010 1:12 AM
Points: 1, Visits: 27
Instead of:

IF EXISTS(SELECT 1 FROM ......WHERE....)

Would the following be quicker still?

IF EXISTS(SELECT TOP 1 1 FROM ......WHERE....)
Post #862997
Posted Wednesday, February 10, 2010 1:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, September 20, 2014 11:31 PM
Points: 56, Visits: 1,044
nicholasw (2/10/2010)
Instead of:

IF EXISTS(SELECT 1 FROM ......WHERE....)

Would the following be quicker still?

IF EXISTS(SELECT TOP 1 1 FROM ......WHERE....)


No, and it doesn't really make sense - TOP implies 'importance' i.e. TOP 1 means you're looking for the '1' most 'important' row. When using 'EXISTS', there is no concept of importance - there is data or there isn't.

Also, doesn't TOP get lonely without an 'ORDER BY' clause ?

Post #863001
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse