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
2 votes
1 answer
38 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 ...
user avatar
  • 21
3 votes
2 answers
284 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 ...
user avatar
0 votes
1 answer
169 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 ...
user avatar
  • 111
0 votes
1 answer
208 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 ...
user avatar
  • 1,373
3 votes
3 answers
389 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 ...
user avatar
9 votes
2 answers
504 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 ...
user avatar
1 vote
1 answer
100 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 (...
user avatar
  • 167
0 votes
2 answers
298 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 ...
user avatar
  • 11
0 votes
1 answer
37 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 ...
user avatar
1 vote
1 answer
220 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 ...
user avatar
  • 111
0 votes
1 answer
898 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 ...
user avatar
2 votes
5 answers
3k 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 ...
user avatar
  • 33
1 vote
2 answers
625 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 ...
user avatar
0 votes
0 answers
194 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 ...
user avatar
1 vote
1 answer
20 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)...
user avatar
  • 113
10 votes
1 answer
228 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 ...
user avatar
  • 4,384
1 vote
0 answers
612 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....
user avatar
  • 11
0 votes
2 answers
700 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 ...
user avatar
0 votes
1 answer
444 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 ...
user avatar
  • 123
1 vote
2 answers
601 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 ...
user avatar
0 votes
1 answer
202 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 ...
user avatar
1 vote
1 answer
67 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 ...
user avatar
1 vote
1 answer
47 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 $$ ...
user avatar
  • 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 ...
user avatar
  • 149
0 votes
1 answer
113 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....
user avatar
0 votes
1 answer
112 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(...
user avatar
0 votes
1 answer
672 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. ...
user avatar
  • 3
0 votes
1 answer
63 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 ...
user avatar
1 vote
1 answer
178 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 ...
user avatar
  • 143
0 votes
2 answers
94 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 ...
user avatar
  • 103
0 votes
0 answers
35 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 ...
user avatar
  • 155
2 votes
1 answer
191 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 ...
user avatar
  • 249
-1 votes
1 answer
962 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?
user avatar
  • 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 ...
user avatar
0 votes
2 answers
169 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. ...
user avatar
  • 24.2k
36 votes
2 answers
5k 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 ...
user avatar
  • 24.2k
0 votes
0 answers
13 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 ...
user avatar
  • 1
5 votes
1 answer
1k 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 ...
user avatar
  • 24.2k
5 votes
1 answer
956 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 ...
user avatar
  • 24.2k
0 votes
2 answers
917 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 ...
user avatar
  • 3
1 vote
1 answer
73 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(...
user avatar
  • 14.9k
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 ...
user avatar
  • 113
1 vote
2 answers
172 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 ...
user avatar
1 vote
2 answers
505 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 | ...
user avatar
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) [...
user avatar
  • 135
0 votes
1 answer
1k views

How to solve a mail issue Failed to initialize sqlcmd library with error number -2147467259?

I got an error while trying to send an email: Msg 22050, Level 16, State 1, Line 0 Failed to initialize sqlcmd library with error number -2147467259. Here is my query: DECLARE @Table TABLE ( ...
user avatar
  • 1,163
1 vote
1 answer
246 views

Postgres long running query not blocking table operations

We have a 3rd party service that imports data into a local postgres instance, that we then query/copy into our own view-data system. These queries can yield millions of rows, so the import of them to ...
user avatar
  • 177
0 votes
1 answer
261 views

Aurora postgres grant permission to pg_attribute

In the process of migration from local postgres version to and RDS aurora postgres version. I'm using temporal tables and do for that reason have temporal tables that are inhirited from the origin. ...
user avatar
0 votes
1 answer
2k views

insert results from store procedure with parameters into table

I have a stored procedure that I want to insert into a table, the stored procedure however has parameters I have to pass, how can I do this? DECLARE @RC int DECLARE @startweek varchar(20) DECLARE @...
user avatar
  • 103
1 vote
0 answers
76 views

How to avoid running multiple selects with the same subquery join?

I am using MySQL 5.7.26. I have 12 SELECT queries which run the same subquery join; since the subquery in a join is very complex (can be more complex, it is a filter query in a web app) I want to ...
user avatar
  • 45

1
2 3 4 5 6