Questions tagged [temporary-tables]
The use of temporary table objects (not table variables) including semantics, design, performance optimization, and indexing. See also [table-variable].
282
questions
-1
votes
0
answers
10
views
Ways to create database in oracle XE
I am quite new to the database topic. I have already installed Oracle XE. The questions are
Whether I am going to set up the database as well as the initial creation of tables by using oracles DBCA (...
- 1
0
votes
0
answers
35
views
Temporary rows in static table in SQL Server
I'm looking for a way to insert temporary rows in a static table. These rows are temporary because are used only in processing.
The reasons are:
I wish not to work in transaction because it's a huge ...
- 113
2
votes
0
answers
37
views
MySQL release memory allocated for a temp table
I'm using 5.6 MySQL database to insert tens of millions records between multiple tables.
Before the insertion I send all my data into a temp memory table, for that reason I have increased both ...
- 121
0
votes
0
answers
40
views
Is a dbo.SORT table in a user database confined to only 1 data file?
A user thought he was exhausting tempdb because of this error:
'Could not allocate space for object 'dbo.SORT temporary run storage: 422291993526272' in database '<USER_DB>' because the '...
WillemG
-2
votes
1
answer
186
views
What is the correct way to check and delete temp table?
Approach 1:
IF OBJECT_ID('tempdb..#MyTempTbl') IS NOT NULL
DROP TABLE #MyTempTbl;
Approach 2:
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
WHERE [name] = N'#MyTempTbl')
DROP TABLE [#...
- 3,153
1
vote
0
answers
64
views
Azure SQL Error "Invalid object name '##{myTempTableName}'
I am getting an error in my stored procedure: "Invalid object name '##{myTempTableName}' for 3 global temporary tables. There are several temp tables used in the procedure, but I am getting this ...
- 11
0
votes
1
answer
39
views
How does a serializable transaction level affect global temporary tables in SQL Server? Creating table affects it?
I have a knowledge gap in SQL Server 2019 and if someone could explain it to me how the following works appreciate it. This will be a long post as I want to explain the full context as best I can ...
0
votes
1
answer
33
views
Solutions for fixing stored procedures that rely heavily on global temporary tables?
I am working on re-writing some stored procedures. The last issue was an abuse of EXECUTE AS USER='someUser' to use the default schema and work against those.
The second thing I just noticed though ...
0
votes
1
answer
89
views
How does PostgreSQL resolve pg_temp?
pg_temp is an alias for current session's temporary-table schema.
Likewise, the current session's temporary-table schema, pg_temp_nnn, is always searched if it exists. It can be explicitly listed in ...
- 101
3
votes
1
answer
150
views
Invalid Object Name after Creating a Table using a while loop
I created this loop to create and populate a temporary table for each int representing a day. The tables are created and properly executed but i get Invalid Object Name everytime i try to print a ...
- 31
2
votes
2
answers
307
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 ...
- 253
3
votes
1
answer
208
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
0
answers
28
views
MySQL: Querying WHERE IN a UNIONed selection of ids is slow, but creating and querying against a temp table with that same UNIONed selection is quick
Running this query is instant (and results in 2 rows)
SELECT model_id as id FROM jobs
WHERE jobs.batch_id IN (999)
AND jobs.model_type = 'App\\Project'
UNION ...
- 123
-1
votes
1
answer
99
views
When DML and DDL changes occur on a Temporal Table do the correlating changes in the History table get logged in the Transaction Log as well?
When DML and DDL queries are executed against a Temporal Table, those changes are appropriately propagated to the correlating History table. E.g. an UPDATE to the Temporal Table results in a new ...
- 30.4k
0
votes
1
answer
192
views
Teradata: Temporary Table vs Create and Drop Table
Currently I have seen an ETL process where a user created tables that are used to store an intermediate result and then drop the tables at the end of the ETL process.
At first glance it seemed to me ...
2
votes
2
answers
500
views
UPDATE FROM with a large table is slow and uses Seq Scans
I have a large table (ultimately maybe a billion rows but currently ~26 million) for which I want to set a flag on the highest PK for a given grouping, in a one-off batch.
I chose to create a ...
- 1,559
0
votes
1
answer
63
views
When Insert Press Name Tech Id Take Wrong Number Where Press Name Exist?
I work on SQL server 2017 I have table #Pressfeature as below
create table #Pressfeature
(
PartId int,
PressName varchar(300),
TechId int
)
insert into #Pressfeature(PartId,PressName,...
2
votes
1
answer
59
views
Alter the collation of a column in temp-file
I have a routine in a DB that uses a temp-file. But the collation of the DB doesn't match the collation of the temp-DB. I can't change the collation of the DB, it's a client DB.
I tried this:
BEGIN
...
- 21
3
votes
2
answers
401
views
How can Temp Table creation time be reduced in terms of CPU time?
Inside a frequently executed stored procedure (sometimes up to 10-15 times per second), there are two particular statements that show up in Top 10 heaviest queries by cumulative CPU impact
These are ...
- 5,109
0
votes
1
answer
1k
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
669
views
Automatically materialize CTE? (instead of temporary table CREATE and INSERT)
I’m a novice trying to learn about query optimization and temporary tables in Oracle.
The examples I’ve seen for Oracle temporary tables involve CREATE TABLE and INSERT INTO statements.
CREATE ...
- 1,417
3
votes
3
answers
1k
views
Reducing Temp Table Scans when joining two temp tables using OR conditions
I'm working on a complicated query. I have up to this point been able to refactor to reduce execution time as well as number of scans and reads. At this point in the query, we have two temp tables ...
- 75
9
votes
2
answers
617
views
How does the optimizer treat yet-unpopulated #temp tables when estimating cardinality?
I've been building stored procedures and can't find any documentation on how the SQL Server optimizer handles #temp tables.
If I have 4 joins to a #temp table where that #temp table is created DURING ...
1
vote
1
answer
2k
views
There is already an object named PK_MyPrimaryKey in the database ( primary index on temp table)
Simplified code
var sql = @" create table #Temp(
int Id NOT NULL
CONSTRAINT [PK_TempId] PRIMARY KEY (Id)
)
insert into #temp(id) select (...
- 177
0
votes
2
answers
857
views
ORA-01652 - unable to extend TEMP segment?
Having an issue that I can't seem to find any solutions to online.
[72000][1652] ORA-01652: unable to extend temp segment by 256 in tablespace TEMP Position: 581
One of our users runs a query ...
- 11
0
votes
1
answer
67
views
Sql Server consuming lot of memory Creating Bottleneck for my web application Hosted in the same server
HI I have a server with 64 GB Ram and I have assigned around 52GB to the SQl Server itself. However Even this has effected the performance of the server and the web application hosted in the same ...
1
vote
1
answer
450
views
MySQL indexing on 11 million rows
I have a development and production server, both with different amounts of RAM. Development machine has 8G, while production has 64G.
I'm importing a large table from development to production, but ...
- 111
0
votes
1
answer
2k
views
Create an Azure Synapse temp table from a SELECT with ORDER BY
I have the following statement that works when I omit the ORDER BY clause, or if I run it as a select and omit the create table part, but I need both to ensure my generated key is sequenced correctly
...
- 67
2
votes
5
answers
6k
views
Temporary table is full
The table '/mnt/disks/data/tmp/#sql58de_a0f6_7' is full
The query is quite complex/big. Lots of joins in joins and unions, sorting, grouping, ...
1 out of 10 the result is returned. Otherwise it ...
- 133
2
votes
3
answers
2k
views
Should I use a temp table or join
I am creating a stored procedure in sql server 2019 that needs to use multiple select statements to get a parent row and then its related data. I have the primary key clustered value for the parent so ...
- 140
0
votes
0
answers
305
views
Is Temporary table supported in Hive
I run queries in Hive...I wanted to create a temporary tables to save intermediate results of a complex query. Here is my query:
CREATE TEMPORARY TABLE test AS
SELECT * FROM my_table
LIMIT 100
The ...
1
vote
1
answer
22
views
Does MySQL support defining a table inside a session without appending it to the database?
As per stated in the title. Does MySQL support defining locally a table? (as a 'local variable' perhaps?)
I understand (according to 'Source') that MySQL has 'two types of variables': (1) local and (2)...
- 113
10
votes
1
answer
330
views
Why using a local temp table (instead of a global temp table or a regular table) influences the Query Optimizer to choose a poor query plan?
This Question brings a situation where the Query Optimizer chooses poorly the seek predicate among the existing predicates of a simple query. After running some tests I got to the conclusion that the ...
- 4,953
1
vote
0
answers
1k
views
MariaDB 10.3.27 - 100% Temporary tables created on disk despite/because of InnoDB?
I am using Mariadb 10.3 with InnoDB with a fairly high load. For some reason, I have 100% of my temporary tables created on disk, despite using Innodb and a large tmp_table_size (2G) (see Mysqltuner....
- 11
0
votes
2
answers
2k
views
How to avoid creating and dropping temp tables in functions?
I have been creating some functions lately that run into the "out of shared memory" warning in postgres. I have tried to increase the max_locks_per_transaction, but it still occurs.
As far ...
0
votes
1
answer
1k
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 ...
- 141
1
vote
2
answers
1k
views
Unknown table 'table' in field list on referencing temporary table inside a trigger
I am creating a trigger BEFORE INSERT that checks a record from another table to see if it exists, otherwise signal sqlstate. The trigger definition shown below
DELIMITER //
DROP TRIGGER IF EXISTS ...
- 13
0
votes
1
answer
518
views
Global Temp Table Error when Stored Proc run by SQL Job on a Schedule
I have a puzzling situation I am trying to confirm/solve.
A stored procedure I have written creates a series of Global Temp Tables and fires off an email if any records show up in the final one. This ...
1
vote
1
answer
114
views
See content of temporary Table in Oracles' EXPLAIN
I'm executing a Query in Oracle SQL Developer and eventually call
SELECT * FROM TABLE ( dbms_xplan.display );
to get an impression what Oracle just did. There's a column Name in the output that ...
- 197
1
vote
1
answer
523
views
Unable to save a stored procedure which contains a temporary table creation and update query
I am unable to save my procedure in Postgres. The procedure contains a temporary table creation and update query.
Here is my query:
CREATE OR REPLACE PROCEDURE account_report_v2()
LANGUAGE SQL
AS $$
...
- 111
-2
votes
1
answer
52
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
0
votes
1
answer
228
views
MariaDB: Why is this still using temporary and filesort - is it because I'm using a self-join? How to fix?
SELECT e2.personId, COUNT(*)
FROM entries e1
JOIN entries e2 ON e2.categoryId = e1.categoryId
AND e2.personId != e1.personId
WHERE e1....
- 225
0
votes
1
answer
165
views
HeidiSQL rounds down double values upon insert...select
I have the following table:
CREATE TEMPORARY TABLE `new_aps` (
`MinuteStart` datetime NOT NULL,
`ProxyListID` bigint(20) NOT NULL,
`SuccessCount` double(22,0) NOT NULL,
`UnknownCount` double(...
- 115
0
votes
1
answer
977
views
Create table using select statement Truncated incorrect datetime value:
I have looked up different answers on similar question before posting it here.
So i have a table with user id and date column
I would like to store the results of select query to a temporary table.
...
- 3
0
votes
1
answer
164
views
Querying an enormous table (~150 million rows) with multiple joins - SQL Server
First off, I don't have SHOWPLAN permission on this server or permission to add indexes, etc. I am not a database administrator by trade; I'm just trying to query a database. There is already indexes ...
1
vote
1
answer
284
views
How do you minimize chance of collision between system-named constraints on temp tables?
We have occasionally hit the error “There is already an object named 'PK__#TempTab__0796211ACE71813B' in the database” when some of our procedures attempt to create temp tables with a system-named PK ...
- 143
0
votes
2
answers
157
views
Dynamic auditting trigger without temporary tables
We currently have a trigger set up on a table to audit any data changes similar to this answer, which uses dynamic SQL to generate the audit rows. In order to use the INSERTED and DELETED tables in ...
- 103
0
votes
0
answers
66
views
Why does UNION fail here?
I have a problem that I can't seem to figure out how to fix.
What I want to do is simple, select data and append a row of sums as the last row. But I keep getting the error
An error occurred in ...
- 165
2
votes
1
answer
387
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
-1
votes
1
answer
3k
views
Use a temp table's result in a view [closed]
I am using a temp table in a stored procedure.
I need to use that temp table's result in my view.
How can I do that?
- 11