June 2, 2015 at 9:09 am
You've made an interesting point, but some of your concerns are specific to SQL Server and not widely applicable. Since this is an SQL Server forum, that's not surprising. But the solution you are proposing is more generic, and the caveats are due strictly to the limitations of SQL Server.
I've encountered the idea that the optimizer "needs enough time to find a good path" only in the context of SQL Server, and I consider it a bug in the DB/optimizer design. I've only dealt with a handful of other databases (DB2, Oracle, PostgreSql), but I've never seen any of the others pick a 'best so far' plan. At least, they haven't been vocal about it, and they don't seem to be hindered by an optimizer time-out (that doesn't mean they haven't failed for lack of other resources).
There's also the problem of SQL Server not handling more than a handful (6? 7? I forget at the moment) of joins. Sub-queries are joins. Too many and the optimizer "runs out of time". There does not appear to be any means of controlling this time-out period (but I'm not a DBA, and I've been a year or so away from SQL Server). All the "fixes" I've seen are work-arounds that skirt the optimizer.
There is also the problem where the optimizer keeps re-optimizing the query, based upon the (presumed) volume of data run through it, and can re-select a different "good enough" plan after several runs on small volumes, which then bites you when you select (for example) a longer date range, and actually need that better-performing initially-selected (and slightly dumber) plan that doesn't take the data volume into account. I can understand the intent of the optimizer design. It even sort of makes sense for a database dominated by ad-hoc queries. But it is still a bug, as it means the database behavior is unstable and unreliable, and what worked fine in Test can't be counted on to work reliably in Production. In this case, your proposed solution would be more likely to be affected this behavior as well.
The upshot is that the programmer/DBA is stuck far too often with the programming model that you're arguing against, simply to deal with the shortcomings of the database optimizer. It isn't a failure to realize the alternatives, it is the failure of the database engine to be able to handle those alternatives in a reliable and predictable fashion.
It would be nice if we could do what you're suggesting, and trust the database engine to deliver the data in a reliable, and properly optimized fashion, but we're handicapped by an optimizer that insists on looking at both less code than the full query, and more data than just the query, without understanding the semantics of the query (time ranges, for example).
This is just a programmer's opinion, of course.
Does this help?
June 2, 2015 at 10:26 am
Sorry, I think your conclusion is plain wrong. While you have some legitimate concerns about performance, blaming the method of design is NOT the answer.
The problem isn't how a person designs the procedure, but when the person stops. Thinking through all of the steps and what all has to happen is a great thing. But that doesn't mean a person shouldn't also look for ways to improve efficiency.
Also, what should be optimized for EVERY time in my opinion is the fastest response that doesn't cause other user's pain. And scaling should only be considered for the scales that are real life. (If a report will only return less than 100 records, then worrying about 1 million is pointless. Just as a report that always will be used to yield close to a million shouldn't be optimized to perform well at 100.)
June 2, 2015 at 10:32 am
Just want to add to others' comments... There is no bigger fan of CTEs and TVFs and APPLY than I am, but one should not blindly use CTEs instead of temp tables... you have to understand exactly what the CTE is doing... and realize that if you use a complicated CTE more than once in a query, it's going to duplicate all of that CTE's work... and this can be especially true of recursive CTEs.
I wrote a tongue-in-cheek blog post about recursive CTEs that gives an example of this... by changing a recursive CTE (which itself uses a CTE) to a temp table solution, the number of reads decreased from 9,197,296 to only 13,716... and duration from 69 minutes down to 500ms:
This Article On Recurson Is Entitled “This Article On Recursion Is Entitled “This Article… 8” ”
June 2, 2015 at 10:33 am
I'm still kind of a newbie and usually when people start discussing execution plans and metrics I get lost. However this article was very straight forward and easy to understand.
I got a lot of valuable insights. Thanks.
June 2, 2015 at 12:07 pm
I have seen many good and thoughtful comments on here about Mickey's article.
In general what Mickey states is mostly counter to my experiences but like so many things in the real world and especially with Sql Server the real answer is "It Depends".
The one thing I haven't see yet is a concern about locking and blocking during the course of the execution. I work on highly transactional systems handling tens of thousands of transactions per second. Any procedure that I have had to tune in the last few years working here have required the use of smaller queries within the proc to load intermediate data into temp tables or table variables in some cases that are then used in the final query at the end.
Any other methodology simply maintains the locks on the source tables too long and blocks other processes from access. In these systems microseconds are crucial.
My advice to anyone wanting to use this method or any other method as well is that you should test it both ways. If you cannot prove it with test results then you don't really know.
Have a great day.
Richard L. Dawson
Microsoft Sql Server DBA/Data Architect
I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.
June 2, 2015 at 3:59 pm
Konstantin Rudoy (6/2/2015)
For 15 years I was against splitting the code into the parts until started working with hundreds of millions of the records. And what is the interesting part, that in many cases (even after rebuild statistics) execution plans are different between what you see in SSMS and what you have in reality, when SP is executed. Especially when you have heavy sub-queries.
There are number of relatively recent posts from Brent Ozar's team that might help you understand the differences between the SP in production and the Query in SSMS. For instance Kendra Little put a nice piece together about a year ago.
Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)[/url]
Also, check SQLSkills.com. This is one of Kimberly Tripp's favorite topics. 🙂
June 2, 2015 at 4:50 pm
If you are referencing a large dataset through a linked server, especially if used multiple times in a join condition, you are better served with a temp table. Also a temp table can get a tables data with updated statistics if you add a index to it (this of course serves you better when the base table's stats are not as current).
Thus temp tables are not the performance burden that I often read about. I find that in SQL, unlike a true top down programming language, comments matter more. It is a wise practice to describe the data you are toying with and why you need to apply the operations that you do. Temp tables allow for a better readability model in this regard. I am thinking of the next guy that will be maintaining my code and his time is more valuable than a some extra cpu cylces.
----------------------------------------------------
June 2, 2015 at 8:17 pm
Hi,
Your last paragraph explained what I was trying to say. Reducing I/0 is important, but it's not going to help the overall performance of your entire workload if the really bad query, with high IO is fast (enough).
Mickey
Banana-823045 (6/2/2015)
Nice article.What I found most interesting about this was the remark:
For most environments, having faster stored procedures and reducing any potential CPU pressure is more important than the number of reads.
Because it's opposite of what I've come to believe - database systems are more likely to be IO bound than CPU bound so if something takes a little more CPU time but reads less, it'll be generally better to use that method because CPU cycle are cheap but disk access aren't.
Am I mistaken to think that?
On the other hand, the overall duration, however, is significant, since a stored procedure that takes half the time to run is much less likely to create lock contentions than other one that takes more time. I was once given the advice that the first and foremost measure is the wall clock -- I was making the mistake of making the execution plans my first measure and it was explained to me that most of time, something that executes in short time with a ugly execution plan is usually "better" than something that has a beautiful plan shape but takes more time.
Mickey Stuewe
Sr Database Developer
My blog
Follow me on twitter: @SQLMickey
Connect with me on LinkedIn
--------------------------------------------------------------------------
I laugh loudly and I laugh often. Just ask anyone who knows me.
June 2, 2015 at 8:22 pm
Thanks Jeff. There is one thing that I've learned, there is no silver bullet. I'm looking forward to you reading your article.
Mickey
Jeff Moden (6/2/2015)
While this is a good example and it's a good article (thank you SQLMickey for taking the time to write it), it IS an example of 1. "Divide'n'Conquer" methods frequently ARE the answer and having single queries that do a lot of things frequently ARE a major performance problem.Also, the article suggests the use recursive CTEs. I want to remind everyone that certain types of recursive CTEs are frequently worse than either Cursors or While loops. Please see the following article for proof of that.
http://www.sqlservercentral.com/articles/T-SQL/74118/
As with all else in SQL Server, "It Depends". Keep both methods in mind because they both have their places.
Mickey Stuewe
Sr Database Developer
My blog
Follow me on twitter: @SQLMickey
Connect with me on LinkedIn
--------------------------------------------------------------------------
I laugh loudly and I laugh often. Just ask anyone who knows me.
June 2, 2015 at 8:26 pm
Hi Adar,
About 2/3rd down the article. There is a chart. The first column shows how long each query ran in milliseconds. Queries 2 and 4 ran faster because they were written differently, more efficiently.
I hope that helps.
Mickey
adar.morag (6/2/2015)
why SPs 2 and 4 run faster than 1 and 3 was not so clear
Mickey Stuewe
Sr Database Developer
My blog
Follow me on twitter: @SQLMickey
Connect with me on LinkedIn
--------------------------------------------------------------------------
I laugh loudly and I laugh often. Just ask anyone who knows me.
June 2, 2015 at 8:41 pm
Hi Chris,
You found one of the "stored procedures" I didn't convert to "query. I thought I caught them all.
Your comments are all valid. But what I have found in my career is that very competent application developers don't always know how to read an execution plan, or they don't have time to write a query several different ways to see which is best. Why? It's not what they are paid to do. They can write amazing C# (or any other language), but they have been using the same techniques they have always used to write SQL. This article was written to help them and others like them to see that writing SQL is different than writing application code. This does not mean that temp tables shouldn't be used or that variables to hold a queiried value shouldn't be used. The article is pointing that they may not be needed, and like you pointed out, testing needs to occur.
Mickey
ChrisM@Work (6/2/2015)
Nice thought-provoking article SQLMickey. Here's a thought for you:"Let’s take a look at a
stored procedurequery written four different ways."Whilst a junior developer might select one of the four without considering the others, an experienced developer will try all four and compare metrics. An informed choice is always better than a guess.
Also:
•Running small queries and inserting the results in temp tables to be used later in the stored procedure. One execution plan will be created and executed for each of these.
Sometimes this is more efficient than integrating the table source into the main query, and in any case, the “extra” execution plan won’t disappear if you integrate the table source into the main query – it will become part of a bigger and more complex execution plan, running the risk of an optimiser timeout. As above, an experienced developer will test, and compare metrics.
•Running small queries and inserting a single value in a variable to be used later in the stored procedure. One execution plan will be created and executed for each of these.
I’ll trade an “extra” execution plan for performance every time. The query optimiser doesn’t always get it right and when it doesn’t, temporary storage structures are usually the tool to use to help it along. Divide'n'Conquer, as Jeff says above.
Mickey Stuewe
Sr Database Developer
My blog
Follow me on twitter: @SQLMickey
Connect with me on LinkedIn
--------------------------------------------------------------------------
I laugh loudly and I laugh often. Just ask anyone who knows me.
June 2, 2015 at 8:53 pm
kbaker91 (6/2/2015)
Simple Question. How are the stats for the different queries acquired?
Hi,
You can turn gather the information with the following two lines of code before running your query.
SET STATISTICS IO ON
SET STATISTICS TIME ON
These settings will stay on for the current session you are in until you either end the session or turn them off by using the same commands with OFF. The metrics will be broken up across several lines, with the reads displayed per table used in the query.
Mickey
Mickey Stuewe
Sr Database Developer
My blog
Follow me on twitter: @SQLMickey
Connect with me on LinkedIn
--------------------------------------------------------------------------
I laugh loudly and I laugh often. Just ask anyone who knows me.
June 2, 2015 at 9:25 pm
Hi John,
I wish I could sit down with you over a cup of coffee and several hours to discuss all the items you've brought up. There are answers to all the questions you have below, and some of those answers will end with "that is how SQL Server handles it". A very short, over arching answer to the questions you pose is this.
Query plans are based, not only on the snytax of the query, but also on the shape of the data. Every index has statistics. The statistics will be broken up into, at most 200 segments. The optimizer uses the statistics to generate the plan, not the actual data in the tables. The query plan will also take into account any parameter values that are used in the query. This is called parameter sniffing. If the statistics are drastically uneven and a parameter passed in is not represented adequately in the statistics, then a bad plan might be chosen. If the statistics are severely out of date which causes the 200 segments to be uneven, then a bad plan might be chosen. If parameter sniffing is not used, but would have been helpful, then a bad plan might be chosen.
Execution plans that are created are stored in the Plan Cache. The Plan Cache phases out old plans based on storage space of the cache and age of the plan. If the query is not a parameterized query, then it will be in the Plan Cache N times where N is a different set of parameter values. This is known as Cache Plan bloat. The other day, I saw a non-parameterized query generated by entity framework appear in the Plan Cache 100 times. If the query had been parameterized, then it would have been in the Cache Plan once. Also, there is a SQL Server setting that can be used called "Optimize for Ad Hoc Workloads". It uses a whole other set of rules on whether a plan is stored in the Plan Cache or not.
I hope that helps. For more in depth answers to the questions you have below, I would recommend Grant Fritchey's book called SQL Server Query Performance Tuning. He goes pretty deep on some of the questions you posed.
Mickey
johncalyn (6/2/2015)
You've made an interesting point, but some of your concerns are specific to SQL Server and not widely applicable. Since this is an SQL Server forum, that's not surprising. But the solution you are proposing is more generic, and the caveats are due strictly to the limitations of SQL Server.I've encountered the idea that the optimizer "needs enough time to find a good path" only in the context of SQL Server, and I consider it a bug in the DB/optimizer design. I've only dealt with a handful of other databases (DB2, Oracle, PostgreSql), but I've never seen any of the others pick a 'best so far' plan. At least, they haven't been vocal about it, and they don't seem to be hindered by an optimizer time-out (that doesn't mean they haven't failed for lack of other resources).
There's also the problem of SQL Server not handling more than a handful (6? 7? I forget at the moment) of joins. Sub-queries are joins. Too many and the optimizer "runs out of time". There does not appear to be any means of controlling this time-out period (but I'm not a DBA, and I've been a year or so away from SQL Server). All the "fixes" I've seen are work-arounds that skirt the optimizer.
There is also the problem where the optimizer keeps re-optimizing the query, based upon the (presumed) volume of data run through it, and can re-select a different "good enough" plan after several runs on small volumes, which then bites you when you select (for example) a longer date range, and actually need that better-performing initially-selected (and slightly dumber) plan that doesn't take the data volume into account. I can understand the intent of the optimizer design. It even sort of makes sense for a database dominated by ad-hoc queries. But it is still a bug, as it means the database behavior is unstable and unreliable, and what worked fine in Test can't be counted on to work reliably in Production. In this case, your proposed solution would be more likely to be affected this behavior as well.
The upshot is that the programmer/DBA is stuck far too often with the programming model that you're arguing against, simply to deal with the shortcomings of the database optimizer. It isn't a failure to realize the alternatives, it is the failure of the database engine to be able to handle those alternatives in a reliable and predictable fashion.
It would be nice if we could do what you're suggesting, and trust the database engine to deliver the data in a reliable, and properly optimized fashion, but we're handicapped by an optimizer that insists on looking at both less code than the full query, and more data than just the query, without understanding the semantics of the query (time ranges, for example).
This is just a programmer's opinion, of course.
Does this help?
Mickey Stuewe
Sr Database Developer
My blog
Follow me on twitter: @SQLMickey
Connect with me on LinkedIn
--------------------------------------------------------------------------
I laugh loudly and I laugh often. Just ask anyone who knows me.
June 3, 2015 at 8:37 am
Thank you for the thoughtful reply, but I was aware of all these things.
I am not a DBA, but where I work (as an example) DBAs get involved in writing SQL only when something shows up egregiously bad. Normally the programmers write the SQL, run the EXPLAINs and report the performance as part of their code reviews. That's the rule (not always the reality, of course).
That said, I did spend over a year and a half tuning the SQL (and database indexing) for a purchased (and customized) application on an Oracle database. This was after several years working with a DB2 database as a programmer. And while that does not make me an expert, I did learn a few things.
Then we migrated an existing system (by mandate) from a DB2/UDB database on "the oldest server in the company" to an SQL Server database on a brand new, many-years-younger box, and ran into exactly the problems I enumerated earlier. The old box actually out-performed the brand new box (in some circumstances) for the same database structure (lifted wholesale. We were using Hibernate). The new DB would start out ok, but performance would abruptly degrade after an arbitrary period of time. We ended up putting in an override (whatever the SQL Server DBAs call it) that forced the optimizer to not re-optimize after the initial plan. In other words, to honor the long-term database stats, rather than the runtime stats. After that, performance was both good and stable.
Stability is important. In cases such as this (a real-time system with external contractual performance constraints) stability is critical. The behavior of a query should not be subject to random re-optimizations that may or may not completed. If the optimizer can 'time out', there should be a mechanism for forcing it to run to completion (or resource exhaustion). SQL Server does provide a DBA-level mechanism for "locking" the initial plan, but this is not a great substitute.
With all of this as background, I can come back to your article. The program design model that you are complaining about is inherently resistant to these particular problems with the optimizer, and without requiring the programmer to be an expert in the latter's foibles. It leads naturally to smaller, separately-optimized queries that are more likely to be stable. It would be nice if your suggested mechanism/design plan would work (as it might on Oracle or DB2, for example), because (theoretically) the SQL would be more efficient. But your design model is (in my opinion...) inherently flawed for a database where the programmer cannot rely upon the stable and predictable behavior of the optimizer.
Does this help?
June 3, 2015 at 3:04 pm
Missing closing bracket in first subquery?
Viewing 15 posts - 16 through 30 (of 57 total)
You must be logged in to reply to this topic. Login to reply