September 9, 2014 at 12:09 pm
Hello,
I have TSQL query using UNION ALL. First subset returns 92 rows, second subset return 0 (ZERO) rows. Now if I execute them separately they execute subsecond. If I put UNION ALL between them then it hangs forever exactly on 88s row.
I understand I need to provide execution plans, actual queries etc but before going into all this details, why on earth even in theory UNION ALL with empty result will result in a hang of the first subset?
Based on estimated execution plan 2 subqueries are performed in parallel and then CONCATENTION is being used which shall result with just first subset immediately displayed in result.
Estimated execution plan attached, I can not get actual execution plan because query never completes.
September 9, 2014 at 12:47 pm
I see plenty of case statements and implicit conversions going on which leads me to believe the second SELECT which has no rows, data types can't be figured out. Perhaps you have case statements returning different data types but no rows coming back. Forcing type conversions on variant columns may help. If you are using sql express then you won't get parallelism.
September 9, 2014 at 12:52 pm
I don't understand how this can be an explanation since there are no errors being produced and results are always hanging exactly at the same spot (row 88 out of total of 94 from first subset). This is being run on Enterprise edition and it DOES NOT return anything at all after row 88 hanging indefinetly using high CPU and no output.
September 9, 2014 at 1:03 pm
There is a lot of performance improvements you can make to your sql. Now the DTA is not always correct but when a single query comes back with 4 missing indexes there are some serious issues with indexing.
It doesn't effect performance but you should use ANSI-92 style joins instead of the old ANSI-89 style joins. They are easier to read and less prone to error.
Here is what your first query might look like using ANSI-92 style joins.
FROM ef_monthly_loan_balances lb
INNER JOIN ef_loan_master l ON lb.loanid = l.loan_id
INNER JOIN ef_borrowers b ON b.customer_id = l.client_id
WHERE lb.created = '2014-07-31'
AND b.customer_id in ('CLIENT_20070910162652', '.....') --Shortened to save space on the forum
AND l.participation IN ('Y', 'N')
Here is one example of a major performance boost. Why do you have an ISNULL wrapped around a hardcoded NULL? This renders any index on that column unusable. Not to mention that you are comparing 1 value to the same value from the same table!!! You could remove that and the query would be the same.
AND isnull( null , lb.clientid) = lb.clientid
Your data conditions seem to be an issue too. Notice how those are all generating index scans in your execution plan? It seems you want all rows for a given month? You should do these calculations against a scalar value instead of your column.
I agree with Bill that you may have a number of implicit datatype conversions going on here which is causing some of the performance challenges.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2014 at 1:05 pm
artisticcheese (9/9/2014)
I don't understand how this can be an explanation since there are no errors being produced and results are always hanging exactly at the same spot (row 88 out of total of 94 from first subset). This is being run on Enterprise edition and it DOES NOT return anything at all after row 88 hanging indefinetly using high CPU and no output.
It must not be hanging indefinitely. You were able to post an actual execution plan. There is lots of low hanging fruit in this query for performance enhancements.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2014 at 1:07 pm
I know about unoptimized quires, indexes missing etc. This is NOT perfomance issue though, it's high CPU hang issue (like you see if you have code loop). Engine stops at row 88 and never progresses any further and just eating CPU after that.
If it would be wrong result datatype then it would produce an error and it will be immediate.
Also I just rebuild all indexes and issue is gone, so it can not be issue with datatype either becouse of that.
This must be either a bug in compiler or some quirky conditions which is causing this behavior.
September 9, 2014 at 1:08 pm
Yes, it DOES hangs indefinetely with high CPU. I waited for 30 minutes for results to come back which stayed on row 88 all this time. Again we are talking about UNION ALL of 98 rows with 0 rows. Execution plan I posted was preliminary one.
September 9, 2014 at 1:17 pm
artisticcheese (9/9/2014)
I know about unoptimized quires, indexes missing etc. This is NOT perfomance issue though, it's high CPU hang issue (like you see if you have code loop). Engine stops at row 88 and never progresses any further and just eating CPU after that.If it would be wrong result datatype then it would produce an error and it will be immediate.
Also I just rebuild all indexes and issue is gone, so it can not be issue with datatype either becouse of that.
This must be either a bug in compiler or some quirky conditions which is causing this behavior.
So you just rebuilt your indexes and the issue is now gone? How can you say it wasn't a performance issue. Your code is full of unoptimized pieces. Looking at the execution plan there are many index scans, this is a pretty good sign that indexes were the root of the issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2014 at 1:23 pm
Yes, that's all I did and yes I believe it's not performance/unoptimized queries issue.
If it would be any of those then results would eventually come up and it would not spin for hours exactly at the same place each time it's being run.
Again.
First subset executing separately executes for 3s and returns 92 rows
Second subset executing separately executes for 1s and returns 0 rows
Putting UNION ALL between them hangs indefinitely with high CPU at row 88 on first subset
Rebuilding indexes fixing the issue
Would you based on data above conclude it was perfomance issue?
September 9, 2014 at 1:33 pm
artisticcheese (9/9/2014)
Yes, that's all I did and yes I believe it's not performance/unoptimized queries issue.If it would be any of those then results would eventually come up and it would not spin for hours exactly at the same place each time it's being run.
Again.
First subset executing separately executes for 3s and returns 92 rows
Second subset executing separately executes for 1s and returns 0 rows
Putting UNION ALL between them hangs indefinitely with high CPU at row 88 on first subset
Rebuilding indexes fixing the issue
Would you based on data above conclude it was perfomance issue?
I absolutely would say it was a performance issue based on the fact that rebuilding the indexes fixed it. I would say that you still have a pretty serious performance issue if your query take 3s to return 92 rows.
I am curious, how do you know that it hung on row 88?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2014 at 2:16 pm
I know it hangs on row 88 becouse that's the last row I see in output. Output appears exactly 3 seconds (like first query is finished) and quickly shows all 87 rows and then hangs on 88.
If you look at estimated execution plan you can see there is no interdependency between 2 queries and they are supposed to be executed in parrallel and then use CONCACT at the last stage. If it would be perfomance issue there either will eventually be some sort of finished exection or it would not hang on each row slowly progressing forward etc or each subquery will be slow but none are true. I don't think it's perfomance issue at all and I think it's a bug in SQL engine.
September 9, 2014 at 2:43 pm
You can't go by visible output. SqlServer uses buffers for output so you'll only retrieve full pages but there might be some remaining rows waiting for the buffer page to fill. Along with the experts here I too recommend using newer syntax and not relying on sql to make marginal code work through implicit and unoptimized code. There is a very tiny chance you are correct about hitting a sql bug BUT you likely won't hit the bug if you write better code.
September 9, 2014 at 2:45 pm
artisticcheese (9/9/2014)
I know it hangs on row 88 becouse that's the last row I see in output. Output appears exactly 3 seconds (like first query is finished) and quickly shows all 87 rows and then hangs on 88.If you look at estimated execution plan you can see there is no interdependency between 2 queries and they are supposed to be executed in parrallel and then use CONCACT at the last stage. If it would be perfomance issue there either will eventually be some sort of finished exection or it would not hang on each row slowly progressing forward etc or each subquery will be slow but none are true. I don't think it's perfomance issue at all and I think it's a bug in SQL engine.
Well if you are determined it is a bug in SQL then you will need to be able to reproduce the issue. It seems that you might have a problem with that since rebuilding your indexes fixed the problem. BTW, you didn't post an estimated plan, you posted an actual execution plan which can't get produced until the query completes. So either the plan you posted is not the same as the query as your were having problems with or the query did in fact finish at some point.
Regardless of the reason your query needs some serious performance help. Glad you were able to get your query working, albeit a bit slower than it should be. If you want help making it faster post us some details and we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2014 at 3:19 pm
I narrowed down the issue to statistics, updating statistics on 3 tables used in quyery query complete immediately. No index rebuild necessary.
Estimated execution plan before statistics was updated and actual execution plan after statistics was updated as attached. I'm pretty sure it's a bug and I can reproduce it at will since I have backup of DB.
As soon as statistics is updated entire set is returned in 3 s.
September 9, 2014 at 3:28 pm
artisticcheese (9/9/2014)
I narrowed down the issue to statistics, updating statistics on 3 tables used in quyery query complete immediately. No index rebuild necessary.Estimated execution plan before statistics was updated and actual execution plan after statistics was updated as attached. I'm pretty sure it's a bug and I can reproduce it at will since I have backup of DB.
As soon as statistics is updated entire set is returned in 3 s.
That isn't a bug. Reproducing this from a given backup is not reproducing the issue. Reproducing it would mean you could recreate this issue in a new database.
This is a performance issue due to non-performant code and stale statistics. Rebuilding the index will cause an update of the statistics. From the execution plan you posted you have some serious problems with both your indexes and the code. I would be willing to bet that with some index tuning and query optimization that query can run in less than 1 second. You are returning less than 100 rows, 3 seconds is a clear sign that there are some very serious performance issues bubbling near the surface.
Do you want help fixing the query?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply