SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temporary table performing much better than table variable


Temporary table performing much better than table variable

Author
Message
Ajit Goswami-482889
Ajit Goswami-482889
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 107
I have a question now on how the Table Variable and Temporary table behaves because while executing a batch statements today i got an issue when i was using a set of table variables.
When i used the table variable batch is taking almost 30 mins to complete but when i use temporary table it is getting executed in less than 20 secs.
I am surprised by this result and don't know what is the right solution for me now i want it to have best performance also on the other hand i dont want to get into issues of cleaning up these temporary tables either.
Dwayne Dibley
Dwayne Dibley
SSC-Addicted
SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)

Group: General Forum Members
Points: 446 Visits: 3171
A search on this site will explain the differences encountered. However, as you are aiming for performance, go with the method that runs quickest. If these local temp tables are created in a stored proc, then they will be dropped when the session ends so you won't need to worry about cleaning up.
WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)

Group: General Forum Members
Points: 9976 Visits: 10574
Ajit Goswami-482889 (1/20/2011)
I have a question now on how the Table Variable and Temporary table behaves because while executing a batch statements today i got an issue when i was using a set of table variables.
When i used the table variable batch is taking almost 30 mins to complete but when i use temporary table it is getting executed in less than 20 secs.
I am surprised by this result and don't know what is the right solution for me now i want it to have best performance also on the other hand i dont want to get into issues of cleaning up these temporary tables either.


The performance problem is due to the lack of statistics on table variables... they don't have them, and the execution plan that SQL selects to use is frequently less than optimal. Among other things, SQL assumes that there is only one record in the table variable, and there is no knowledge of the density of the data values in the columns.

Temporary tables are automatically cleaned up when the connection is ended. If being used in a stored procedure, they are cleaned up when exiting the stored procedure.

I'd venture a few guesses about your query - how many of these are true?:
1. > 100 rows in the table variable.
2. Joined to other tables.
3. Where clause is utilized on the table variable.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32779 Visits: 18559
I would pay close attention to what Wayne has to say about the topic. He has some very good information.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Ajit Goswami-482889
Ajit Goswami-482889
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 107
All your guesses are true but my issues is not mainly about the performance but the weird behavior that is being demonstrated by SQL.

I am using 3 table variables and i get the data into the first 2 and then use them to populate the 3rd one.
So just changing the 3rd table variable into temp table to which i am just inserting this data from the same query is boosting the performance.

And i can't believe that this is because of any memory issues because i have tried this same query in 3 different servers all having different configuration.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32779 Visits: 18559
Ajit Goswami-482889 (1/20/2011)
All your guesses are true but my issues is not mainly about the performance but the weird behavior that is being demonstrated by SQL.

I am using 3 table variables and i get the data into the first 2 and then use them to populate the 3rd one.
So just changing the 3rd table variable into temp table to which i am just inserting this data from the same query is boosting the performance.

And i can't believe that this is because of any memory issues because i have tried this same query in 3 different servers all having different configuration.


This is not weird behavior by SQL, but expected. That is why Wayne made those three assertions. Table Variables may perform significantly slower if those three factors are true.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)

Group: General Forum Members
Points: 9976 Visits: 10574
CirquedeSQLeil (1/21/2011)
Ajit Goswami-482889 (1/20/2011)
All your guesses are true but my issues is not mainly about the performance but the weird behavior that is being demonstrated by SQL.

I am using 3 table variables and i get the data into the first 2 and then use them to populate the 3rd one.
So just changing the 3rd table variable into temp table to which i am just inserting this data from the same query is boosting the performance.

And i can't believe that this is because of any memory issues because i have tried this same query in 3 different servers all having different configuration.


This is not weird behavior by SQL, but expected. That is why Wayne made those three assertions. Table Variables may perform significantly slower if those three factors are true.


I'd go so far to say that they WILL perform significantly slower if any of those three factors are true - especially when combined. The issue, as previously explained, is because of lack of statistics on the table variables - they can't have any, and sql needs them to generate good execution plans.

Is there anything else different? Did you add any indexes to the temporary table?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10192 Visits: 5314
I often recommend trying it with all the possible combinations of temp tables and table variable. In some cases I have been looking at CTE's. Wayne provides some really good guidance but unfortunately like many things in SQL, IT DEPENDS..

Although it would be great to say this WILL perform better than that, in some cases using table variables will be faster in others slower, the same is true for temp tables. For big tables temp tables will most often be best, but not always..

I have had a couple situations where mixing and matching worked best, but this was found by testing.. And in least one case seemed to be completely couterintuitive.

CEWII
Ajit Goswami-482889
Ajit Goswami-482889
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 107
Thanks Wayne and everyone else for your insights.

I just wanted to be sure that i am not the only one who has faced this issue. :-)

I'll do my analysis on whats the best mix of temp tables and table variables in future.
WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)

Group: General Forum Members
Points: 9976 Visits: 10574
Ajit Goswami-482889 (1/23/2011)
Thanks Wayne and everyone else for your insights.

I just wanted to be sure that i am not the only one who has faced this issue. :-)

I'll do my analysis on whats the best mix of temp tables and table variables in future.


Don't be afraid to ask here for help if needed!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search