Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

The tag has no usage guidance.

1
vote
1answer
12 views

mysql - calculate traffic for tmp tables

I'm thinking of placing tmpdir to SSD - to increase speed of working with sorting data in tmp tables on disk. Our base creates a lot temporary tables on disk during sorting, merging, joining an so on. ...
0
votes
1answer
15 views

Dropping rows based on the previous selection statmnet

I have a database with following tables: temp_1, temp_2.... and cached_tbl. Inside cached_tbl I have column table_name and in corresponding rows I store all the temp table names I mentioned above. SO ...
0
votes
1answer
61 views

Truncating TEMP table before insert leads to big amount of logical reads on TEMP TABLE

Setup: We have a complex set of queries, that runs quite often (about 3000 times per hour) for different users. At the start of the process, .NET application creates a set of temp tables during the ...
1
vote
1answer
89 views

Why do LocalTemp table showing with lengthy underscore under Temporary Tables of tempdb in SSMS 2017 Express Edition? [duplicate]

In my Microsoft SQL Server 2017 Express Edition, I have created a local temp table using below create statement, create table #localtemptable (tempid int,tempname varchar(20) ) After creating #table,...
2
votes
0answers
62 views

Reasons for not Globally enabling Trace Flag 2453 - Cardinality on Table Variables

I've been doing some reading on the improvements that Trace Flag 2453 can give on the performance of Table Variables by maintaining statistics and was wondering what would be the reasons you would ...
1
vote
0answers
40 views

Adding field in SELECT cause Using temporary; Using filesort [closed]

I have query with JOIN EXPLAIN SELECT o.id, ostring.services_string FROM offers o LEFT JOIN offers_string ostring ON o.`id` = ostring.`id` WHERE o.`date` >= '2018-09-22 00:00:00' o.`date` <= '...
4
votes
2answers
91 views

Temp Table Filtered Index Creation is Blocked When Stored Procedure is Run Multiple Times Concurrently

Are there any known blocking issues in SQL Server 2014 that are specific to creating one or more filtered indexes on a temp table after the initial table creation? (i.e. not an inline index creation) ...
1
vote
0answers
52 views

Why does the Select statement stalls when executed directly in sequence?

When executing the following in workbench or in code, the Drop table/Create Table works ok. The Select statement stalls when executed in one go. If the table is MyIsam, it works ok. If executed [...
3
votes
1answer
690 views

MS SQL Server: Do multiple queries in a batch ever execute in parallel and if so what happens when the second query is dependent on the first?

If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)? If the first SELECT ...
0
votes
1answer
34 views

How to get the consolidated ServiceID with the sum of File Size associated with over an year

Can anyone please help me on below to get the consolidated ServiceID with the sum of File Size associated with it over an year. Complete Code (For reference) If(OBJECT_ID('tempdb..#temp1') Is Not ...
0
votes
1answer
98 views

Where is `pg_temp` documented in the PG manual?

I have tried various ways to search for pg_temp in the manual, but could not find it. Is it documented in the manual?
0
votes
2answers
70 views

How to get around not being able to use Temp Tables in Functions?

So, to get straight to the point. A Client wants to be able to view all of their staff of a certain position are going to be available to perform a set of shifts between 2 dates. Seems simple, have a ...
0
votes
1answer
128 views

mysql memory temp table creation SLOW

I'm trying to get to the bottom of what I'm thinking is a performance issue when creating in memory temporary tables. The underlying query I have looks to me to be pretty well optimized. The explain ...
0
votes
0answers
133 views

Slow performance using CTE

The query looks something like this ; with stats as ( select * from openquery ([remote-server], 'select empid, stat_dt' from [statistics]') -- when run solo, returns 20K rows in 45 seconds ) , ...
0
votes
0answers
213 views

Create temporary tables in read only transaction - MYSQL 5.7

I am currently assisting with integration testing for migrating an application that currently runs against a mysql 5.5 DB to mysql 5.7. We have run into issues related to functionality that was ...
5
votes
1answer
278 views

Why is it not possible to create indexes on temp tables in SNAPSHOT isolation?

When I try to create indexes on a temporary table in SQL Server when using the SNAPSHOT transaction isolation, I get this error: Transaction failed because this DDL statement is not allowed inside ...
1
vote
2answers
273 views

Can multiple instance of procedure create same #Temp simultaneously?

I have a procedure which is inserting data into #temp table. After performing certain manipulations to the data, it store data from #temp table to some other table. I cannot run multiple instance of ...
3
votes
1answer
86 views

Tempdb size strategy

I've recently started my role as a DBA and my initial task has been to take ownership and redeploy a database provided by a 3rd party to help us conform to GDPR legislation. As a consequence, I've ...
0
votes
0answers
46 views

Temporary tablespace size in 12C

I have installed 12C and importing data through sqlldr , but it increases the temporary tablespace size tremendously which causing the oracle shutdown. Please help and guide me what to do to get rid ...
0
votes
1answer
198 views

How to find index details on Temporary tables in SYBASE

I am a layman in SYBASE world. In order to improve performance, I am creating indexes after data load of temporary tables in many stored procedures. Say, one proc A has an insert into temporary ...
1
vote
3answers
124 views

I ran MySQLTuner and wondered if anyone would go over the results with me, specifically query cache and temporary tables

Here are the results of MySQLTuner: [--] Skipped version check for MySQLTuner script [OK] Logged in using credentials from debian maintenance account. [OK] Currently running supported MySQL version 5....
2
votes
2answers
232 views

Issue while trying to insert the values into a temp table from a query

Please help me on below . I am trying to insert values into a temp table using the sql query. But I am facing below errors . Please help me understand the mistakes. Errors I am facing : Database ...
0
votes
1answer
48 views

New Analyst SQL Optimization - Multiple Spools

Unfortunately my ability to Query has outgrown my knowledge of SQL Optimization, so i am hoping someone would help a young analyst by looking at this atrocious execution plan and provide some wisdom ...
1
vote
1answer
55 views

How to check if a PostgreSQL table is temporary?

How can I write a query to check if a PostgreSQL (10.x) table is a temporary table? I looked at the pg_class system table, but couldn't find any column about whether the relation is temporary. ...
2
votes
0answers
386 views

How to put an execution time limit for MySQL Temporary table creation

We can use SET MAX_EXECUTION_TIME=10; statement to stop the execution of a SELECT statement, if it took more than 10 milliseconds to execute the query. But the same wont work for other statements like ...
3
votes
1answer
64 views

INSERT MASTER.sys.sysdatabases result into a temporary table

I have this query that iterate all databases on my SQL Server DECLARE @DB_Name varchar(100) DECLARE @Command nvarchar(200) DECLARE database_cursor CURSOR FOR SELECT name FROM MASTER.sys....
1
vote
1answer
89 views

DELETE … IN hangs after deleting half of a large table (even for deleting 10 rows)

I'm having trouble with a DELETE hanging, even for a small number of rows (10) in a large MariaDB InnoDB table. Background I had a large table (~400,000,000 rows) that needed to have rows archived ...
2
votes
1answer
259 views

MEMORY_OPTIMIZED table as replacement for temporary tables

I use temporary tables for complex processing which is done in several "child" procedures. These procedures are working in the same session, so the temp tables are accessible for them. Is there a ...
1
vote
1answer
21 views

Find rows where column matches one of n strings [closed]

I am trying to find the best way to see if multiple email addresses already exist in my database. I have a table with member id's, emails, and other info, and I need to import members but to check if ...
3
votes
2answers
133 views

SQL Server Temporary Table Security

I am part of a project to convert an old AS400 system to SQL server. The server is used for our payroll processing. Part of the payroll process is to break out the percentage of the biweekly wages ...
1
vote
0answers
39 views

Why is using a temp table so must faster for this query? [closed]

So my original query looked something like this : select d.d_id,d.name from a inner join b on b.a_id = a.a_id inner join c on c.c_id = b.c_id inner join d on d.d_id = c.d_id inner join e on e.d_id = ...
2
votes
1answer
602 views

How to find MySQL creating temporary table in memory or disk?

I have a query, after running it if i run the show status in the same session like below show status where Variable_name like '%Created_tmp_disk_tables%'; It got incremented by one value. So it ...
1
vote
1answer
104 views

Loop INSERT EXEC AT through linked servers

Relating to Inserting EXEC AT linked server into temp table I am now in the midst of dynamically insert each result of dynamic sql to the temp table for each registered linked server. Below is my ...
1
vote
1answer
2k views

Using INSERT INTO…EXEC AT linked server into temp table fails with Msg 7391

I am using SQL Server 2014. I want to execute EXEC (@remotecmd) AT [server_name]; (@remotecmd is a dynamic sql not a stored procedure) into ##eapb. My code is insert into ##eapb EXEC (@remotecmd) AT [...
0
votes
1answer
45 views

Temp table lives after being explicitly dropped [duplicate]

I'm creating a post-deployment script in VS2017 database project. In this script, I'm using single temp table, which is re-created frequently to adjust to different data structures. Below you can see ...
0
votes
0answers
56 views

Calling SP on linked MsSql server from Oracle - Temp table

I need some advice with the problem mentioned in the title. Specific flow of processing should be in the line of something like this: Pass table (or array, lets say IDs) of data from Oracle to ...
13
votes
1answer
531 views

SQL Server table name starting with # in user database, not in tempdb, not a temp table

Somehow, decades ago, a table got created in our database that starts with a #. It shows up in Object Explorer under the app's database, not in tempdb. For some reason, Azure won't import the database ...
3
votes
2answers
912 views

Postgresql: when temporary tables are deleted in postgresql

I am inserting data into temp table and its working fine. below is the psedo sql code for the same with cte as( ) select * into temp_table from cte with this approach data is inserted very fast ...
0
votes
0answers
88 views

PostgreSQL disk space issue because Temp table size increases with the size of the main table

My table size is of 25 GB and containing 2.5 Million records. one of my requirement is to update all rows of the particular column of this table . updating all rows is taking 8 minutes. In order to ...
0
votes
1answer
19 views

can my SQL Server application code using temporary tables be used for Postgres?

I have a large application that connects to SQL Server and makes extensive use of temporary tables using the #table syntax. I would like to convert this to connect to postgres, which does not accept ...
6
votes
1answer
977 views

Bad execution plan after stats update due to temp table

A stored procedure query sometimes gets a bad plan after stats update on one of the tables, but can be recompiled to the good plan right after. Same compiled parameters. Issue seems to come from a ...
12
votes
1answer
2k views

Why does truncating a temp table at the end of the stored procedure that creates it free tempdb space faster?

SQL Server caches temp tables created within stored procedures and merely renames them when the procedure ends and is subsequently executed. My question has to do with when the tempdb space is ...
6
votes
2answers
466 views

Can SQL Server Always Encrypted be used for temporary tables?

I'm trying to determine if I can use Always Encrypted on a column in a temporary table (SQL Server 2016 SP1 Enterprise on Windows 2016). I've tried as follows: CREATE TABLE #listofdates (compare_dt ...
1
vote
1answer
50 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 ...
0
votes
1answer
196 views

Postgresql using LEFT JOIN and RIGHT JOIN at the same time

I have two temporary tables that I have created using tampa, tampb clauses in PostgreSQL. I want to be able to get result when data from either side is missing (company1 or company2) but not both. I ...
-1
votes
1answer
115 views

Query Using Common Table Expression Takes a long time [closed]

I have a complex query with partitioning and plan to store the results into Common Table Expression. And then at the end of the query I look into that CTE to find specific rows that only contain ...
2
votes
1answer
443 views

Make a temporary table visible for a function inside a function (or a subfunction)

I have two functions in PL/pgSQL: The function 1 calls function 2, and function 2 needs to process a temporary table created on function 1, in another words, this temporary table needs to be global ...
2
votes
3answers
495 views

Can one have Temporary Rows?

I understand that Temporary Tables in Postgres and MS SQL Server persist until the end of the session. When the user’s session ends, any temp tables created by that user are dropped automatically. The ...