October 27, 2008 at 8:21 am
We recently tried optimizing a complex view with a third party tool. The results were terrific, with a 96% reduction in elapsed time for data access via the view.
The optimized view uses specific join algorithms, e.g. loop joins, hash join and merge joins instead of the more generic inner and outer joins used in the original view definition.
My question: what is your experience with third party T-SQL code optimizers, and are there and pitfalls you are aware of with their use? We are pretty happy with this initial test, but are somewhat leery of the 'too good to be true' possibility.
Thanks for any input.
October 27, 2008 at 8:40 am
rchantler (10/27/2008)
The optimized view uses specific join algorithms, e.g. loop joins, hash join and merge joins instead of the more generic inner and outer joins used in the original view definition.
Be very, very careful of any form of query hints. That includes index hints or the join hints you're using there. They may work well today, there's no guarantee that they will work well tomorrow or next week or any time the data changes. By using those you are preventing the SQL query optimiser from evaluating certain plans. (They're called hints, but they are not suggestions, they are commands)
I would generally say that you shouldn't use a hint unless you know exactly what the hint is doing, why it's there and you are absolutely 120% certain that you know better than the query optimiser as to how this query should run. Most of the time properly written SQL and good (useful, non-fragmented) indexes with up to date statistics will produce better results than a query with hints. There are rare edge cases but they are just that - rare.
If you do decide to use the hints, document very well where they are used and make sure you retest the query regularly to see if the hints do help.
Perhaps you can post the original view here, along with the table definitions, the index definitions and the execution plan (saved as a .sqlplan file, zipped and attached) and we could take a look.
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
October 27, 2008 at 9:15 am
Another thing to be concerned about, did the 3rd party tool recommend any index changes? Index changes to clustered or non-clustered indexes should be considered across the entire application the way the data is used and queried, not necessarily for one specific query or view. Is this view the only way the table(s) are queried?
October 28, 2008 at 7:45 am
Thank you Gail and Chris for responding to this.
There are only 2 ways we are using this view (now) - pulling data for a single day or pulling a subset of facts for a number of days.
Given that these are the access patterns and that performance is very important in this instance I will be creating 2 views, one for each of the access patterns; optimized.
I will heed your advice about periodic testing. The data is expected to grow over time but not to change in any structural way.
Best Regards ... Ray Chantler
October 28, 2008 at 10:09 am
rchantler (10/28/2008)
Given that these are the access patterns and that performance is very important in this instance I will be creating 2 views, one for each of the access patterns; optimized.
Good idea.
I would still suggest that you post the query here and let someone take a look at it. If hints are getting that kind of improvement then there's a good chance that something can be changed in the query or indexes so that the hint isn't necessary and the performance is good.
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
October 29, 2008 at 9:32 am
GilaMonster (10/28/2008)
rchantler (10/28/2008)
Given that these are the access patterns and that performance is very important in this instance I will be creating 2 views, one for each of the access patterns; optimized.Good idea.
I would still suggest that you post the query here and let someone take a look at it. If hints are getting that kind of improvement then there's a good chance that something can be changed in the query or indexes so that the hint isn't necessary and the performance is good.
As usual I agree with Gail here - on both accounts. I have indeed come across queries that simply would not optimize correctly no matter what and query hints were the only way to consistently get the proper plan, but those instances are VERY rare - and I have done this for a living for the past decade or so. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 31, 2008 at 1:08 pm
I would be very, very careful about any query with a LOOP join if your data is going to grow. This is a real killer. If the table being joined to has only a few rows - let's say 20 or so - which would be appropriate for some kind of category code, then the LOOP join is the fastest.
If the table grows, then it becomes a real performance dog very quickly.
Todd Fifield
October 31, 2008 at 4:11 pm
What is the preferred option, with(nolock) or with(readpast).
What is the preferred option, if exists or if @@rowcount.
What is the preferred option, temporary table or derived table.
What is the preferred option, static or dynamic query.
What is the preferred option, top or set rowcount.
What is the preferred option, scope_identity() or @@identity
Regards
Kevin
October 31, 2008 at 6:48 pm
clive (10/31/2008)
What is the preferred option, with(nolock) or with(readpast).What is the preferred option, if exists or if @@rowcount.
What is the preferred option, temporary table or derived table.
What is the preferred option, static or dynamic query.
What is the preferred option, top or set rowcount.
What is the preferred option, scope_identity() or @@identity
Regards
Kevin
All of these fall into the category of "it depends" except the last one... Always use Scope_Identity instead of @@Identity.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 31, 2008 at 9:36 pm
What would be the name of this tool?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 1, 2008 at 2:45 am
clive (10/31/2008)
What is the preferred option, with(nolock) or with(readpast).
Depends. Do you want to read dirty data or skip locked rows entirely. Better option would be to use neither and make sure that your queries are efficient and indexes appropriate. That will reduce the amount of locks taken and needed
What is the preferred option, if exists or if @@rowcount.
Depends what you're doing. However, checking for existence is usually faster than counting all the rows to see if the count is > 1. Especially if there are a lot of rows
What is the preferred option, temporary table or derived table.
Depends. Test both and see which is faster. Sometimes doing a query all in one is fastest. Sometimes putting intermediary pieces into a temp table is fastest
What is the preferred option, static or dynamic query.
Not sure what you mean here
What is the preferred option, top or set rowcount.
Top, as Rowcount is partially deprecated in SQL 2005 and 2008
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
November 1, 2008 at 8:29 am
While I agree with everything that Jeff and Gail said, I wanted to add to a couple of these:
clive (10/31/2008)
What is the preferred option, if exists or if @@rowcount.
If @@ROWCOUNT is already set then you should use it. Otherwise, use EXISTS() as Gail pointed out.
What is the preferred option, temporary table or derived table.
The general thinking is to prefer derived tables because they leave the optimizer with more freedom to optimize. However, experience also shows that the more complex the query, the less likely the optimizer is going to make the best choices. Consequently, for significantly complex queries, converting one or more derived tables to temporary tables serves both as a way to force certain decisions on the optimizer and as a way to simplify the query, thus allowing the optimizer to make better decisions with what remains. The catch here is that if you convert the wrong derived table to a temporary table, then you end up forcing a bad choice on the optimizer, thus making the overall performance worse instead of better.
What is the preferred option, static or dynamic query.
Static is preferred because it is safer, easier to read and write and (marginally) faster. However, some parametrized queries cannot be done as static SQL and many more can only be done in a terribly inefficient way. In these cases you should use dynamic SQL instead, but with great caution to avoid the possibility of Injection.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 10, 2008 at 4:37 am
A lot of the comments I have seen on this thread warn about the pitfalls of adding hints to T-SQL code. I agree that if you try to optimise the code manually then you need to be very cautious about adding hints, as very few people have the skills to use the right hint for the given circumstance. I have seen various optimising legends that adding this or that tweak will improve performance, long after the need to make the tweak disappeared due to improvements in the SQL Optimiser.
If you have a tool that optimises T-SQL DML code by adding hints, it is only as good as its own optimiser. Some products may have a very good optimiser (e.g. IBM Data Joiner) that is able to produce a better access path than the SQL Server optimiser in some situations. However, you should treat the resulting DML as valid for your current release of SQL Server only. If you move to a new SQL Server version, any optimised code should be regenerated as hints that were good in the old version may be counter-productive in the new SQL Server version.
Ultimately very few organisations make extensive use of SQL optimisers, due to the implied need to refactor the resulting SQL whenever the database software is upgraded. Most organisations prefer code stability to extacting ultimate performance, but in the right situation a SQL optimiser can deliver a business advantage by speeding up a critical query.
I guess the above is a long way of saying 'it depends'.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 10, 2008 at 8:24 am
EdVassie (11/10/2008)
..
.
I guess the above is a long way of saying 'it depends'.
No need to 'guess' there! That was indeed a LONG way of saying "it depends"!! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 11, 2008 at 3:32 am
Hey, what is the third party tool that you have used. Is it a freeware or a shareware ? Can you share the link.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply