May 24, 2014 at 11:56 am
I usually change select into into a create table .... insert when tuning procedures, partially so I have the option of creating a clustered index, partially so I can keep all the create tables at the top.
I did that with a query I was working on earlier this week, and the first insert suddenly gained a pointless and very expensive table spool operator which I could not persuade the optimiser to remove. It was annoying, because adding a clustered index did improve performance over the same query without the cluster, but the table spool added so much overhead that the improvement from the clustered index was lost.
It was annoying. When I get some spare time, I need to dig into the why further, assuming I can reproduce the behaviour in a simpler scenario
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
May 24, 2014 at 2:23 pm
Thanks Everyone.
Please let me know,
How the table spool effect the performance? In what situation we can get table spool in execution plan and how can we avoid that?
May 24, 2014 at 5:42 pm
ramana3327 (5/24/2014)
Thanks Everyone.Please let me know,
How the table spool effect the performance? In what situation we can get table spool in execution plan and how can we avoid that?
This is impossible to answer as a black and white response. It depends of the query and a particular plan.
If there is a table spool on your plan, it's because the optimizer believes your query will run faster with it (true or not) or it's a set that at some point is needed for another query or a join, so it's temporary saved in tempdb.
Usually they are bad for performance because disk is slower than memory or in some cases, it can be avoided with different TSQL so you can avoid it and not creating it on tempdb.
May 25, 2014 at 9:55 am
sql-lover (5/24/2014)
ramana3327 (5/24/2014)
Thanks Everyone.Please let me know,
How the table spool effect the performance? In what situation we can get table spool in execution plan and how can we avoid that?
This is impossible to answer as a black and white response. It depends of the query and a particular plan.
If there is a table spool on your plan, it's because the optimizer believes your query will run faster with it (true or not) or it's a set that at some point is needed for another query or a join, so it's temporary saved in tempdb.
Usually they are bad for performance because disk is slower than memory or in some cases, it can be avoided with different TSQL so you can avoid it and not creating it on tempdb.
Ahhh... just to be clear... TempDB is not "Disk Only". TempDB starts in memory (very similar to table variables but with the possibility of stats being used) and only spools to disk if it gets too large for memory. If you do things right, that should never happen.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 26, 2014 at 3:39 pm
Hi,
You mentioned stats here. How do we know how often we have to update stats. How to find the optimizer is using right stats or not?
May 26, 2014 at 3:48 pm
ramana3327 (5/26/2014)
Hi,You mentioned stats here. How do we know how often we have to update stats. How to find the optimizer is using right stats or not?
Please start a new thread for that. Or search on SSC.com or the web for numerous references.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 26, 2014 at 4:41 pm
Ok. Thanks
May 26, 2014 at 5:12 pm
ramana3327 (5/26/2014)
Hi,You mentioned stats here. How do we know how often we have to update stats. How to find the optimizer is using right stats or not?
SQL Server does not hold statistics for table variables so row count is always 1.
A good rule of thumb is picking table variables for small sets, and when you see continue recompilations issues on your store procedure. And temporary tables for large data sets.
But like Kevin said, the main topic is being deviated here so you should open a new and fresh thread and start your new questions there.
May 26, 2014 at 8:26 pm
It is bad advice to recommend picking table variables with few numbers of rows. I can show you a one-row, one-column table variable that gets a bad plan when the temp table gets the right plan. Having statistics can be INCREDIBLY important!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 26, 2014 at 9:12 pm
TheSQLGuru (5/26/2014)
It is bad advice to recommend picking table variables with few numbers of rows. I can show you a one-row, one-column table variable that gets a bad plan when the temp table gets the right plan. Having statistics can be INCREDIBLY important!!
I said, "good rule of thumb ".
When talking about table variables vs temporary tables, there is no black and white answer. I said it before. I'm not advocating for one or the other.
General speaking and when dealing with very small sets, table variables perform better. It has been my experience for years, and you can revise documents online as well.
You can show us few examples when they do, and I can show you hundreds when do not, but overall, temporary tables perform better with large data sets and variable tables perform better with small data sets.
May 26, 2014 at 9:46 pm
I am coming to conclusion for the difference b/n select * into and Insert into like below
1) select * into #temp from table1 doesn't degrade any performance.
2) Select * into is minimally logged
3)It holds only schema locks other than that it won't block any users.
So it is much better than insert into statement.
If you want to create index on temp tables then it is better to create the temp table first.
May 27, 2014 at 2:11 am
ramana3327 (5/26/2014)
So it is much better than insert into statement.
No, you can't conclude that.
They both can be minimally logged, the both hold the same locks.
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
May 27, 2014 at 2:14 am
sql-lover (5/26/2014)
SQL Server does not hold statistics for table variables so row count is always 1.
Not actually true.
A good rule of thumb is picking table variables for small sets, and when you see continue recompilations issues on your store procedure. And temporary tables for large data sets.
No, absolutely not. I've reduced a procedure from 1 sec to 20ms by replacing a table variable which held 25 rows with an equivalent temp table. It's true that they cause less problems with small number of rows, but not that they should automatically be used when there are small numbers of rows.
If you want a rule of thumb, if you are not joining, filtering, aggregating (all you ever do with it is insert rows and then SELECT without a where clause), then a table variable is fine. Otherwise it probably should be avoided unless careful testing (with production loads and production data distribution) show that the performance impact of the table variable is negligible.
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
May 27, 2014 at 5:40 am
GilaMonster (5/27/2014)
sql-lover (5/26/2014)
SQL Server does not hold statistics for table variables so row count is always 1.Not actually true.
A good rule of thumb is picking table variables for small sets, and when you see continue recompilations issues on your store procedure. And temporary tables for large data sets.
No, absolutely not. I've reduced a procedure from 1 sec to 20ms by replacing a table variable which held 25 rows with an equivalent temp table. It's true that they cause less problems with small number of rows, but not that they should automatically be used when there are small numbers of rows.
If you want a rule of thumb, if you are not joining, filtering, aggregating (all you ever do with it is insert rows and then SELECT without a where clause), then a table variable is fine. Otherwise it probably should be avoided unless careful testing (with production loads and production data distribution) show that the performance impact of the table variable is negligible.
For God's sake... 🙁
Who's saying they automatically are better?
It is impossible to give an advice on temp tables without knowing the plan but for smaller sets they usually perform better. Sometimes not, but usually they do.
Each case is unique so that can change things a bit.
May 27, 2014 at 10:50 am
sql-lover (5/27/2014)
GilaMonster (5/27/2014)
sql-lover (5/26/2014)
SQL Server does not hold statistics for table variables so row count is always 1.Not actually true.
A good rule of thumb is picking table variables for small sets, and when you see continue recompilations issues on your store procedure. And temporary tables for large data sets.
No, absolutely not. I've reduced a procedure from 1 sec to 20ms by replacing a table variable which held 25 rows with an equivalent temp table. It's true that they cause less problems with small number of rows, but not that they should automatically be used when there are small numbers of rows.
If you want a rule of thumb, if you are not joining, filtering, aggregating (all you ever do with it is insert rows and then SELECT without a where clause), then a table variable is fine. Otherwise it probably should be avoided unless careful testing (with production loads and production data distribution) show that the performance impact of the table variable is negligible.
For God's sake... 🙁
Who's saying they automatically are better?
It is impossible to give an advice on temp tables without knowing the plan but for smaller sets they usually perform better. Sometimes not, but usually they do.
Each case is unique so that can change things a bit.
When you call something a rule of thumb, you are pretty much saying they (table variables) are better for small data sets. So by translation you are saying they are automatically better.
I have found that table variables are most often less performant than temp tables with small data sets and with large data sets. I wouldn't ever call it a rule of thumb to use table variables for any data set.
The kicker to all of this is 2014 where performance with table variables has been dramatically improved.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply