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 Wednesday, February 10, 2010 1:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 3:55 AM
Points: 92, Visits: 223
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, as the "top 1" is only done after the "select 1 from".
Post #863009
Posted Wednesday, February 10, 2010 3:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:46 AM
Points: 328, Visits: 2,234
Outstanding article...clear in meaning, no clutter or distractions, straight to the point.

It is also enlightening to see some of the beginner mistakes I made so many years ago when I tried to use the relational database as storage for a perfect object model and used only a few generic tables holding all the data. Your article correctly describes the problems it had when soon after it wend into production...shockingly quickly I might add, even on brutal hardware. That everything was developed on a memory constraint laptop did not help either ofcourse, always make sure to also test on similar hardware it will eventually be running on. A memory constraint laptop running 5 applications next to SQL server will not correctly separate bad code/models from good code/models. It is like trying to find out which athlete can run faster and should be send to the olympic games, with the constraint that they all have to tug 800kg of metal behind them to find it out...uphill.

As for the bitfield problem (and similar ones), filtered indexes (SQL 2008) can be a big help here.
Post #863066
Posted Wednesday, February 10, 2010 4:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:57 AM
Points: 1,545, Visits: 759
Excellent article... Especially the list!!!!

For better assistance in answering your questions
Click Here
Post #863067
Posted Wednesday, February 10, 2010 4:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:57 AM
Points: 1,545, Visits: 759
Excellent article... Especially the list!!!!

For better assistance in answering your questions
Click Here
Post #863068
Posted Wednesday, February 10, 2010 4:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:46 AM
Points: 328, Visits: 2,234
CraigIW (2/10/2010)
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, as the "top 1" is only done after the "select 1 from".


Funny enaugh, it is not always that simple. I seen cases where a:

select top 1 max(id)

outperformed

select max(id)

many times over, and I suspect top sometimes acts as a undocumented hint. I got some testing to do on it still, and if it pans out it might also be a trick to control table variables better by using top to provide an estimate other then the constant 1 and thus get better query plans. It could also be just a side effect of the optimizer assigning costs to each operation and in rare cases come to a different plan due to a slightly higher ** estimate ** of the costs. In any case, it does not hurt to test both methods if you see a query perform worse then you anticipated.
Post #863071
Posted Wednesday, February 10, 2010 4:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 3:55 AM
Points: 92, Visits: 223
peter-757102 (2/10/2010)
CraigIW (2/10/2010)
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, as the "top 1" is only done after the "select 1 from".


Funny enaugh, it is not always that simple. I seen cases where a:

select top 1 max(id)

outperformed

select max(id)

many times over, and I suspect top sometimes acts as a undocumented hint. I got some testing to do on it still, and if it pans out it might also be a trick to control table variables bestter by using top to provide an estimate other then the constant 1 and thus get better query plans.


Crazy! Good to know that though.
Post #863072
Posted Wednesday, February 10, 2010 4:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:42 AM
Points: 28, Visits: 240
Well done-- very useful!
Post #863082
Posted Wednesday, February 10, 2010 4:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:18 AM
Points: 337, Visits: 1,995
Catch all Lookup tables are the bane of my life.
I've lost count of how many times I've had to write queries which join to the same lookup table 10 or more times to retrieve some basic data items.
As your article points out, it makes enforcing business logic very difficult, sometimes these data values can be mandatory but there is no straight forward way of enforcing that.

It also hides the true data structure so when someone new to the database is trying to mine the information schema tables to try and find certain data items they find themselves unable to locate key columns which have been instead hidden into a lookup!

Finally, the performance hit. All the extra processing which has to go on to flatten the data from the lookup tables. Storage is cheap, CPU cycles wastes energy!


Post #863094
Posted Wednesday, February 10, 2010 4:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:56 PM
Points: 2,903, Visits: 1,820
If you look throught he source of the built in system stored procs you will see quite a few cases of IF(SELECT COUNT(*)...)>0

Try sp_helptext 'sp_helptext' for an example!


LinkedIn Profile
Newbie on www.simple-talk.com
Post #863096
Posted Wednesday, February 10, 2010 6:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:57 AM
Points: 1,545, Visits: 759
"Catch all Lookup tables are the bane of my life.
I've lost count of how many times I've had to write queries which join to the same lookup table 10 or more times to retrieve some basic data items."


Iv come across the same, This becomes worse if proper documentation is not present.

But having lookup tables has its own advantages, There are scenarios when u r dealing with a huge DB and if its not normalized atleast to the 2nd level u face loads of performance related problems especially with data retrieval.


For better assistance in answering your questions
Click Here
Post #863143
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse