July 6, 2017 at 9:39 am
Hi All,
This is generic question which I may follow up and post some code on at a later date but I just wanted to ask for some initial feedback first.
Basically I inherited took some old T-SQL - of lots of joins of views and multiple tables which in turn called more views - most of all of which had grouping for aggregate calculations
I tidied it up in an effort to understand what was going on - rolled up sub-views - threw some windows functions in here and there and VOILA !! - I now have new tidy code in one Select statement
rather than one Select statement calling several views of which some call other views - and indeed go 4 levels deep in some places.
Pleased with un-ravelling code I checked the output matched the original - and all was good
Sadly performance has taken a significant hit (175%) so my question and what I want to understand in basic bullet point terms is - why would this be ?
Is there a general reason why Windows Functions are slower than Select Group by approach or replacing calling views with pure code (ie. SQL in brackets) ?
Appreciate this is fairly wide open but some initial pointer is fine because if nothing else I can just "untidy" it again if performance becomes an issue but for now it is not an issue - simply intrigued as to why a Windows Function simplified approach takes longer - or indeed why replacing views with pure SQL code would not use the same Query plan and hence should take approx. the same time.
Finally, I would not usually essentially "un-do" views but believe me in this instance it has helped me to do so 🙂
Any brief indicators I can then read up on would be greatly appreciated
Jabba
July 6, 2017 at 10:54 am
Can you give an example of what you were comparing for windowed function compared to select group by approach? Is there an index on the windowed function's PARTITION BY and ORDER BY columns?
As for using views versus derived table subqueries, nothing comes to mind offhand as why a derived table would be slower for the same query as what's in the view, unless there's something else running against your SQL Server at the time you did the testing for the rewritten query.
July 6, 2017 at 11:06 am
Just because two queries generate the same results does not mean they'll have the same execution plan, but yes we would need to see more details about what you're doing. For example are the views being filtered in the view whereas you're handling that logic in the windowing functions?
July 6, 2017 at 11:45 am
Just guessing (which is frequently unhelpful, it's why everyone is asking you to post the code), your functions. Are they by chance multi-statement table-valued functions? If so, those are notoriously poor performers. It may make the code neat, but they will make the performance tank.
To truly understand what's happening, get out the execution plans. Look at the old code with the nested views (and yes, you're right to want to clean that up, it frequently leads to optimizer timeouts and subsequently poor execution plans) and compare it to the new code. Are you getting scans where it used to be seeks? Is there a larger disparity between estimated and actual values? Look for that type of thing (and a whole lot more) to understand why performance is poor.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 6, 2017 at 12:15 pm
By "Windows Function", I'm assuming you mean "Windowed Function"... The kind with an OVER clause...
Windowing a function typically involves sorting data which tends to be one of the more expensive operations in a query plan.
Compare the old plan to the new one and and see where the cost differences are. Odds are The updated code changed which indexes are being used and most likely added some sort operations that weren't there before.
Also, mage sure your code simplification didn't involve the use of functions in any of the joins or in the WHERE clause.
Also, take a look at Itzik Ben-Gan's article regarding the creation of "POC" indexes for windowed functions... http://sqlmag.com/sql-server-2012/sql-server-2012-how-write-t-sql-window-functions-part-3
July 7, 2017 at 2:01 am
Thank You All - Chris Harsham, ZZartin, Grant Fritchey and Jason A. Long
Not easy to comment without code - I know - but your answers were exactly the sort I was looking for and very much appreciated
A quick summary of your combined responses... and I suspect it boils down to additional sorting brought on by the Windows Functions - <slaps forehead with palm> - Doh !! 🙂 - this makes sense.
Something I could and should have surmised from the execution plans of course <embarrassed face>
I am going to immerse myself in Ben-Gan's article which looks awesome and then start looking at the executions plans
Thanks again to all for your time and best regards
Jabba
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply