April 25, 2013 at 9:17 am
I have a question on performance as it relates to SQL queries and Stored Procedures/Views. Please help me understand the performance impacts of my question. Thank you.
I have a Stored Proc that runs 50+ hours to complete. It has 7 table variables that build on each other (i.e. DECLARE @Table1 ..., insert into @Table1 select stuff ...; DECLARE @Table2 ..., insert into @Table2 select more stuff plus @Table1 stuff from @Table1; etc.) I took this procedure and revamped it into a VIEW, actually each table variable was turned into its own view with a SELECT * FROM final_view at the end. The view approach produced the exact same results as stored procedure, it just ran over 30 minutes. VIEW : 30 minutes, Stored Proc : 50+ hours. It seems like a no brainer. What am I missing? What performance impacts would a view have over a stored proc, if any? Why is the view so much faster? (The view is not on a production server. It's on a report server.)
April 25, 2013 at 9:48 am
I bet it's the table variables. They can't have indexes on them nor stats maintained for the data.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
April 25, 2013 at 10:38 am
There's no inherent reason the proc should perform so badly.
Instead of table variables, use temp tables and index the temp tables appropriately.
I suspect the time can be reduced significantly from 30 mins even.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 25, 2013 at 10:55 am
Very likely because of the table variables. They don't have stats and hence (without a recompile) the optimiser will always estimate one row. It there's actually lots of rows, you can get a very sub-optimal execution plan from the incorrect row cardinality estimates that result. The more complex the query, often the worse the cardinality errors get.
Try temp tables with indexes if necessary (but don't over-index temp tables), see what that's like. Otherwise post some of the code and plans and we'll take a look.
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
April 25, 2013 at 10:55 am
Abu Dina (4/25/2013)
I bet it's the table variables. They can't have indexes on them nor stats maintained for the data.
Table variables don't have stats, but they certainly can have indexes, clustered and nonclustered.
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
April 25, 2013 at 11:09 am
Let's say, for whatever crazy reason, that temp tables are not an option. Is there a problem with using VIEWS? Especially if the views can be recycled for other uses...?
April 25, 2013 at 11:10 am
GilaMonster (4/25/2013)
Abu Dina (4/25/2013)
I bet it's the table variables. They can't have indexes on them nor stats maintained for the data.Table variables don't have stats, but they certainly can have indexes, clustered and nonclustered.
I didn't think table variables could have indexes defined on them either, only constraints -- the constraint declaration may end up causing SQL to create a supporting index, but AFAIK you can't explicitly create indexes on table variables.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 25, 2013 at 11:12 am
SQL_Enthusiast (4/25/2013)
Let's say, for whatever crazy reason, that temp tables are not an option. Is there a problem with using VIEWS? Especially if the views can be recycled for other uses...?
Not a problem per se, just likely to not perform as well as temp tables would.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 25, 2013 at 11:15 am
Thank you everyone for the information. I appreciate it!
April 25, 2013 at 11:20 am
So you're joining one view to another to another to arrive at a final view that contains all the other views nested? That's almost as bad an approach as the table functions. If you look at the execution plan, at the first operator, I'll be the Reason for Early Termination is "Timeout". That means the optimizer just gave up. You have an execution plan, but it's likely to be highly unstable and may not perform well over time.
"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
April 25, 2013 at 11:28 am
ScottPletcher (4/25/2013)
GilaMonster (4/25/2013)
Abu Dina (4/25/2013)
I bet it's the table variables. They can't have indexes on them nor stats maintained for the data.Table variables don't have stats, but they certainly can have indexes, clustered and nonclustered.
I didn't think table variables could have indexes defined on them either, only constraints -- the constraint declaration may end up causing SQL to create a supporting index, but AFAIK you can't explicitly create indexes on table variables.
You can't run a CREATE INDEX, but since unique and primary key constraints are always enforced by indexes, creating one creates an index, hence it is incorrect to say that table variable can't have indexes, they can, they just have to be tied to constraints.
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
April 25, 2013 at 11:30 am
SQL_Enthusiast (4/25/2013)
Let's say, for whatever crazy reason, that temp tables are not an option. Is there a problem with using VIEWS?
Maybe. Maybe not. Depends on what's in the views, how complex they are, how much nesting there is and a bunch of other factors
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
April 25, 2013 at 12:13 pm
Some weeks ago I had this issue. I used table variables to solve a problem. They were great until requirements changed and the code had to be adapted to deal with larger record sets. That's when I tried an failed to index the table variables. In the end I I used temp tables.
BTW gali, no malice intended with my previous comment. I do appreciate your knowledge just take chill pill. Great tip about the primary key constraint. Cheers
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
April 25, 2013 at 12:31 pm
Abu Dina (4/25/2013)
BTW gali, no malice intended with my previous comment. I do appreciate your knowledge just take chill pill.
Did I miss something in this thread, Abu, that it required this kind of feedback?
April 25, 2013 at 12:31 pm
Abu Dina (4/25/2013)
Some weeks ago I had this issue. I used table variables to solve a problem. They were great until requirements changed and the code had to be adapted to deal with larger record sets. That's when I tried an failed to index the table variables. In the end I I used temp tables.BTW gali, no malice intended with my previous comment. I do appreciate your knowledge just take chill pill. Great tip about the primary key constraint. Cheers
Don't see anything in any of Gail's responses that would even elicit a response to "take a chill pill."
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply