Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David Poole » Time Bomb Coding
Add to briefcase
74 posts, Page 2 of 8
««12345»»»

Time Bomb Coding Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
CraigIW
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: Friday, July 20, 2012 4:50 AM
Points: 89, Visits: 213
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
peter-757102
Posted Wednesday, February 10, 2010 3:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:57 AM
Points: 288, Visits: 1,903
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
Sriram.RM
Posted Wednesday, February 10, 2010 4:02 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, February 04, 2013 5:20 AM
Points: 1,491, Visits: 733
Excellent article... Especially the list!!!!

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


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, February 04, 2013 5:20 AM
Points: 1,491, Visits: 733
Excellent article... Especially the list!!!!

For better assistance in answering your questions
Click Here
Post #863068
peter-757102
Posted Wednesday, February 10, 2010 4:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:57 AM
Points: 288, Visits: 1,903
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
CraigIW
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: Friday, July 20, 2012 4:50 AM
Points: 89, Visits: 213
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
Craig Benson
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: Monday, April 15, 2013 11:14 AM
Points: 28, Visits: 197
Well done-- very useful!
Post #863082
Samuel Vella
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 @ 2:22 AM
Points: 328, Visits: 1,852
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
David.Poole
Posted Wednesday, February 10, 2010 4:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 2,750, Visits: 1,410
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
Post #863096
Sriram.RM
Posted Wednesday, February 10, 2010 6:02 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, February 04, 2013 5:20 AM
Points: 1,491, Visits: 733
"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
74 posts, Page 2 of 8
««12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.