September 2, 2015 at 9:58 am
We have quite a few of those stored procs that have grown into monsters. In a lot of cases, there are joins to select statements that aggregate queries which I think is taking too much time.
My first questions is, wouldn't it be faster to have a common key in a view and store that stuff in a view, especially if it's being used in multiple stored procs?
2nd - the query analyzer should be able to tell me where the longest time period is happening in the query, correct? Sorry, I've never used it before - I'm a manager, not a developer. I know that people have used it in the past and have said that it's as optimized as possible, but it still runs too slow.
I have a feeling it's in the underlying way of how we store things, but if pulling from a view will speed things up, I'd like to give it a shot.
September 2, 2015 at 10:59 am
Based on the general suggestions you're making, no. What you're suggesting is referred to as Nested Views. It's a common code smell that leads to quite a few problems. The principle issue being that a view is just a query. It's not a materialization of the data or anything like that. So you'd be taking a potentially complex query, putting it into a view, then combining that complex query with other complex queries in other views. The query optimizer basically gets overwhelmed trying to sort out all the pertinent objects and you end up with really poor performance because of bad choices by the optimizer.
The better choice is to write each query as itself without attempting to reuse code through views. As much as that seems like how things ought to be done, it just isn't how it works best within SQL Server (or Oracle or MySQL and I suspect, but don't know, Postgres).
To identify slow running procedures, the best approach is to use extended events to capture the query metrics. You can use the output from there to group queries by execution count, resource use, run-time, etc. to identify which ones are the worst performers. If you have individual statements you need to identify within a given query, again, extended events is the way to go. Once you identify the query in question, you look the code and the structures, statistics and execution plans, to understand what to do to fix it.
"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
September 2, 2015 at 1:41 pm
I believe the biggest problem here is the report was designed to fill a need, and that need has changed about 100 times as people use it and want just one tweak, so it's been tweaked so much that it's held together with bandaids and prayers.
I'm not sure what you mean by write each query as itself though.
For example, I have a stored proc that the bulk of it is the main query joining 5-6 tables and then 5-6 inner joins to select statements to the point that the stored proc gives you vertigo when you look at it. I would think that joining to a select statement would not be the proper thing to do, but as you explained, putting it into a view wouldn't be doing anything but moving the query to a view and confounding the processor regardless?
I have users who complain about report speed because they take too long. I was thinking it's because we keep tacking on these statements to join data that may or may not have been designed to be joined that way so we're having to jump through serious hoops to do it.
September 2, 2015 at 3:28 pm
Yeah, that a was kind of badly worded statement. Apologies.
What I meant was, treat every query as a separate entity rather than try to use what looks like, from a coding standpoint, a valid object for code reuse such as a view.
Hopefully that makes more sense.
If you can, post the query and an execution plan (save the xml as a .sqlplan file). One of us is sure to jump on it and see if there are tuning opportunities.
"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
September 2, 2015 at 8:51 pm
When it comes to regular views I agree 100%. That said, you mentioned that the derived table is doing aggregations... In that case it MAY be a candidate for an INDEXED view.
Indexed views have some pretty stifling restrictions and they carry the same penalties as other indexes but they can add dramatic performance increases. Just a thought...
September 3, 2015 at 12:14 pm
I wish I could, I'd get in hot water if I posted things because for the most part, I'm not supposed to be tinkering at all 🙂 I'll take the advice from here and see what I can do.
Thanks man.
September 3, 2015 at 12:53 pm
sqlkohai (9/3/2015)
I wish I could, I'd get in hot water if I posted things because for the most part, I'm not supposed to be tinkering at all 🙂 I'll take the advice from here and see what I can do.Thanks man.
Understood. Don't hesitate to ask for help in the future.
"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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply