Tsql/SP Code Optimization

  • sizal0234

    SSCrazy

    Points: 2041

    Hi,

    I am working on optimizing some of Tsql code for SP's. I know we have formatting tools which help in understanding the code. However do we have any tools which suggest performance improvement like replace x with y might increase performance. Please let me know and appreciate your time in advance.

    Thank you!

  • ScottPletcher

    SSC Guru

    Points: 98120

    I'm not aware of any such tool.  If there were one, it would be rather expensive, I'm sure.

    Tuning code for performance requires human skills (hallelujah!, since that's one of the things I specialize in).

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • sizal0234

    SSCrazy

    Points: 2041

    Ok thanks, could you share your thoughts on the below. The code below is part of SP. The problem I am trying to resolve is "Selecting all instead of required"

    SELE

    -- Example this is what I would like to change it to.

    SELECT

    ,CustomerPhone_Number

    ,@CreateID

    FROM CustomerNumbers

    WHERE Age = 2

    AND EmployerCustomerNumbers_X_ID = @X_ID

    AND NOT EXISTS (SELECT EPN_EmpID, EmployerP, FROM TbEmployerPh -- Is this correct? I kind of changes the * to column name.

    • This reply was modified 1 month ago by  sizal0234.
    • This reply was modified 1 month ago by  sizal0234.
  • ScottPletcher

    SSC Guru

    Points: 98120

    The column names will make no difference in a NOT EXISTS.  It's customary to use SELECT * or SELECT 1 in a NOT EXISTS query.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • sizal0234

    SSCrazy

    Points: 2041

    Thanks, could you help me with some more details like evidence which helps me make my case in proving so instead of saying it is not going to help with performance. Any links or stuff. Appreciate it. Curious to know why.

    Saved me time. Have a great day!

  • ScottPletcher

    SSC Guru

    Points: 98120

    The easiest way to verify it is to look at the query plan.  You should be able to see that SQL is not actually pulling all the columns from that table, that it's just checking for whether a column exists or not.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Alan Burstein

    SSC Guru

    Points: 61039

    Redgate SQL Prompt is useful for this kind of thing. It's not as intuitive as you would describe but it will help you identify unused variables, parameters and columns, which can be helpful.

    As Scott said - this is more of a human endeavor. The low-hanging fruit I look for are things such as (and I'm pulling stuff off the top of my head):

    1. RBAR

    (Coined by Jeff Moden as Row-By-Agonizing row)...

    This includes cursors, loops, triangular joins, scalar user defined functions, and using recursive CTEs for counting. Finding and replacing RBAR with asset-based code usually yields the highest ROI for me.

    2. Bad functions

    As far as I'm concerned, any T-SQL user defined function that includes a BEGIN and END (such as multi-statement and scalar functions) will slow you down horribly. Replacing them with inline Table Values functions is the solution.

    That's all I have time to mention but, if you can get these things under control you'll be way ahead of the game.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • sizal0234

    SSCrazy

    Points: 2041

    Great. Thanks!

    I have noted the things you have mentioned and will now take a look at the SP's and look for additional problems as mentioned.

     

  • Luis Cazares

    SSC Guru

    Points: 183568

    For your question about not the comment on not helping with performance, it's because EXISTS doesn't evaluate the columns from the subquery. An interesting way to test this is by using something like:

    SELECT *
    FROM SomeTable st
    WHERE EXISTS( SELECT 1/0 --This won't be evaluated, therefore will not throw an error.
    FROM OtherTable ot WHERE st.column = ot.column);

    For the rest of your question, you could use something like SQL Cop (now part of SQL Test from Redgate) or the code analysis function from SQL Prompt. Both options will just recommend best practices, but won't necessarily find all performance problems. As mentioned before, that's more of a human analysis with help of multiple tools (those include code, configuration and schema design analysis).

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 9 posts - 1 through 9 (of 9 total)

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