Questions tagged [temporary-tables]
The use of temporary table objects (not table variables) including semantics, design, performance optimization, and indexing. See also [table-variable].
301 questions
0
votes
0
answers
38
views
Kitchen Sink Pagination - How to avoid recompiles or bad estimates when materializing intermediate result sets (temp table vs table variable vs CTE)?
I have a high-traffic SQL Server stored procedure that runs thousands of times per hour. The procedure loads a filtered set of “projects” for a user, identifies the “active task” for each project, ...
- 821
0
votes
0
answers
85
views
Temp Table Management in Explicit Transactions and CATCH blocks
Example code below populates a PhoneNumber table and associates those PhoneNumberId's with an OrderNumber.
As written I am getting the error message:
The current transaction cannot be committed and ...
- 1,096
3
votes
1
answer
130
views
Postgres Temp table performance
We are converting a database from SQL Server 2022 to Postgres 18.
Our system has a lot of code in the database and a lot of queries that use local temp tables a lot more than CTE's. This is due to the ...
- 31
2
votes
1
answer
127
views
Slow queries on JSON_TABLE based view on mariaDB
We a running an instance of Apache syncope with around 130k users (ca. 33k with active or pending state). When searching for users by attributes we see long query times often over 50 seconds. One ...
- 121
7
votes
1
answer
298
views
SQL Server deadlock on temp tables in different connections
I'm getting occasional deadlocks on temp tables in two different connections (at least as far as I can tell, the deadlock graph shows different spids). The deadlock graph (lightly redacted, and with ...
- 171
1
vote
1
answer
134
views
Is pg_attribute bloat from temp table creation still a potential issue in modern versions of PostgreSQL?
I am not an expert in PostgreSQL, however I recently ran across some PostgreSQL code during a code review which creates and drops temporary tables in a manner that seems consistent with the typical ...
- 71.1k
0
votes
0
answers
37
views
Is there a limit to how many temporary tables MySQL can handle?
I manage a web service. The service has multiple endpoints, and most of these endpoints simply return some data representing an entity (such as a track, genre or composer). Each endpoint usually ...
- 193
-1
votes
1
answer
181
views
OBJECT_ID fails to find existing temp table [closed]
I'm using temp tables to stage data before inserting into production. As a couple of things use them, before anything else I do a:
IF OBJECT_ID(N'##Projects_TEMP') IS NOT NULL
TRUNCATE TABLE ##...
- 230
15
votes
1
answer
771
views
What could cause a SQL Server non-cached temporary table to NOT trigger a recompile when a large amount of rows have changed?
I have observed (and reproduced) the following scenario with SQL Server 2022.
The pattern in use
code is executed via sp_executesql (no stored procedure is involved)
The first query selects data into ...
- 958
0
votes
0
answers
49
views
Temporary relation files not removed after sessions ends - Single PostgreSQL instance PostgreSQL 13
We are facing default temporary tablespace disk space full issue due to lots of below temporary relations files created but not removed after ending session
1.1G t756_1536213694.409
1.1G ...
- 143
2
votes
1
answer
198
views
What kinds of operations could cause a "Temp table changed" recompilation?
Frequent recompilation happens in one SP with 'Temp table changed' cause. What kinds of operations on temp table would trigger this? There are no alter and create index operations on temp table, just ...
- 43
6
votes
1
answer
1k
views
How does sp_executesql interact with temporary tables?
I'm at a loss as to why some SQL leveraging sp_executesql is working the way it is when temporary tables are involved, along with INSERT and SELECT - INTO. I'm using SQL Server.
Here is the code in ...
- 171
0
votes
1
answer
232
views
DFT drops connection and its temporary table after leaving any DFT item, tested with two Script Components. How do I keep the temp table alive?
Links
This takes up:
Using temporary tables in SSIS flow fails - Stack Overflow
Is it possible to use a temp table in a data flow source?; answer is:
'No', since it cannot be passed without being ...
- 366
0
votes
2
answers
2k
views
How do I create and fill a temporary table with incoming data from a Data Source item without leaving the SSIS Data Flow Task C# Script Component?
I came to ask this question after coding in a script component for some time, testing mainly how to loop over columns of the Row object, see Looping Through Columns in SSIS Script Component - Stack ...
- 366
0
votes
0
answers
62
views
Do Primary Keys On Table Variables Have No Statistics At All? [duplicate]
My confusion is down to an apparent contradiction in principles. Primary keys are indexes and those always have statistics, but table variables notoriously always don't have statistics. What follows ...
- 1,362