All Questions
Tagged with temporary-tables cte
10 questions
2
votes
2
answers
1k
views
CTE vs. temp table for batch deletes
Two-part question here.
We have a large table (between 1-2 million rows) with very frequent DML operations on it. During low volume periods, we have an agent job to remove older rows to keep the ...
- 255
3
votes
1
answer
222
views
Valid for WITH clause to use existing table name?
Suppose I have a table Entry. Is it valid for a WITH clause to replace the Entry table for the duration of a query? I tried with SQLite and MySql and it seems to work, but I want to be sure this is ...
- 133
0
votes
1
answer
3k
views
How to create multiple temp tables using records from a CTE that I need to call multiple times in Postgres plpgsql Procedure?
I am already using a CTE expression within a plpgsql Procedure to grab some Foreign Keys from (1) specific table, we can call it master_table. I created a brand new table, we can call this table ...
- 121
0
votes
1
answer
4k
views
How to rewrite slow CTE construction to match speed of temp tables
I think the general advice of this community is to avoid temp tables in favor of CTEs. However, I sometimes encounter situations in which CTE constructions are very slow, while their temp table ...
- 151
-2
votes
1
answer
104
views
Same query performance problems when using ORDER BY [closed]
I have query that uses a few CTEs. The final output query joins the CTEs together. When I ran the query with no ORDER BY, it runs in seconds. When I ran it with an ORDER BY, it takes several minutes ...
- 149
2
votes
1
answer
923
views
How to use Common Table Expression properly - Example where it seems to be very slow in TSQL
I've been starting using common table expressions more and more and thought I was using them "right" but I've run into an example where the solution with CTE is very slow compared to using a temp ...
- 249
0
votes
1
answer
849
views
Use Common Table Expression (CTE) to reduce locks (max_locks_per_transaction)
I am getting
"ERROR: out of shared memory"
presumably because my plpgsql function drops/creates a lot of temporary tables.
Searching for a solution to the issue, I found this answer that says:
...
- 3,692
1
vote
0
answers
2k
views
PostgreSQL Optimize bulk values CTE and SELECT INTO TEMP TABLE to prevent OOM kills
Recently we moved some code from Rails way to raw SQL for performance reasons. We have some jobs which fetch some data from APIs, data can be in 100K+ rows of count sometimes. This data needs to be ...
- 111
1
vote
1
answer
105
views
Optimize series of queries that recycle the same filter
I have about 30 queries across 50 tables in total which each pull or aggregate information to be later all joined together. The original code provided used entirely temp tables, but it is difficult to ...
- 113
192
votes
7
answers
218k
views
What's the difference between a CTE and a Temp Table?
What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other?
CTE
WITH cte (Column1, Column2, Column3)
AS
(
SELECT Column1, Column2, ...
- 8,537