Temp tables versus table variables

[DannyC]

G'day Scott and Mike,

Was wondering if one of you could shed some light on the broad reasoning behind many of the Jiwa stored procedures using table variables?

As background, I have been delving pretty deeply into SQL Server over the past few weeks with an aim to speeding up the Jiwa performance for a particular client. Aside from adjusting a few global SQL settings, I have also chucked in a few indexes, dropped a few unused indexes and have also changed a couple of stored procs from table variables to temp tables (with excellent improvements). But before I get carried away with converting more stored procedures to use temp tables I thought I'd touch base to see why table variables seem to be preferred over temp tables?

[Mike.Sheen]

As a general rule, table variables should be used if you expect the number of rows you populate the table with to be less than 10,000 to 15,000 rows - that will yield better performance in my experience. It all depends, however on the environment - RAM available to SQL Server and also the speed of the disk which the tempdb sits on (temp tables use the tempdb database).

Temp tables can have indexes, so with larger sets of data, they will perform better if you also create indexes which will benefit queries operating on them.

I've seen exactly what you've discovered - changing to temp tables yielding better performance - in some cases - it depends on the data you are operating on. We've been stung before where we've seen a performance issue in using temp tables so switched to table variables, but later found for other customers this caused performance degradation. And also vice-versa.

Sometimes it is impossible to choose an optimal strategy which suits all scenarios - it might be worth us exploring branching the code in stored procedures to use temp tables if the expected row count is greater than a certain threshold, otherwise use table variables. This would add complexity - which I don't like - and it would also increase the risk of mysterious and magical errors when a stored proc is modified and one forgets to modify both branches of the stored proc code - you could end up with different results depending on the number of rows in a table - not what anybody wants.

Do you know if the improved performance you obtained by switching to temp tables was using more than 10K or 15K rows in the table?

[DannyC]

Do you know if the improved performance you obtained by switching to temp tables was using more than 10K or 15K rows in the table?

I'd say the table would've easily exceeded 10,000 rows. I didn't check but I know it's a big database!

My research has led me to understand that table variables perform well when the data is somewhat static and there isn't many rows, just as you say. However the broad rule would be around 100 rows, not 10,000 rows. But that's what I've read and I haven't done any conclusive tests to prove or disprove that theory. I don't have a firm view.

I've seen exactly what you've discovered - changing to temp tables yielding better performance - in some cases - it depends on the data you are operating on. We've been stung before where we've seen a performance issue in using temp tables so switched to table variables, but later found for other customers this caused performance degradation. And also vice-versa.

Indeed.
And also as you've stated, the performance improvements are highly dependent on the SQL Server hardware.

Thanks for getting back to me.

[SBarnes]

The following to links i found beneficial in understanding the differences and the when and where to use them but one rule I would also suggest if you modify one of the standard procedures and can copy it and use the copy its a safer bet to do this so there is no possibility that one of the Jiwa upgrade scripts can wiping out your changes.

https://stackoverflow.com/questions/118 ... sql-server


https://www.red-gate.com/hub/product-le ... ary-tables

[DannyC]

I've read those articles (and others).
One of the takeaways I get is that table variables stop a query from going parallel. It's nicely stated here

If the source for the data inserted to the table is from a potentially expensive SELECT statement then consider that using a table variable will block the possibility of this using a parallel plan.

so if you have a multi-processor SQL Server (and who doesn't these days) then simply using a table variable will force that query execution to load up a single processor. Which is actually good for small datasets but not so good for as the quote says for large datasets.
Bottom, line - use them on a case-by-case basis.

[Mike.Sheen]

One of the takeaways I get is that table variables stop a query from going parallel. It's nicely stated here

If the source for the data inserted to the table is from a potentially expensive SELECT statement then consider that using a table variable will block the possibility of this using a parallel plan.

I didn't know that!

Thanks for sharing that.

[pricerc]

Where they are feasible, another alternative that can also give interesting performance results (positive and negative) is using CTEs (common table expressions) instead of temporary tables/variables.

They only work if your temporary data is populated once and is then 'read-only' and then only read once. But this covers many stored procedures that I've seen and that I've written.

e.g. instead of:

Code: Select all

create table #temp ... /* or declare @temp table....
insert #temp /* or @temp */
select * from ....
where ...

select * from #temp;


you can do

Code: Select all

with #temp as (
select * from ...
where ...
)

select * from #temp;


(yes, you can use # or @ in a CTE name, which is handy when you're experimenting).

The SQL optimizer will (possibly) choose a better query plan. But as with temporary tables vs variables, performance can go either way, depending on your SQL Server's physical configuration, the size of the datasets involved and the indexes available - I've seen queries go from minutes to seconds and vice-versa when converting both ways.

[Mike.Sheen]

performance can go either way, depending on your SQL Server's physical configuration, the size of the datasets involved and the indexes available - I've seen queries go from minutes to seconds and vice-versa when converting both ways.

I think we need the SQL Server team to conjour some magic to allow us to write TSQL abstracted away from temp tables / CTE's / Table variables and it decide the most appropriate mechanism at compile time based on the data set sizes and physical machine performance.

[Scott.Pearce]

write TSQL abstracted away from temp tables / CTE's / Table variables

They could call such an abstract object temp tabl....oh wait.

[Mike.Sheen]

My research has led me to understand that table variables perform well when the data is somewhat static and there isn't many rows, just as you say. However the broad rule would be around 100 rows, not 10,000 rows. But that's what I've read and I haven't done any conclusive tests to prove or disprove that theory.

I just ran the tests published in this article on a SQL 2014 version of the AdventureWorks database available here (because that's the database the article tested with).

I had similar results to the author - the 19,127 rows returned by the query took pretty much the same time for temp tables, CTE's and table variable queries.

CTE	Temp Table	Table Variable
760	820	810

Figures are in milliseconds - average of 10 runs.

And when a WHERE clause limits the results to just one row:

CTE	Temp Table	Table Variable
6	22	3

Note that in the last result the CTE figure of 6 is a little misleading - the first execution took 60ms, and the next 9 executions took 0ms averaging to be 6ms. Table variables had similar results - but not as accentuated - 16ms for the first execution, then 1ms for 8 executions and 4ms for one of the executions resulting in an average of 3ms. Temp tables was pretty consistent for each execution.

The conclusion I'm drawing there is CTE and Table variables cache their results more readily for smaller result sets - but that's only of benefit when you get cache hits - in a fluid environment the cached results might be purged before you get a cache hit, and then CTE and table variables offer WORSE performance than temp tables even on a small result set!