Is there any limit for the number of temp tables to create in a stored procedure? One of my functionality is so huge with so many hanging modules. The best way I found was to create temp tables for the hanging modules and pull data out from those tables. Right now, there are 22 temp tables in that stored procedure of 2500 lines. Is there any potential limit or performance threat related to count of temp tables?
-
Why not use table-variables instead?Dai– Dai2018-06-22 02:32:16 +00:00Commented Jun 22, 2018 at 2:32
-
1I am aware that temp tables provide better performance than temp variables. My code also involves doing some DDL/DML operations for those temp table dataRick– Rick2018-06-22 02:34:34 +00:00Commented Jun 22, 2018 at 2:34
-
2@Dai in most situations, temp tables will be better than table variables. Table variables will spill to disk (tempdb) if large enough, and they can lead to poor execution plans due to incorrect row estimates.alroc– alroc2018-06-22 02:35:57 +00:00Commented Jun 22, 2018 at 2:35
-
3@alroc: clarification: table variables are ALWAYS backed by disk regardless of size.Mitch Wheat– Mitch Wheat2018-06-22 02:47:26 +00:00Commented Jun 22, 2018 at 2:47
-
422 temp tables doesn't sound ideal, but I'd be more worried by a 2500 line stored proc....Mitch Wheat– Mitch Wheat2018-06-22 03:05:31 +00:00Commented Jun 22, 2018 at 3:05
Add a comment
|
1 Answer
Practically speaking there's no limit to how many temp tables you can have simultaneously.
You would probably have to have an out of control process spewing out and populating temp tables over and over to run into issues related simply to the overhead of the number of temp tables.
You probably want to pay more attention to how much data is moving around in them though as tempdb resources are shared across all databases, and you could end up monopolizing things like IO and CPU if you just go hog wild. But if you need 22 temp tables, you need 22 temp tables. You can monopolize your resources against a single temp table as easily, so it's not a magic bullet simply to have fewer tables. So I wouldn't lose much sleep over that aspect of it. The footprint of each additional temp table is pretty negligible. They do maintain their own statistics, so that is one factor that might come into play (again, if you were to go absolutely nuts with it) but that's about all I can think of.
1 Comment
Rick
I am trying to reduce/merge some temp tables to reduce the count however since every hanging module is different from each one, even If I merge them, the flow of data will be more to the merged table. Right now, my 2500 lines sproc spits out data in 5 seconds. But If the count of records is going to increase, I am worried about the time and performance