Post reply

Twenty tips to write a good stored procedure

  • August 13, 2009 at 9:04 am

    #1038754

    10. WHERE clauses - In a WHERE clause, the various operators used directly affect how fast a query can run. Here are the conditional operators used in the WHERE clause, ordered by their performance.

    =, >, =, <=, , !=, !>, !<

    for details, refer to the article: http://msdn.microsoft.com/en-us/library/ms190276.aspx

    Completely wrong. The artcile referenced talks about the presidence and not about the time taken for the operation.

    Looks like this line is the cause of confusion:

    The MSDN article says "When two operators in an expression have the same operator precedence level, they are evaluated left to right based on their position in the expression."

    This line explains about how an expression will be evaluated and not on the performance of the article.

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • August 13, 2009 at 9:04 am

    #1038755

    ANSI SQL92 is the other code, instead. which will work for Oracle adn other SQL-92 DBs. Microsofter always uses tactics to push other people away, reader beware. :hehe:

    Jason
    http://dbace.us
    😛

  • August 13, 2009 at 9:08 am

    #1038760

    ANSI SQL92 is the other code, instead. which will work for Oracle adn other SQL-92 DBs. Microsofter always uses tactics to push other people away, reader beware.

    Jason
    http://dbace.us
    😛

  • August 13, 2009 at 9:11 am

    #1038768

    17. CREATE TABLE vs. SELECT INTO - Select * INTO works fine for small tables, but when dealing with large record sets or long-running queries, it creates locks on the system objects within the tempdb database. As a result, other queries and procedures that need to create objects within the tempdb database will have to wait for the long-running query to complete. This is because when an object is created, an exclusive lock is taken against the sysobjects, syscolumns, sysindexes tables.

    What would the impact be if the select into was SELECT Top 0 * INTO FROM

    to create the table struction and then do an insert into it?

  • August 13, 2009 at 9:22 am

    #1038781

    Paul White (8/13/2009)

    In the most recent build of 2005 and 2008, I would tend to allocate a number of equal-sized files to tempdb (maximum four), and leave the trace flag off.

    Agreed. It's very hard to hit major TempDB SGAM contention in 2005 and above. I generally recommend starting with files = 1/4 the number of cores (min 2, max 8), watching for a while and if there's still contention double the number of files. Monitor again and if there's still contention, double the files again. I haven't seen a case where it was necessary to go to more files than 1/2 the number of cores, and that was a 12 processor server with massive Temp table usage.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 13, 2009 at 9:25 am

    #1038784

    jswong05 (8/13/2009)

    ANSI SQL92 is the other code, instead. which will work for Oracle adn other SQL-92 DBs. Microsofter always uses tactics to push other people away, reader beware. :hehe:

    Oh, so neither Oracle nor mySQL have any non-SQL92 extensions to the SQL language?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 13, 2009 at 9:30 am

    #1038789

    jpendergraft (8/13/2009)

    What would the impact be if the select into was SELECT Top 0 * INTO FROM

    to create the table struction and then do an insert into it?

    The same slowness as just creating the table separately. It's totally not necessary.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 13, 2009 at 9:34 am

    #1038794

    jswong05 (8/13/2009)

    ANSI SQL92 is the other code, instead. which will work for Oracle adn other SQL-92 DBs. Microsofter always uses tactics to push other people away, reader beware. :hehe:

    ANSI code may be fine for simple GUI interface stuff... but it's pretty lame compared to the extensions available in both SQL Server and Oracle. Except for the simple GUI interface stuff, code portability especially ,for high speed complex batch code, is just going to make for some slow code. True portability is a myth.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 13, 2009 at 9:45 am

    #1038803

    True portability is a myth.

    True, basic compatibility is expected. Nothing is 100% compatible to another thing.:-P Why don't database vendors got together and make one ANSI standard, they don't want to!!!!! They want to cut throat of each other to get ahead in the market.

    Jason
    http://dbace.us
    😛

  • August 13, 2009 at 10:26 am

    #1038823

    John te Lintelo (8/11/2009)

    Andy DBA (8/10/2009)

    So my question is: Does anyone have strong recommendations (other than readability:-D) regarding LEFT JOIN versus either of the other two? Are there any circumstances in SQL Server where it might outperform them? Does it really take a 3% hit for the filter? (OK, 3 questions)

    NOT IN:

    SELECT * FROM Sales.Customer WHERE CustomerID NOT IN (SELECT CustomerID from Sales.CustomerAddress)

    NOT EXISTS:

    SELECT * FROM Sales.Customer C WHERE NOT EXISTS (SELECT 1 FROM Sales.CustomerAddress CA where C.CustomerID = CA.CustomerID)

    LEFT JOIN:

    SELECT * FROM Sales.Customer C LEFT JOIN Sales.CustomerAddress CA ON C.CustomerID = CA.CustomerID WHERE CA.CustomerID IS NULL

    Using a LEFT JOIN will not return the same results if there are multiple CustomerAddress records that match a Customer record.

    I think you may have missed the where clause in my LEFT JOIN example:w00t:. All three of these queries will return the same result set. ie Customer records without matching CustomerAddress records.

    I noticed that the optimizer creates a slightly different execution plan for the LEFT JOIN query and my question was whether or not anyone knew if it would perform differently than the other two (which have identical plans).

    So far only one person responded with a guess that the LEFT JOIN would make better use of indices, but that's not apparent to me when I look at the execution plan. If there is any difference, I'm guessing it's insignificant, but the only way to really know is to do some testing.

  • August 13, 2009 at 11:18 am

    #1038862

    jswong05 (8/13/2009)

    True portability is a myth.

    True, basic compatibility is expected. Nothing is 100% compatible to another thing.:-P Why don't database vendors got together and make one ANSI standard, they don't want to!!!!! They want to cut throat of each other to get ahead in the market.

    I wouldn't want them to... competition spurs innovation.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 13, 2009 at 11:30 am

    #1038872

    Its funny, I came here looking for what I thought would be exactly this article, but it didn't end up helping me at all.

    What I wanted was more of a best practices article I think -- With suggestions about error checking, commands that shouldn't be used in triggers or stored procedures, commenting code, naming conventions, performance hints, etc.

    I think probably error checking would be good for an article in and of itself. Do you have special error handling stored procedures? Do they email the dba? Do they raise errors? Record errors into an error table?

    I wish I did more with stored procedures in general, I'd give writing such an article a shot... Maybe I should post an idea like that to the forums, let you all rip it up until I get it right, and then we'd post it together?

    Or maybe my google-foo just isn't good enough and such an article on error handling already exists?

  • August 13, 2009 at 12:03 pm

    #1038896

    Introductory error handling article[/url]. It includes 2000 and 2005/2008 syntax.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • August 13, 2009 at 12:14 pm

    #1038906

    Andy DBA (8/13/2009)

    John te Lintelo (8/11/2009)

    Andy DBA (8/10/2009)

    So my question is: Does anyone have strong recommendations (other than readability:-D) regarding LEFT JOIN versus either of the other two? Are there any circumstances in SQL Server where it might outperform them? Does it really take a 3% hit for the filter? (OK, 3 questions)

    NOT IN:

    SELECT * FROM Sales.Customer WHERE CustomerID NOT IN (SELECT CustomerID from Sales.CustomerAddress)

    NOT EXISTS:

    SELECT * FROM Sales.Customer C WHERE NOT EXISTS (SELECT 1 FROM Sales.CustomerAddress CA where C.CustomerID = CA.CustomerID)

    LEFT JOIN:

    SELECT * FROM Sales.Customer C LEFT JOIN Sales.CustomerAddress CA ON C.CustomerID = CA.CustomerID WHERE CA.CustomerID IS NULL

    Using a LEFT JOIN will not return the same results if there are multiple CustomerAddress records that match a Customer record.

    I think you may have missed the where clause in my LEFT JOIN example:w00t:. All three of these queries will return the same result set. ie Customer records without matching CustomerAddress records.

    I noticed that the optimizer creates a slightly different execution plan for the LEFT JOIN query and my question was whether or not anyone knew if it would perform differently than the other two (which have identical plans).

    So far only one person responded with a guess that the LEFT JOIN would make better use of indices, but that's not apparent to me when I look at the execution plan. If there is any difference, I'm guessing it's insignificant, but the only way to really know is to do some testing.

    With Oracle it is more efficient to use NOT IN than LEFT JOIN and test for IS NULL. Their optimizer uses has a very efficient way of evaluating NOT IN.

    I'm not sure about SQL Server, it could be that the data will determine which is quicker.

    If you are in doubt it is best to try both on real data and then take the quicker.

  • August 13, 2009 at 12:50 pm

    #1038932

    Grant Fritchey (8/13/2009)

    Introductory error handling article[/url]. It includes 2000 and 2005/2008 syntax.

    Oh, that is lovely, thanks Grant! I'm learning tons and tons!

Viewing 15 posts - 181 through 195 (of 244 total)

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