May 19, 2017 at 1:53 am
sarang1183 - Thursday, May 18, 2017 12:38 PMHi ChrisThanks for showing interest to understand the query. But unfortunately being SOX audited application, I am not allowed to share any potion of my code / query.
Surely SOX is about disclosure, not the opposite?
If you're unwilling to share your code then options for outside help are limited.
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
May 19, 2017 at 6:42 am
Ed Wagner - Thursday, May 18, 2017 9:06 PMI've found that most things run faster in 2016 than in 2012, but I've not done extensive testing in my own environment yet because others have to get things migrated and processes test first. Then again, like always, it all depends on the structure and the code.
I've done multiple upgrade projects by this point, my experience has been that around 5% of queries degrade, the rest either keep the same plan and performance or get performance improvements.
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
May 19, 2017 at 6:44 am
sarang1183 - Thursday, May 18, 2017 8:13 PMGilaMonsterThanks for your valuable feedback..
I will try to obfuscate the query... It may take time since its too complicated..
48 columns selected.. out of which
22 columns are the derived columns containing a subquery and that and each column's subquery has CASE with at least 4 conditions (WHEN . THEN)
We'll likely need the execution plan as well, actual plan. You can obfuscate that as well, just be consistent about the replacement names for tables and columns.
The fact that the query is complicated is probably why it's performing badly. Can you not try simplifying it, maybe with a couple temp tables to hold interim results?
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
May 19, 2017 at 8:59 am
GilaMonster - Friday, May 19, 2017 6:44 AMsarang1183 - Thursday, May 18, 2017 8:13 PMGilaMonsterThanks for your valuable feedback..
I will try to obfuscate the query... It may take time since its too complicated..
48 columns selected.. out of which
22 columns are the derived columns containing a subquery and that and each column's subquery has CASE with at least 4 conditions (WHEN . THEN)We'll likely need the execution plan as well, actual plan. You can obfuscate that as well, just be consistent about the replacement names for tables and columns.
The fact that the query is complicated is probably why it's performing badly. Can you not try simplifying it, maybe with a couple temp tables to hold interim results?
Hi GilaMonster
sure. I will provide this information.
I have also already started working on optimizing this query and obfuscating it. I was trying to avoid these manual efforts, because original query is in production since 5 years. Suddenly 2016 comes into picture just because of organization policy to replace 2012 by 2016, and indirectly forces us to change the query just because of changes carnality estimation/ execution engine.
This is little bit disappointing. When you migrate to higher version, anybody will always expect that your life will be more easy but looks like this is not the case with 2016 (for my scenario), instead of executing query smoothly (same as of 2012 ), its indirectly forcing me to optimize the query.
Anyways i should focus how to find a solution on this problem rather than to explain on how everything was working fine in 2012...
Thanks again for hearing my problems..
I will get back to you with plan and query...
May 24, 2017 at 4:02 pm
Hi All
Thanks a lot for your constructive replies and patience to hear my problem....I got the solution to this problem.. off course I had to change my query to simplify it.
Out of 22 complicate columns having subqueries, I simplied a column which had complex cases referring to table with million records.
I analyzed the CASE , created hashtable and took care all these complex conditions in table it self. Then I referred this hash table in the query instead of actual table there.
This resulted in giving records in just 13 seconds !!! This was taking 25 minutes before.
Now my query is behaving exactly same as of it was working in 2012 .
Thanks again....
May 26, 2017 at 2:54 pm
I've seen the same as everyone else - most things are the same or better, a few are very much worse.
My best advice if you have a lot of similar code that has the same issue is to simplify a copy of the query until it gets fast - including removing required pieces of functionality - until it gets fast (suddenly), then put the piece you removed back in and remove other pieces.
Once you're at the point where it's slow, but removing anything makes it fast, you know that section of all those queries needs to change. In one case I saw it was an extremely simply construct that was quite logical and worked quickly before, but got a really bad plan on the new one (a pointless cartesian self join) with the new version's query plan. A slight redesign got a good plan again, and had to be updated in all similar queries.
We ended up rolling in a couple other enhancements to those queries at the same time for a really significant performance gain after the work was done!
May 30, 2017 at 5:23 am
In my old job over time we upgraded from SQL2005 to SQL2008 to SQL2008R2 to SQL2012 and were going live with SQL2014 when I retired at the end of 2014.
For each upgrade we had the much the same experience - about 80% of queries ran within statistically the same time, about 10% had measurable performance improvement, about 5% had significant performance improvement and about 5% had measurable to significant performance degregation.
I would expect the same with an upgrade to SQL2016 or SQL2017 - most things improve but there will always be a few losers that have to be manually tweaked.
Some of the badly performing queries were show-stoppers for implementation in Production so they were fixed prior to go-live. The rest went into the technical debt queue for prioritisation and fixing, in other words business as normal regarding any type of upgrade. Fixing the critical bad performers is simply part of the cost of getting the new features the business wants.
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
May 30, 2017 at 3:02 pm
sarang1183 - Friday, May 19, 2017 8:59 AMGilaMonster - Friday, May 19, 2017 6:44 AMsarang1183 - Thursday, May 18, 2017 8:13 PMGilaMonsterThanks for your valuable feedback..
I will try to obfuscate the query... It may take time since its too complicated..
48 columns selected.. out of which
22 columns are the derived columns containing a subquery and that and each column's subquery has CASE with at least 4 conditions (WHEN . THEN)We'll likely need the execution plan as well, actual plan. You can obfuscate that as well, just be consistent about the replacement names for tables and columns.
The fact that the query is complicated is probably why it's performing badly. Can you not try simplifying it, maybe with a couple temp tables to hold interim results?
Hi GilaMonster
sure. I will provide this information.I have also already started working on optimizing this query and obfuscating it. I was trying to avoid these manual efforts, because original query is in production since 5 years. Suddenly 2016 comes into picture just because of organization policy to replace 2012 by 2016, and indirectly forces us to change the query just because of changes carnality estimation/ execution engine.
This is little bit disappointing. When you migrate to higher version, anybody will always expect that your life will be more easy but looks like this is not the case with 2016 (for my scenario), instead of executing query smoothly (same as of 2012 ), its indirectly forcing me to optimize the query.
Anyways i should focus how to find a solution on this problem rather than to explain on how everything was working fine in 2012...
Thanks again for hearing my problems..I will get back to you with plan and query...
From what you described previously, the query was an accident waiting to happen. Fixing it is the right way to go.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2017 at 5:50 pm
Jeff Moden - Tuesday, May 30, 2017 3:02 PMFrom what you described previously, the query was an accident waiting to happen. Fixing it is the right way to go.
There aren't many absolutes in SQL Server, but that comes close.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply