Designing for Performance – opinions, tips, tricks, techniques?!

Been thinking a lot about something that was mentioned in a few of my most recent posts… Especially when I get comments like “that’s another item to add to our checklist” or “that’s a good trick to add to our arsenal” and well, I thought in this blog entry I’d ask for your tricks that fall under the umbrella of designing for performance.



For example – do you change collations? I had a recommendation here.
For example – do you have a view that you want ordered? I had a recommendation (with caution) here. But – Adam Mechanic came back and said that he’s used that trick to improve performance… and, I’m sure that’s the case as well!
For example – do you have stored procedure parameters that are giving you grief? I had a series of recommendations in my Optimizing Procedural Code category here.


In fact, sometimes the best form of “hint” to SQL Server is NOT an optimizer hint but instead a more subtle change to the join (derived tables for example) or the infamous subquery -> join rewrite or the join -> subquery rewrite. I’m always asked “which is better – a subquery or a join” and I always answer YES. ;-)  OR taking a complex process and breaking it down into temp tables (I’d try to create views instead of temp tables first and see if the optimizer figures it out but there are cases when sometimes they just don’t). Remember, it’s not the optimizer’s job to find the absolutely BEST plan; it’s their job to find a good plan fast. And – they typically do. Really, no general “tricks” work ALL of the time and often they don’t help at all but there are LOTS of things that I’m sure you’ve done and you really want to tell someone about it. How about here? I’m going to try to compile these tips/tricks into a best of…

3 thoughts on “Designing for Performance – opinions, tips, tricks, techniques?!

  1. I don’t know if you want to call this a tip or common sense but here it is anyway:
    Make your parameters and columns the same datatype. What do I mean by that? If your table has a column that is of type int don’t use a varchar or char for your parameter (believe me I see this stuff all the time). If you look at the execution plan you will see a convert taking place and this will slow down your procedure

    Here are 2 more
    Use NOCOUNT and group DDL and DML statements together with the DDL statements first followed by the DML statements

  2. These are both GREAT. The first one is known as implicit conversion and you’re right in that it can DEFINITELY cause you grief in your performance plans, etc. This is (yet another reason for) why I’m really looking forward to some of the tools of Data Dude (check out my blog entry and links for Data Dude: https://www.sqlskills.com/blogs/kimberly/2006/06/02/DBAsDefinitelyShouldNOTReadThisBlogEntry.aspx) – intelligent refactoring. This means that if you schema changes they can intelligently look through your code to make sure that your procs, etc. have the corrected type as well. If you were to explicitly convert the variable over then you wouldn’t have the problem but at that point then why not make the variable match… So, this is great.

    As for NOCOUNT. Ha! I can’t think of a reason to ever NOT use SET NOCOUNT ON at the beginning of a stored procedure. In fact, I think the correct syntax (big grin) of create proc is:

    CREATE PROC name
    (variable)
    AS
    SET NOCOUNT ON
    rest of code

    Finally, properly arranging DML and DLL is another great one. Especially true in SQL Server 2000, this can help to minimize excessive recompilations. Check out this KB for some more details: 243586 (http://support.microsoft.com/kb/243586/en-us).

    THANKS for the comments. This is great!
    kt

  3. So as I was saying in my lost comment :-)

    Many of these things are so obvious to us (I think):

    – Keep your rowsize as small as possible by using the narrowest possible datatype
    – Split tables based on heavy used fields vs occasionally used fields
    – Use UNION ALL if UNION is not needed
    – Some relationships benefit from a clustered index on 1 field for the main object and use combined clustering keys (with the main PK and one or more additional fields) for derived objects
    – Very obvious but often forgotten, load a realistic amount of data and as close to reality as possible when first tuning your queries
    – Limit the use of updateable nullable varchars
    – If a FK field is nullable maintain the relationship in a seperate table so FK updates are eliminated
    – A good design is the most important aspect (and the most difficult to change) of a fast performing database. Know your data and know it’s usage!

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.