Writing Better T-SQL: Top-Down Design May Not be the Best Choice

  • Jeff Moden

    SSC Guru

    Points: 994863

    Another take away should be...

    1. If you have multiple solutions and some favor CPU while others favor IO, then you've missed a solution because you shouldn't have to settle. You've just proven that both are possible. πŸ˜‰

    2. Adding the correct indexes to RPT4 preserves the low duration (output to the screen is the "Great Equalizer" here), drops CPU to sub-millisecond in most cases, and drops the number of reads from 2,025 to 216 for the largest group, "North America". That's almost a full order of magnitude.

    Here's a list of the indexes I added. They have JBM in the names so I can easily find and remove them for my experiments.

    CREATE UNIQUE INDEX JBMST ON Sales.SalesTerritory ([Group],TerritoryID) INCLUDE (Name,CountryRegionCode);

    CREATE INDEX JBMS ON Sales.Store (SalesPersonID) INCLUDE (BusinessEntityID,Name);

    CREATE INDEX JBMP ON Person.Person (BusinessEntityID) INCLUDE (FirstName,LastName);

    This also supports Gail Shaw's recommendation of writing code as simply as possible {I'll add... while following good practices, which she implies and exemplifies} and only attempting something else if there's a performance problem.

    Although I do agree with "Divide'n'Conquer" methods and frequently use them, pre-aggregating to a Temp Table is still slower and more read intensive than using RPT4 with the correct indexes because they're also a simple duplication of data and the overhead is spread out much more during the inserts.

    3. While you might be tempted to add more indexes, you do have to be careful. Adding the following index, which is recommended by the "missing index" suggestion of the Actual Execution Plan, does nothing for performance and jacks up the number of reads again.

    CREATE INDEX JBMC ON Sales.Customer (StoreID);

    4. 216 reads equals 1.8 million bytes of IO. Even if they are logical reads, that can become a problem if this code were executed many times a second (it's reporting code, so I doubt it here). Pre-aggregating the count using an indexed view does weigh too heavily on read during inserts but does knock the reads for the largest group down from 216 to 97, which is about 1/20th of what the original code does.

    5. Having spent a bit of time on the code, I've also spent a lot of time looking at the tables involved in the AdventureWorks database, which leads me to agree with what a lot of people have stated in the past. The AdventureWorks database is a much better example of how NOT to do things than it is an example of how to do things correctly. Seriously? BusinessEntityID all over the place, especially in the Person table? Celko would have a fit and I'd have to agree with him.

    It IS, however, a great example of the mess I normally see in real life except there's usually no hint of documentation in Extended Properties like they tried to do in the AdventureWorks database.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Sergiy

    SSC Guru

    Points: 109670

    There are other aspects which were not mentioned by anyone.

    1. The same small query populating a temp table may be reused in several procedures.

    In such case its plan will be created on a first execution and reused in every other call.

    If the query is called regularly with repeating set of parameters (i.e. last month transactions), even the resultset may be cached, so all the reads will be done from memory.

    If you make sure the "small queries" are written the same way in all procedures where they are used then the total number of cached plans may be on the par or even less than for the case of "all in one" queries.

    2. A cached plan needs to be revisited when statistics are changed.

    More tables included into a query means more chance of query recompilation.

    3 out of 4 small queries may use relatively static data, and their plans would not be revisited for ages.

    4th query which retrieves records from frequently updated table would need to be recompiled on a regular basis.

    But since the query is small - the cost of it would not be significant.

    If you combine those 4 queries into a big one - you're gonna get a recompilation every single time you call it, and it's gonna be a costly recompilation, since the query is way more complicated.

  • sknox

    SSChampion

    Points: 12231

    Sergiy (8/14/2016)


    There are other aspects which were not mentioned by anyone.

    1. The same small query populating a temp table may be reused in several procedures.

    In such case its plan will be created on a first execution and reused in every other call.

    If the query is called regularly with repeating set of parameters (i.e. last month transactions), even the resultset may be cached, so all the reads will be done from memory.

    If you make sure the "small queries" are written the same way in all procedures where they are used then the total number of cached plans may be on the par or even less than for the case of "all in one" queries.

    A more logical response to several procedures needing the same data is to create and populate the table once. Even if SQL server caches the plan to populate the table -- and even the data itself, it still has to write to tempdb's log for every time the temp table is created, populated, and disposed of.

    We had exactly the scenario you describe in one of our databases: last month's transactions, multiple stored procedures calculating profitability data generating the same temp table over and over and over again. Instead of relying on SQL Server possibly reusing the plan or the data, I rewrote it so the table was created once, when the transactional data was originally loaded. Then the multiple procedures all use that one table, rather than creating, populating, and disposing of identical clones.

  • Sergiy

    SSC Guru

    Points: 109670

    sknox (8/14/2016)


    Sergiy (8/14/2016)


    There are other aspects which were not mentioned by anyone.

    1. The same small query populating a temp table may be reused in several procedures.

    In such case its plan will be created on a first execution and reused in every other call.

    If the query is called regularly with repeating set of parameters (i.e. last month transactions), even the resultset may be cached, so all the reads will be done from memory.

    If you make sure the "small queries" are written the same way in all procedures where they are used then the total number of cached plans may be on the par or even less than for the case of "all in one" queries.

    A more logical response to several procedures needing the same data is to create and populate the table once. Even if SQL server caches the plan to populate the table -- and even the data itself, it still has to write to tempdb's log for every time the temp table is created, populated, and disposed of.

    We had exactly the scenario you describe in one of our databases: last month's transactions, multiple stored procedures calculating profitability data generating the same temp table over and over and over again. Instead of relying on SQL Server possibly reusing the plan or the data, I rewrote it so the table was created once, when the transactional data was originally loaded. Then the multiple procedures all use that one table, rather than creating, populating, and disposing of identical clones.

    If you need to know "Current balance so far" - a static table won't help you.

    And that's quite a common request.

  • Jeff Moden

    SSC Guru

    Points: 994863

    Sergiy (8/14/2016)


    sknox (8/14/2016)


    Sergiy (8/14/2016)


    There are other aspects which were not mentioned by anyone.

    1. The same small query populating a temp table may be reused in several procedures.

    In such case its plan will be created on a first execution and reused in every other call.

    If the query is called regularly with repeating set of parameters (i.e. last month transactions), even the resultset may be cached, so all the reads will be done from memory.

    If you make sure the "small queries" are written the same way in all procedures where they are used then the total number of cached plans may be on the par or even less than for the case of "all in one" queries.

    A more logical response to several procedures needing the same data is to create and populate the table once. Even if SQL server caches the plan to populate the table -- and even the data itself, it still has to write to tempdb's log for every time the temp table is created, populated, and disposed of.

    We had exactly the scenario you describe in one of our databases: last month's transactions, multiple stored procedures calculating profitability data generating the same temp table over and over and over again. Instead of relying on SQL Server possibly reusing the plan or the data, I rewrote it so the table was created once, when the transactional data was originally loaded. Then the multiple procedures all use that one table, rather than creating, populating, and disposing of identical clones.

    If you need to know "Current balance so far" - a static table won't help you.

    And that's quite a common request.

    I absolutely agree and, sometimes, THAT's a very good use for Indexed Views. Or, like Sergiy suggested (and perhaps taking it a bit further), pre-aggregate the answer and use a Temp Table to reduce the load as updates occur until the time of the next pre-aggregation.

    Still, the idea of caching an answer can be a real life saver depending on the situation, as you say. We had some software where the developers where told to "check the user privs" for every pull-down, screen change, and field focus change. As tiny as they thought that check was (it sucked actually... took 250ms and 20K reads and "couldn't" be made better because of really terrible legacy table design that affected everything), it was occurring hundreds and, sometimes, thousands of times per minute and caused quite the load on the server. It turned out that the underlying tables were only updated once or twice a day and it was silly to run the proc for virtually every user action. We cached the result of the proc on the webservers themselves and updated them only when the data in the relatively static underlying tables were change and the problem vanished. Even the webservers breathed easier.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Sometimes that works and sometimes it doesn't. I've got examples where I had to break up an elaborate query into smaller steps to gain any appreciable speed.

  • Jeff Moden

    SSC Guru

    Points: 994863

    Iwas Bornready (8/15/2016)


    Sometimes that works and sometimes it doesn't. I've got examples where I had to break up an elaborate query into smaller steps to gain any appreciable speed.

    I've done that, as well. Took a server crushing 45 minute reporting job down to little more than 3 seconds using that very method.

    As with all else in SQL Server, "It Depends".

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • The Dixie Flatline

    SSC Guru

    Points: 53224

    Interesting discussion, but I am concerned about the casual dismissal of the number of logical reads. A consequence of more logical reads is that, in a busy system, those logical reads might require physical reads, which can dramatically slow down a query.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • ChrisM@Work

    SSC Guru

    Points: 186054

    The Dixie Flatline (8/16/2016)


    Interesting discussion, but I am concerned about the casual dismissal of the number of logical reads. A consequence of more logical reads is that, in a busy system, those logical reads might require physical reads, which can dramatically slow down a query.

    I see where you're coming from and offer you this: a complex query is more likely than a simple query to have, somewhere, some rotten estimates because of the cascade effect: 10% out, then 10% more and so on. Breaking it up to a few simple queries is likely to provide more accurate estimates. You will reduce the chance of spills due to underestimates in sorts and hashes. You will also reduce the chance of suboptimal operator choice - an overestimate may favour a hash join over a loop join, which would likely result in inflated reads.

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Jeff Moden

    SSC Guru

    Points: 994863

    ChrisM@Work (8/16/2016)


    The Dixie Flatline (8/16/2016)


    Interesting discussion, but I am concerned about the casual dismissal of the number of logical reads. A consequence of more logical reads is that, in a busy system, those logical reads might require physical reads, which can dramatically slow down a query.

    I see where you're coming from and offer you this: a complex query is more likely than a simple query to have, somewhere, some rotten estimates because of the cascade effect: 10% out, then 10% more and so on. Breaking it up to a few simple queries is likely to provide more accurate estimates. You will reduce the chance of spills due to underestimates in sorts and hashes. You will also reduce the chance of suboptimal operator choice - an overestimate may favour a hash join over a loop join, which would likely result in inflated reads.

    Logical reads must also come from MEMORY. Unnecessary reads frequently also means that memory has some unnecessary stuff in it. Also, despite how fast memory is, even it has to "go through a pipe" of sorts and it's not free. I have seen systems where the memory IO in the form of logical reads was pretty well maxed out and NOTHING got done fast.

    I agree that if you have resources, you should be able to use them but let's not be pound foolish. Reads (logical or otherwise), CPU, simple Duration, and Writes are the 4 things that I usually won't make tradeoffs on.

    What's really ironic is that if you mind the Reads, then CPU, Duration, and memory usage usually benefit quite a bit and that's why it's first on my list.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994863

    Jeff Moden (8/16/2016)


    ChrisM@Work (8/16/2016)


    The Dixie Flatline (8/16/2016)


    Interesting discussion, but I am concerned about the casual dismissal of the number of logical reads. A consequence of more logical reads is that, in a busy system, those logical reads might require physical reads, which can dramatically slow down a query.

    I see where you're coming from and offer you this: a complex query is more likely than a simple query to have, somewhere, some rotten estimates because of the cascade effect: 10% out, then 10% more and so on. Breaking it up to a few simple queries is likely to provide more accurate estimates. You will reduce the chance of spills due to underestimates in sorts and hashes. You will also reduce the chance of suboptimal operator choice - an overestimate may favour a hash join over a loop join, which would likely result in inflated reads.

    Just to add to that...

    Logical reads must also come from MEMORY. Unnecessary reads frequently also means that memory has some unnecessary stuff in it. Also, despite how fast memory is, even it has to "go through a pipe" of sorts and it's not free. I have seen systems where the memory IO in the form of logical reads was pretty well maxed out and NOTHING got done fast.

    I agree that if you have resources, you should be able to use them but let's not be pound foolish. Reads (logical or otherwise), CPU, simple Duration, and Writes are the 4 things that I usually won't make tradeoffs on.

    What's really ironic is that if you mind the Reads, then CPU, Duration, and memory usage usually benefit quite a bit and that's why it's first on my list.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • The Dixie Flatline

    SSC Guru

    Points: 53224

    Jeff, why are you quoting yourself now?

    Guys, I agree that minimizing the logical reads is always a good idea. My point was that the article espoused accepting more reads for the purpose of saving CPU time, but the test times reflected logical reads only.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • rchantler

    SSCrazy

    Points: 2092

    "I can calculate the movements of stars but not the madness of men."

    Isaac Newton

Viewing 13 posts - 46 through 58 (of 58 total)

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