April 8, 2009 at 3:29 am
paul.gregory (4/8/2009)
Not sure I agree with "'Get something up and running. We can always tidy it up later' "You should always plan to have to tune/improve some parts of the system. When you are testing and let's say you have two testers at your disposal, there is no way you can simulate 2000 users, Deadlocks and such may only become apparent later.
There's a difference between write it well and know that you may have to tune it later and doing a quick and dirty hack job with the justification that it can always be cleaned up later.
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
April 8, 2009 at 3:33 am
I write everything well, and "encourage" my team to do the same wherever I work. I have a big pair of boots.
Are you implying that there are folks out there who don't strive for excellence;-)
April 8, 2009 at 4:29 am
Great article!
I've seen stuff though that actually performs better when it's broken up into many little chunks rather then one big statement. It's also alot easier to support, test and troubleshoot.
My thoughts around that were that it was too complex for the optimizer to come up with an optimal plan and taking stuff in smaller chunks led to a more optimal plan.
Mark
April 8, 2009 at 4:44 am
I work in a large education organisation and my teams sole job is to develop BI reports across many platforms. For reporting Services we always aim to write a single proc with no children (apart from scalar valued functions) to power a report, cursors are pretty much banned and in line documentation is the key to understanding procs which are up to 5000 lines of code.
We already have over 200 significant, large procs each usually powering multiple levels of the same report which can be accessed with different parameters.
The point i make is that if we broke our code down into more peices it would just take us longer to edit reports and find all the components involved in getting data for a particular report. With in line comments at every step and a knowledge of SQL, me and my team can easily dig through enormous sprocs and quickly understand what they do.
The biggest sproc i have written is about 5000 lines of solid sql for one report and my colleagues can support this easily because they know SQL and understand execution plans, this size of sproc is not daunting to us.
The only thing a couple of my colleagues struggle with is when i have used cartesian and/or a tally table to solve a SQL problem as there is usually a remarkably small amount of code solving a very complex problem.
April 8, 2009 at 4:53 am
The favourite here is "Its much better than what we had before" .....
My usual reply that still doesn't mean it's any good.
Or at somewhere else I worked "It failed the test so I removed the test"....
Then wondered why it didn't work when it went live....
April 8, 2009 at 4:54 am
david.murden (4/8/2009)
I work in a large education organisation and my teams sole job is to develop BI reports across many platforms. For reporting Services we always aim to write a single proc with no children (apart from scalar valued functions) to power a report, cursors are pretty much banned and in line documentation is the key to understanding procs which are up to 5000 lines of code.We already have over 200 significant, large procs each usually powering multiple levels of the same report which can be accessed with different parameters.
The point i make is that if we broke our code down into more peices it would just take us longer to edit reports and find all the components involved in getting data for a particular report. With in line comments at every step and a knowledge of SQL, me and my team can easily dig through enormous sprocs and quickly understand what they do.
The biggest sproc i have written is about 5000 lines of solid sql for one report and my colleagues can support this easily because they know SQL and understand execution plans, this size of sproc is not daunting to us.
The only thing a couple of my colleagues struggle with is when i have used cartesian and/or a tally table to solve a SQL problem as there is usually a remarkably small amount of code solving a very complex problem.
Which reminds me of another very unsafe assumption.
"Best practices must ALWAYS be followed"
You're right, David. Exceptions to the rule are fine so long as they're approached with suitable forethought, can be justified and the risks are both understood and managed.
Semper in excretia, suus solum profundum variat
April 8, 2009 at 5:00 am
Carolyn Richardson (4/8/2009)
....Or at somewhere else I worked "It failed the test so I removed the test"....Then wondered why it didn't work when it went live....
Man that one had me laughing... conjures up images of a guy hitting his finger with a hammer and wondering why it hurts....
Unbelieveable...
April 8, 2009 at 5:43 am
First we'll get it to work as SA....later, we'll crank down the privileges.
(ouch - just got shot in the left eye)
April 8, 2009 at 6:40 am
GilaMonster (4/8/2009)
paul.gregory (4/8/2009)
Not sure I agree with "'Get something up and running. We can always tidy it up later' "You should always plan to have to tune/improve some parts of the system. When you are testing and let's say you have two testers at your disposal, there is no way you can simulate 2000 users, Deadlocks and such may only become apparent later.
There's a difference between write it well and know that you may have to tune it later and doing a quick and dirty hack job with the justification that it can always be cleaned up later.
I think the correct rule should be "make it right, then make it fast"
BTW I tend to break code into small chunks and generally leave it that way. It's a lot easier for the next person who needs to debug or modify my code.
...
-- FORTRAN manual for Xerox Computers --
April 8, 2009 at 6:48 am
One of the worst assumptions I run into all the time is "The rules belong in the business logic layer, not the database", which is always followed with, "why do we have all this bad data in the database?"
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 7:19 am
Ahhh, One of my favorite subjects.
Years back I discovered the hard way that joining multiple tables could dramitically increase the total run time. This was in MS Access using ODBC into a Progress DB, but I just had the same experience yesterday linking into a SQL database.
I have found it is almost always results in faster execution if I chop things up into simple queries with intermediate work tables in a linear job stream.
Not just in runtime execution, but also in programming the job stream. I find it is much quicker to code a multistep linear job stream than it is to code a single massive "do it all at once" program. It also is significantly faster to troubleshoot the linear job stream.
Do I amaze people with my technical prowess?? Quite frankly, I don't care as long as the users get quick and accurate results.
April 8, 2009 at 7:42 am
A recent assumption that wasn't fatal, but caused much grief that I saw recently was that the .Net guys I work with decided on their own that it would be better for them to cache extremely large result sets on the application server. They came up with their own ways of dealing with what to keep in memory and how long and what to persist to disk. They built their own mechanisms to keep track of which cached data belonged to what user. They even built an elaborate code layer to abstract all of this from the main program logic.
When I learned about this, and suggested that they've esentially re-invented a database, and not very efficiently, they stated that it was easier to scale out the application servers than the database server so it was OK. Even though only a small portion of those large result sets typically gets used in the application at a time, and scaling out means that a user could get different results depending on what server they happen to hit.
April 8, 2009 at 7:49 am
Sounds like a case of way too many programmers with way too much time on their hands...
April 8, 2009 at 8:11 am
This relates to the "get it up and running quickly" syndrome.
We have a lot of business logic embedded in our sql code which is consistent for our operations - a manufacturing company. Then along comes a new customer or product that breaks the rules of our system. The temptation is to just do a little hard coding for that exception, assuming that this is a one-off situation. Inevitably, a month or three later another similar situation comes along and before long we have a string of spaghetti that would fill a large bowl.
Lesson: take the time to do it right, even if you are under the gun.
April 8, 2009 at 8:20 am
Another one is the "that will never happen here" and its corollary "we're never going to do that."
I've seen those flip so many times over the years that I now automatically try to code in exceptions for anything I am told "will never happen."
Viewing 15 posts - 16 through 30 (of 81 total)
You must be logged in to reply to this topic. Login to reply