Questions tagged [temporary-tables]

The use of temporary table objects (not table variables) including semantics, design, performance optimization, and indexing. See also [table-variable].

Filter by
Sorted by
Tagged with
0 votes
0 answers
14 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 ...
-1 votes
1 answer
63 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 ...
  • 26.7k
0 votes
1 answer
47 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
122 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,509
0 votes
1 answer
61 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
41 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
308 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 ...
0 votes
1 answer
561 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 ...
  • 111
0 votes
1 answer
372 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,387
3 votes
3 answers
716 views

Reducing Temp Table Scans when joining two temp tables using OR conditions

I'm working on a complicated query that 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 ...
9 votes
2 answers
544 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
817 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
508 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
53 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
295 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
1k 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 ...
2 votes
5 answers
4k 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 ...
  • 33
1 vote
3 answers
1k 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 ...
0 votes
0 answers
254 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
21 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
254 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,742
1 vote
0 answers
837 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
1k 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
815 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 ...
  • 123
1 vote
2 answers
740 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 ...
0 votes
1 answer
326 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
81 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 ...
1 vote
1 answer
210 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
171 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....
0 votes
1 answer
130 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(...
0 votes
1 answer
803 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
106 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
216 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
121 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
41 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 ...
  • 155
2 votes
1 answer
295 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
2k 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
2 votes
1 answer
3k views

Sql Server string concat with order by and subquery

While working on some dynamic sql production code we encountered code that's concatenating a bunch of strings and a subquery and then more string concatenation. The goal of the code is to populate a ...
0 votes
2 answers
222 views

Are key lookups from non-clustered indexes always slower than a second query that does the lookup?

I've noticed in my system, whenever a non-clustered index is used in a query that has to also do a key lookup to get the additional fields being selected, it's faster for me to instead do two queries. ...
  • 26.7k
36 votes
2 answers
6k views

Is it generally faster to select into a temp table than selecting into an actual table?

I thought I once read somewhere that writing to tempdb is faster than an actual table not in tempdb. Is this true in any capacity? I thought I recall it saying something special about tempdb and ...
  • 26.7k
0 votes
0 answers
15 views

Does the Query Optimizer have the right to create Temporary (Materialized) Views if it enhances performance?

Does the Query Optimizer have the right to create Temporary (Materialized) Views which can enhance performance? Sorry if this question seem very trivial. Or in other words, does the query optimizer ...
  • 1
5 votes
1 answer
2k views

Is there an easy way to SELECT FROM another session's temp table?

Is there an easy way to select the data from another session's temp table? I need to do so ad-hocly to debug an issue that I have limited visibility on otherwise. E.g. if I have access to TEMPDB, is ...
  • 26.7k
5 votes
1 answer
1k views

If sp_ExecuteSql creates a new session, how come I can access a local temp table created (prior to it's execution) outside of the dynamic SQL?

If local temp tables are only available to the current session, and sp_ExecuteSql creates a new session to execute the dynamic SQL string passed into it, how can that dynamic SQL query access a temp ...
  • 26.7k
0 votes
2 answers
1k views

msdb.dbo.sp_send_dbmail temp table does not exist

I have created a temp table and would like to email the results I am getting the following error. Query execution failed: Msg 208, Level 16, State 1, Server PRODYCHDBX3\X3V7, Line 1 Invalid ...
  • 3
1 vote
1 answer
94 views

Unable to Insert Casted XML Data into New Column in Temporary Table

Question How can I insert CAST(event_data as XML) data into a newly added column in a temporary table with the following statement: INSERT INTO #Unique_Logins (event_data_xml) SELECT CAST(...
  • 15.5k
1 vote
1 answer
1k views

Using WITH together with CREATE TEMPORARY TABLE AS SELECT ... in Postgres?

I would like to create a temporary table using a select statement that uses temporary views specified in a WITH statement (not the WITH statement of CREATE TEMPORARY TABLE). e.g. something along the ...
  • 113
1 vote
2 answers
182 views

Replace function removing Ones / 1 when removing char(185) (superscript 1)

Ok as the title suggests this is occuring. When using the REPLACE function on char(185) for data in a temporary table, ones ("1") are being removed as well. SQL SERVER 2008R2 UNICODE Data DB ...
1 vote
2 answers
704 views

How to split a table in SQL Server into two different related tables inserting foreign key?

I have a table OriginTable with the following structure EventID | Date | Value1 | Value2 | Value3 ----------------------------------------------------------- 5 | 2019-10-07 | ...
1 vote
2 answers
1k views

SQL Server - global temporary table vs normal table

I have a stored procedure which I use to load csv files to SQL Server. This will initially bulk insert the file into a global temporary table then at the end it will do: select cast(column1 as int) [...
  • 135

1
2 3 4 5 6