October 25, 2016 at 10:06 am
I'd welcome any helpful comments on the optimal handling of concurrency issues in this thread. Cheers!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 25, 2016 at 10:09 am
Should I be concerned for this person's info?
October 25, 2016 at 10:53 am
Luis Cazares (10/25/2016)
Should I be concerned for this person's info?
If by info you mean data, yes. But there's nothing any of us can do about it.
October 25, 2016 at 11:48 am
So this ought to be fun...
Dev asked me to take a look at a view that isn't performing as well as it did on the system it used to be on. I pulled up the view definition, turns out it joins to a couple other views, so we've got nested views.
Now, that's not a guaranteed performance killer, but it's not a good start. Which is when I find out from the dev that the views that get joined to themselves have nested views, 5-6 levels deep all told...
As the dev pointed out, he inherited these views when he was hired from someone else.
Now, as for why they performed better before, well, the DB and app in question was recently migrated (on a tight time frame) from Oracle RDBMS 11. As I told him, I believe Oracle handles views differently from MS SQL and that likely the only short or long-term fix is going to be to "de-nest" the views.
Trying to get an actual query plan from the view, I ran the query (in QA) and it's been running for ~30 minutes so far...
Not sure I'm going to have a magic bullet to fix this, unless it's a bullet to the views' head...
October 25, 2016 at 11:56 am
jasona.work (10/25/2016)
So this ought to be fun...Dev asked me to take a look at a view that isn't performing as well as it did on the system it used to be on. I pulled up the view definition, turns out it joins to a couple other views, so we've got nested views.
Now, that's not a guaranteed performance killer, but it's not a good start. Which is when I find out from the dev that the views that get joined to themselves have nested views, 5-6 levels deep all told...
Sounds like one of my environments. Views built by a power user using SQL Designer because he can't write code, but he knows what data he wants. Then he makes his team use the views when they could write a query joining 3 tables instead of 15 nested views to get the same info.
October 25, 2016 at 12:10 pm
Brandie Tarvin (10/25/2016)
jasona.work (10/25/2016)
So this ought to be fun...Dev asked me to take a look at a view that isn't performing as well as it did on the system it used to be on. I pulled up the view definition, turns out it joins to a couple other views, so we've got nested views.
Now, that's not a guaranteed performance killer, but it's not a good start. Which is when I find out from the dev that the views that get joined to themselves have nested views, 5-6 levels deep all told...
Sounds like one of my environments. Views built by a power user using SQL Designer because he can't write code, but he knows what data he wants. Then he makes his team use the views when they could write a query joining 3 tables instead of 15 nested views to get the same info.
The "good" news is, the dev who asked about this is also now the guy in charge of it, so he can change it (as long as it doesn't break the app.)
Ought to be interesting to see what comes out of all this...
Considering that my run of the query is *still* going right now, so we're at ~50 minutes + to run (of course, I didn't put a where clause on the query, that might've helped. Maybe I'll kill it and try again with a where, or a top 1)
October 25, 2016 at 12:37 pm
jasona.work (10/25/2016)
Brandie Tarvin (10/25/2016)
jasona.work (10/25/2016)
So this ought to be fun...Dev asked me to take a look at a view that isn't performing as well as it did on the system it used to be on. I pulled up the view definition, turns out it joins to a couple other views, so we've got nested views.
Now, that's not a guaranteed performance killer, but it's not a good start. Which is when I find out from the dev that the views that get joined to themselves have nested views, 5-6 levels deep all told...
Sounds like one of my environments. Views built by a power user using SQL Designer because he can't write code, but he knows what data he wants. Then he makes his team use the views when they could write a query joining 3 tables instead of 15 nested views to get the same info.
The "good" news is, the dev who asked about this is also now the guy in charge of it, so he can change it (as long as it doesn't break the app.)
Ought to be interesting to see what comes out of all this...
Considering that my run of the query is *still* going right now, so we're at ~50 minutes + to run (of course, I didn't put a where clause on the query, that might've helped. Maybe I'll kill it and try again with a where, or a top 1)
That could change the execution plan.
October 25, 2016 at 12:44 pm
Lynn Pettis (10/25/2016)
jasona.work (10/25/2016)
Brandie Tarvin (10/25/2016)
jasona.work (10/25/2016)
So this ought to be fun...Dev asked me to take a look at a view that isn't performing as well as it did on the system it used to be on. I pulled up the view definition, turns out it joins to a couple other views, so we've got nested views.
Now, that's not a guaranteed performance killer, but it's not a good start. Which is when I find out from the dev that the views that get joined to themselves have nested views, 5-6 levels deep all told...
Sounds like one of my environments. Views built by a power user using SQL Designer because he can't write code, but he knows what data he wants. Then he makes his team use the views when they could write a query joining 3 tables instead of 15 nested views to get the same info.
The "good" news is, the dev who asked about this is also now the guy in charge of it, so he can change it (as long as it doesn't break the app.)
Ought to be interesting to see what comes out of all this...
Considering that my run of the query is *still* going right now, so we're at ~50 minutes + to run (of course, I didn't put a where clause on the query, that might've helped. Maybe I'll kill it and try again with a where, or a top 1)
That could change the execution plan.
Hmm. Hadn't thought of that.
I'll have to try it again tomorrow, as-is, and see what comes up. I did manage to get a result back putting in a where to limit the return, it took 16 seconds (roughly per SSMS) to pop up the result.
I think the solution is going to be a re-write, frankly.
October 25, 2016 at 12:54 pm
jasona.work (10/25/2016)
Lynn Pettis (10/25/2016)
jasona.work (10/25/2016)
Brandie Tarvin (10/25/2016)
jasona.work (10/25/2016)
So this ought to be fun...Dev asked me to take a look at a view that isn't performing as well as it did on the system it used to be on. I pulled up the view definition, turns out it joins to a couple other views, so we've got nested views.
Now, that's not a guaranteed performance killer, but it's not a good start. Which is when I find out from the dev that the views that get joined to themselves have nested views, 5-6 levels deep all told...
Sounds like one of my environments. Views built by a power user using SQL Designer because he can't write code, but he knows what data he wants. Then he makes his team use the views when they could write a query joining 3 tables instead of 15 nested views to get the same info.
The "good" news is, the dev who asked about this is also now the guy in charge of it, so he can change it (as long as it doesn't break the app.)
Ought to be interesting to see what comes out of all this...
Considering that my run of the query is *still* going right now, so we're at ~50 minutes + to run (of course, I didn't put a where clause on the query, that might've helped. Maybe I'll kill it and try again with a where, or a top 1)
That could change the execution plan.
Hmm. Hadn't thought of that.
I'll have to try it again tomorrow, as-is, and see what comes up. I did manage to get a result back putting in a where to limit the return, it took 16 seconds (roughly per SSMS) to pop up the result.
I think the solution is going to be a re-write, frankly.
That's direction I would look.
October 25, 2016 at 1:40 pm
Lynn Pettis (10/25/2016)
jasona.work (10/25/2016)
Lynn Pettis (10/25/2016)
jasona.work (10/25/2016)
Brandie Tarvin (10/25/2016)
jasona.work (10/25/2016)
So this ought to be fun...Dev asked me to take a look at a view that isn't performing as well as it did on the system it used to be on. I pulled up the view definition, turns out it joins to a couple other views, so we've got nested views.
Now, that's not a guaranteed performance killer, but it's not a good start. Which is when I find out from the dev that the views that get joined to themselves have nested views, 5-6 levels deep all told...
Sounds like one of my environments. Views built by a power user using SQL Designer because he can't write code, but he knows what data he wants. Then he makes his team use the views when they could write a query joining 3 tables instead of 15 nested views to get the same info.
The "good" news is, the dev who asked about this is also now the guy in charge of it, so he can change it (as long as it doesn't break the app.)
Ought to be interesting to see what comes out of all this...
Considering that my run of the query is *still* going right now, so we're at ~50 minutes + to run (of course, I didn't put a where clause on the query, that might've helped. Maybe I'll kill it and try again with a where, or a top 1)
That could change the execution plan.
Hmm. Hadn't thought of that.
I'll have to try it again tomorrow, as-is, and see what comes up. I did manage to get a result back putting in a where to limit the return, it took 16 seconds (roughly per SSMS) to pop up the result.
I think the solution is going to be a re-write, frankly.
That's direction I would look.
Might be faster to rewrite this than getting the execution plan. 😀
_______________________________________________________________
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/
October 25, 2016 at 1:45 pm
Spammers are getting better, now even the topics they post look legit.
October 26, 2016 at 3:21 am
Lynn Pettis (10/25/2016)
Spammers are getting better, now even the topics they post look legit.
I liked Eric's response - "get a job you bum".
October 26, 2016 at 4:23 am
<headdesk>
I don't know whether to cry about the problem or sing praise that someone finally got it.
A dev has a ticket to improve a process he wrote. I got involved because there was a reference to a table I recently changed. So I reached out to him to let him know about my changes, thinking they might help. He described his problem and it seemed my changes were tangential. So I said "Let me know if I can help you troubleshoot your code. Send me your SQL when you have a chance."
Yesterday he finally sent me the code with a note that basically says "Here are my changes. I found things worked much better when I created all the records in one shot instead of passing each record into a loop to process."
I just want to scream YA THINK?, run over to him and explain RBAR BAD.
On the other hand, he did figure it out before he sent the code to me... But now I wonder what other code he's been writing that is using RBAR. And if he's doing it, how many of the other developers are doing it too?
October 26, 2016 at 5:07 am
Brandie Tarvin (10/26/2016)[hrOn the other hand, he did figure it out before he sent the code to me... But now I wonder what other code he's been writing that is using RBAR. And if he's doing it, how many of the other developers are doing it too?
Because he figured it out himself, most likely it will stick in his mind better then if someone told him. You learn best from your own mistakes! About all other developers... get on the barricades! An overwhelming load is coming your way :crying:
October 26, 2016 at 5:12 am
Brandie Tarvin (10/26/2016)
<headdesk>I don't know whether to cry about the problem or sing praise that someone finally got it.
A dev has a ticket to improve a process he wrote. I got involved because there was a reference to a table I recently changed. So I reached out to him to let him know about my changes, thinking they might help. He described his problem and it seemed my changes were tangential. So I said "Let me know if I can help you troubleshoot your code. Send me your SQL when you have a chance."
Yesterday he finally sent me the code with a note that basically says "Here are my changes. I found things worked much better when I created all the records in one shot instead of passing each record into a loop to process."
I just want to scream YA THINK?, run over to him and explain RBAR BAD.
On the other hand, he did figure it out before he sent the code to me... But now I wonder what other code he's been writing that is using RBAR. And if he's doing it, how many of the other developers are doing it too?
I think it's a clear win. He figured it out and it sounds like it stuck. I guess you'll know the next time he releases code, right? I'd take it as a victory.
Viewing 15 posts - 56,416 through 56,430 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply