Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Performance Tuning for the procedure Expand / Collapse
Author
Message
Posted Tuesday, June 25, 2013 8:26 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:43 PM
Points: 3,667, Visits: 8,006
Sean Pearce (6/25/2013)
michal.lisinski (6/25/2013)
Hi Gila
You have the right to disagree with my opinion, but there is no "right" answer here. For data that is not meant to persist beyond the scope of the procedure, you are choosing between #temp tables and table variables.
Decision should depend on performance and reasonable load testing.
I read an article on SqlServerCentral about it some time ago http://www.sqlservercentral.com/articles/Temporary+Tables/66720
and I fully agree that real test give optimal solution.

Regards
Mike

There is a "correct" answer here. Table variables do not perform faster than temp tables and your suggestion will not help the OP with his performance issues.

Well, table variables might perform better in very specific ocassions. However, it won't be a significant improvement if the procedure is taking several minutes or even hours to run. There's not much to work here and even less to give a correct solution.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1467192
Posted Tuesday, June 25, 2013 8:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
Luis Cazares (6/25/2013)
Sean Pearce (6/25/2013)
michal.lisinski (6/25/2013)
Hi Gila
You have the right to disagree with my opinion, but there is no "right" answer here. For data that is not meant to persist beyond the scope of the procedure, you are choosing between #temp tables and table variables.
Decision should depend on performance and reasonable load testing.
I read an article on SqlServerCentral about it some time ago http://www.sqlservercentral.com/articles/Temporary+Tables/66720
and I fully agree that real test give optimal solution.

Regards
Mike

There is a "correct" answer here. Table variables do not perform faster than temp tables and your suggestion will not help the OP with his performance issues.

Well, table variables might perform better in very specific ocassions. However, it won't be a significant improvement if the procedure is taking several minutes or even hours to run. There's not much to work here and even less to give a correct solution.


Exploit the differences. Table variables are handy if you wish to save some state when a transaction rolls back. It's about all I use them for.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1467200
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse