August 14, 2016 at 10:48 am
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
Change is inevitable... Change for the better is not.
August 14, 2016 at 7:08 pm
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.
_____________
Code for TallyGenerator
August 14, 2016 at 9:07 pm
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.
August 14, 2016 at 10:19 pm
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.
_____________
Code for TallyGenerator
August 15, 2016 at 3:29 am
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
Change is inevitable... Change for the better is not.
August 15, 2016 at 7:08 am
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.
August 15, 2016 at 7:29 am
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
Change is inevitable... Change for the better is not.
August 16, 2016 at 9:03 am
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
August 16, 2016 at 9:52 am
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.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 16, 2016 at 11:12 am
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
Change is inevitable... Change for the better is not.
August 16, 2016 at 4:25 pm
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
Change is inevitable... Change for the better is not.
August 18, 2016 at 11:06 am
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
August 18, 2016 at 11:24 am
"I can calculate the movements of stars but not the madness of men."
Isaac Newton
Viewing 13 posts - 46 through 57 (of 57 total)
You must be logged in to reply to this topic. Login to reply