Time Bomb Coding

Question

Time Bomb Coding

Viewing 14 posts - 61 through 73 (of 73 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

David.Poole (2/11/2010)
Jeff, there is a short and illuminating article on http://www.infoq.com http://www.infoq.com/vendorcontent/show.action?vcr=847 regarding technical debt in agile projects that echoes what you say.
Basically a project team is given a fixed immutable deadline to deliver a project and the only way to deliver on time is to compromise on quality.
Another team comes along to do the next project and because of the technical debt accrued in itteration one they work slower. Inevitably the comment is made that team two is less productive than team one and pressure is applied. Again the only way of getting the project "on track" is to take short-cuts and bodge the quality.....etc
When done properly an agile project is a joy to work on. It is so easy to talk the talk with with agile but not walk the walk!

Now I have 3 things to thank you for... your article, the link, and the feedback. Thanks, David.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Scott Abrants SSCommitted Points: 1503 More actions · Answer 2

Very nicely done! All too often I hear development folks and management say that the DB guys are the bottlenecks and shared resources are a problem yet when they try and go it alone you often see "Time Bomb Code". There is a reason why if your on a plane and the pilot is late they do not come over the loudspeaker and ask if anyone sitting in coach can fly the plane. This is because the pilot has had countless hours of training and has seen many different situations that even training alone cannot prepare someone for. This is very true in database land. Databases design and coding is as much of an art form as it is a science.

Well done...

Jon Russell Hall of Fame Points: 3048 More actions · Answer 3

Scott Abrants (2/12/2010)
Very nicely done! All too often I hear development folks and management say that the DB guys are the bottlenecks and shared resources are a problem yet when they try and go it alone you often see "Time Bomb Code". There is a reason why if your on a plane and the pilot is late they do not come over the loudspeaker and ask if anyone sitting in coach can fly the plane. This is because the pilot has had countless hours of training and has seen many different situations that even training alone cannot prepare someone for. This is very true in database land. Databases design and coding is as much of an art form as it is a science.
Well done...

Scott, you described my situation perfectly. I have a couple of years worth of work to get rid of these time bombs, RBAR, and cursors cleaned up. But thanks to all you folks at SSC, things will be easier.

M. Furqan SSC Rookie Points: 26 More actions · Answer 4

An excellent article. Thanx 4 the bomb list. 😉

Just want someone to clear my concept regarding section: A Top 'n' products solution.

If v use this 1 to 1 relationship (e.g. dbo.Product and dbo.TopProducts in this case) then in most cases v need product information e.g. name, price of top products. For this purpose v have to make a join of dbo.Product and dbo.TopProducts on basis of ProductID.

Is this join less painful then using TopProductPosition field in original product table itself ??

David.Poole SSC Guru Points: 76000 More actions · Answer 5

As with all things DBA related it depends.

If you have a very large product set then adding in an extra column for the Top 'n' products wastes storage though in SQL2008 you have the SPARSE columns facility for NULLable values. I'm old fashioned, I regard NULL values as an occasionally necessary evil.

If the Top 'n' products rarely change, or at least are not in a state of constant flux then it may be worth sticking an indexed view over the top of the two tables so you are getting the best of both worlds.

The other option is to denormalise the Top 'n' products table and copy the main fields you require into that table from the main products table. Obviously you have to plan for updates in two places if you do this.

LinkedIn Profile

dan.forest SSC Veteran Points: 222 More actions · Answer 6

I found that select count(*) and select count(1/NULL) both showed the same execution plan and took the same amount of time.

Dan

rwoods-871939 SSC Veteran Points: 229 More actions · Answer 7

dan.forest (3/2/2010)
I found that select count(*) and select count(1/NULL) both showed the same execution plan and took the same amount of time.
Dan

Apparently they do, but they don't produce the same result. Select count(1/NULL) appears to always return zero, regardless of the actual row count.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

well 1/ null will surele give null if not an error.

also count(null) has a different behavior than you might expect... please read bol for the full details.

rwoods-871939 SSC Veteran Points: 229 More actions · Answer 9

SELECT (1/NULL) will return NULL. SELECT COUNT(1/NULL) will return 0. SELECT NULL will obviously return NULL, SELECT COUNT(NULL) will return an error. If this is documented anywhere in BOL, it is not in the T-SQL reference section.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

from bol 2008

COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

This will also apply to count(whatever you put in here)

rwoods-871939 SSC Veteran Points: 229 More actions · Answer 11

True, but COUNT(NULL) and COUNT(1/NULL) are not mentioned in the T-SQL COUNT() reference. Since the results of SELECT (1/NULL) is NULL, this would appear to be a way to pass the NULL value to COUNT(), but BOL doesn't mention it, nor is there any explanation of why the result of COUNT(1/NULL) would be zero. If I were to take a stab, I would guess that supplying NULL to the COUNT() function satisfies the parameter condition but does not count any rows. There may be some mathematical explanation of the behavior, but I wouldn't know what it is.

sanjays-735734 SSC Enthusiast Points: 134 More actions · Answer 12

Hi David

I would like to add something in Your "simple example"...

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

Instead of this Use following...

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

sanjays-735734 (6/7/2010)
Hi David
I would like to add something in Your "simple example"...
IF EXISTS(SELECT 1 FROM ... WHERE...)
Instead of this Use following...
IF EXISTS(SELECT TOP(1) 1 FROM ... WHERE...)

Why?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David.Poole SSC Guru Points: 76000 More actions · Answer 14

sanjays-735734 (6/7/2010)
Hi David
I would like to add something in Your "simple example"...
IF EXISTS(SELECT 1 FROM ... WHERE...)
Instead of this Use following...
IF EXISTS(SELECT TOP(1) 1 FROM ... WHERE...)

Sanjays, I've found that the IF EXISTS in nearly all cases generates the same execution plan.

You can get away with SELECT * however I've always hammered in the disciplines of not using SELECT * so I don't confuse developers with the cases where it doesn't matter.

LinkedIn Profile