SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Profanities


SQL Profanities

Author
Message
KenSimmons
KenSimmons
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3792 Visits: 2614
Comments posted to this topic are about the item SQL Profanities

Ken Simmons
http://twitter.com/KenSimmons
Phil Factor
Phil Factor
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7446 Visits: 3063
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
gserdijn
gserdijn
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1852 Visits: 818
Amen.

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



Dutch Anti-RBAR League
rgillings
rgillings
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1592 Visits: 239
Well put!
Robert_Harvey-764930
Robert_Harvey-764930
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 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. Tongue
David le Quesne
David le Quesne
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2291 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 Smile ).

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...
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)

Group: General Forum Members
Points: 161753 Visits: 33199
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Steve Jones
Steve Jones
SSC Guru
SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)

Group: Administrators
Points: 247991 Visits: 19802
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
My Blog: www.voiceofthedba.com
Brian Kukowski
Brian Kukowski
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 1618
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
Phil Factor
Phil Factor
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7446 Visits: 3063
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search