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

SQL Profanities Expand / Collapse
Author
Message
Posted Monday, August 11, 2008 9:22 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,221, Visits: 2,614
Comments posted to this topic are about the item SQL Profanities

Ken Simmons
http://twitter.com/KenSimmons
Post #550718
Posted Tuesday, August 12, 2008 3:00 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 577, Visits: 2,503
I think that there is a problem with trying to lay down hard and fast rules as to what is 'Best Practice' with Transact SQL. For some reason, we are always far too eager to act like Old Testament prophets when passing on our knowledge. It is a tendency that afflicts a lot of programmers but we Database Developers and DBAs seem to make a beeline for the hard and fast rule.

The real message, I believe, should be this. Your code must perform well, be reliable, and be easy to understand and maintain. I also think it is important to get something up and running quickly once the design is complete, and then refine, and re-factor as you increasingly understand the key points that determine the reliability and performance of the system.

I realise this is much more boring than sounding off on the evils of various 'SQL Profanities', but hell, it is the way to get the job done.

I don't care if you use cursors, Dynamic SQL or any other naughtiness. The advice I always give is:

* Design first, then code. The application's architecture, its overall design, is the largest contributing factor to its performance and reliability. Indexes, constraints, and views should all be planned up-front wherever possible.
* Have the right tools and techniques available to measure performance, and use them to understand how long the various routines and processes take to execute, under various loads and table-sizes, and why.
* Write code that is clear and is easy to maintain and understand. By far the greatest optimisations come through changing the algorithm, so the clearer the code, the easier it is to subsequently optimise.
* Never assume that an error event, such as a particular constraint violation, can't happen. For some reason, they always seem to happen, usually at midnight on a vital production system.
* Set performance goals as early as possible. You must decide what represents a 'good' performance: perhaps by measuring the speed of the previous version of the application, or of other similar applications. Make sure that clear, documented, performance objectives on all parts of the project are agreed 'upfront', so that you know when to start and stop optimising, and where the priorities are. Never micro-optimise. You should revise performance goals at each milestone
* Only optimise routines when necessary. Because detailed optimisation is time-consuming, it should be carefully targeted where it will have the most effect. The worst thing is to plough through DML indiscriminately, changing it to optimise its execution. It takes time, usually makes the code more difficult to understand and maintain, and usually has very little effect.
* Avoid optimising too early. Detailed optimisation of SQL code should not be done until the best algorithm is in place and checked for throughput and scalability.
* Do not delay optimisation too far. The most performance-critical code is often that which is referenced from the most other places in the application, such as an inline function: if the fix requires a large change to the way things operate, you can end up having to rewrite or refactor a huge portion of your application!
* Assume that poor performance is caused by human error rather than SQL Server.
* Employ an iterative routine of measuring, investigating, refining/correcting from the beginning to the end of the product cycle



Best wishes,

Phil Factor
Simple Talk
Post #550811
Posted Tuesday, August 12, 2008 4:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:47 AM
Points: 1,203, Visits: 744
Amen.

But I'd like to add GOTO to the list of Mr. Simmons. SQL Server 2000 only...




Dutch Anti-RBAR League
Post #550839
Posted Tuesday, August 12, 2008 5:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 9, 2014 4:10 PM
Points: 1,388, Visits: 239
Well put!
Post #550884
Posted Tuesday, August 12, 2008 6:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 23, 2011 12:57 PM
Points: 214, Visits: 18
You forgot to add SQL-CLR to your list of profanities. Not that I consider it to be so. But, I have a developer background. So, my biases are to be questioned anyway. :P
Post #550898
Posted Tuesday, August 12, 2008 6:23 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 4:54 AM
Points: 815, Visits: 32
A couple of thoughts sprung to my mind when reading Phil Factors reply to this editorial.

First was a design philosphy I always try to follow, which is to think re-usable. If you are desigining a piece of new functionality, ask yourself if you can forsee it being used in other parts of the system. Is it part of the business logic of your organisation that could be captured in a stored function or procedure. Much easier to change it then when the organisation changes it's business rules (these things occaisionally happen, like once a month :) ).

Following on from this, think modular, if you are going to encapsulate some business logic in a function, make it as self-contained as is logically and physically possible given performance constraints. Supply it with the key values it needs to perform the process, and let it acquire any other the associated data it needs internally. This way your code presents a consistent 'surface' to the rest of your business system. It is far easier to change the internal operation of your functions and procedures when they are not over-reliant on the nature of the information passed to them. Otherwise you find yourself playing a sort of SQL Jenga, move one block, everthing else comes crashing down.

And on the subjects of the SQL Profanities, it is not the tools that are the cause of profanities, usually it is the workmanship that is done with them...

David


If it ain't broke, don't fix it...
Post #550899
Posted Tuesday, August 12, 2008 6:35 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:11 PM
Points: 15,517, Visits: 27,898
You left out triggers...

Good editorial. I agree with it. I also take, a very slight, exception to it. The problems that people who post here every day see are not that someone has a simple little cursor that does the job, quietly, efficiently or a table valued multi-statement function that really only returns about 10 rows so that it's generating a good execution plan that works well within the query, or a view that simply acts as a mask on table structures or builds out complex joins in a way that makes the rest of the code more maintainable. If this was what was primarily out there, then the good ole' saw "It Depends" would be all we'd need to define these issues.

Instead we get cursors wrapped in cursors wrapped in cursors calling 15 million rows and why, oh why, does it run slow. We have multi-statement functions that return millions of rows and these frequently get called by other multi-statement functions also calling millions of rows and that "should be fine otherwise why does SQL Server let you do it." Etc.

The reason so many people rail against the evils of these various tools isn't because there's anything wrong with the tools, but because they're so horribly misused so frequently. It just becomes easier to say NEVER use X rather than outline all the distinct times it's OK. So it really comes down to a bit laziness on the parts of posters who go that route, but that laziness is predicated on experience, not simply laziness.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #550909
Posted Tuesday, August 12, 2008 7:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:02 PM
Points: 33,062, Visits: 15,176
Andy Warren has a saying I love. "Always use the defaults until you have a reason, and know better, why something else should be done."

I think that applies here. Those items listed, along with GOTO and triggers shouldn't be used in code. You should just no reach for these tools.

Until you have a good reason and you understand the implication of doing it that way.

I avoid triggers if I can and look for better ways to handle things, including adding extra code to many sprocs. However I do find places where it doesn't make sense to do that, or it doesn't work and I'll use a trigger. Just sparingly and when I have a reason to do so.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #550939
Posted Tuesday, August 12, 2008 7:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 459, Visits: 1,220
For me, the use of "profane" features comes down to a question of risk/benefit analysis. Will the benefits of using a feature overcome the potential risks? The answer to that question will depend upon many factors (ex: is it a production environment? What's the required timeframe? What level of security is required?)

My attitude has definitely changed since I started my career twelve years ago. Initially, my mindset was to pursue theoretically perfect solutions. It took me a few years to grasp the reality that my role, ultimately, is not to solve technical issues, but to add value to the business.

I think it's important we strive to gain our own convictions to use as basis for making decisions, not just because someone else said it. Even amongst the gurus in the field, there's plenty of differences of opinion.


Brian Kukowski
Post #550951
Posted Tuesday, August 12, 2008 7:14 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 577, Visits: 2,503
True, Steve. I once didn't even realise TSQL had a Goto...but then the first time I ever came across a goto and a cursor in the same sp was in the source of a system stored procedure!


Best wishes,

Phil Factor
Simple Talk
Post #550956
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse