The tag has no wiki summary.

learn more… | top users | synonyms

188
votes
4answers
32k views

What's the difference between a temp table and table variable in SQL Server?

This seems to be an area with quite a few myths and conflicting views. So what is the difference between a table variable and a local temporary table in SQL Server?
18
votes
1answer
3k views

Why is using a table variable more than twice as fast as a #temp table in this specific case?

I was looking at the article here Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for ...
14
votes
2answers
644 views

Does varchar size matter in temporary tables?

There is a debate at my wife's work about just using varchar(255) for all varchar fields in temporary tables in stored procedures. Basically, one camp wants to use 255 because it will always work ...
8
votes
2answers
3k views

INSERT performance difference between temporary tables and table variable

I have the following issue in SQL Server 2005: trying to inserts some rows into a table variable takes a lot of time compared to the same insert using a temporary table. This is the code to insert ...
6
votes
3answers
734 views

Why is @temp table performance some times worse than #temp table performance?

I was recently working in a very slow stored procedure (took 5 minutes to run). I made a very small tweak from doing this: declare @tempTable table ( ... ) insert into @tempTable select ..... to ...
6
votes
1answer
617 views

Guarantee SQL Server Identity Columns that two consecutive values differ by constant increments?

I posed a question about Denali sequences here and another question about emulation of identity columns in Oracle Global Tables here. I always thought that I could trust that identity values ...
5
votes
2answers
109 views

Is this temp table behaviour documented?

Query Language (SQL) and PL/pgSQL functions treat temp tables differently: begin; create table foo(id) as values (1); select * from foo; /* id ---- 1 */ savepoint s; create function f() returns ...
5
votes
1answer
424 views

Dealing with temp tables when I have not control over db variables

I have no control over things like tmp_table_size and max_heap_table_size, and so as our tables grow the time taken by queries requiring temp tables is growing geometrically. I am wondering if there ...
4
votes
1answer
5k views

temporary table inside procedure oracle

I'm in a situation right now. I'm migrating several procedures from Mysql 5.0 to Oracle 11g. Mysql procedures allow me to: 1. Create temporary tables 2. Insert data into temporary tables / Query these ...
4
votes
2answers
1k views

Is it bad to create many mysql temporary tables simultaneously?

I need to make heavy statistical analysis to deliver some data to users. Currently, I catch the data from mysql and process them via PHP arrays. However, mysql temporary tables are quite better ...
3
votes
2answers
202 views

Are there advantages to using temporary tables over derived tables in SQL Server?

I read the derived tables have better performance than temporary tables, but anyway many SQL Server developers prefer the second ones. Why? I must do queries with large data (millions records) and I ...
3
votes
2answers
4k views

MySQL creates temporary tables on disk. How do I stop it?

We are running a site (Moodle) that the users currently find slow. I think I have tracked down the problem to MySQL creating temporary tables on disk. I watch the variable created_tmp_disk_tables in ...
2
votes
2answers
391 views

What reason is there to create a physical table for a report output?

I've noticed that for Crystal Reports made by our organization and by some of our ERA software providers have a tendency to use physical tables for their reports' data sets, rather than using a view ...
2
votes
1answer
1k views

How to get a list of global temporary tables in the current database?

I am trying to get a list of the global temporary tables that are currently active. There seems to be an issue with them bumping in to each other (same name). As the 'acting' dba, trying to sort out a ...
2
votes
2answers
2k views

mysql 5.5 corrupt - missing temporary table

One of our mysql instances crashed and failed to start: 120422 10:04:13 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './databasename/#sql-29f6_45.ibd'! ...
2
votes
1answer
919 views

Is there a MySQL engine or trick to avoid writing so many temp tables to disk?

I've got a web application connected to a database running MySQL. Here's the problem: a not insignificant number of queries create temporary tables and many of the tables involved have BLOB fields. ...
2
votes
1answer
9k views

MySQL: Execute CREATE TEMPORARY TABLE SELECT just one time?

I want to create a temporary table with some select-data. My (strange) problem is that I have to do the same query more than one time. CREATE TEMPORARY TABLE IF NOT EXISTS cache (id int(11) NOT ...
2
votes
1answer
2k views

Repairing Myisam Table when there was no additional Disk space & Table corrupted

I had a table which MyIsam as storage Engine contains million rows. There is a purging task and need to remove 35 days old data. i measured up the rows in following manner : mysql> select ...
2
votes
1answer
539 views

What are all the reasons/possibilities that may fail to release disk space occupied by TEMPORARY table?

We're using PostgreSQL v8.2.3. Ours is a web-based application and we're using pgpool-II v 2.0.1 purely for connection pooling (we don't use other features of pgpool like Replication, Load Balancing, ...
2
votes
2answers
411 views

changing TEMPORARY TABLE default collation

when I use CREATE TEMPORARY TABLE to create a temporary table, the collation of this table is utf8_unicode_ci by default. why by default it is utf8_unicode_ci? How can I change the default?
2
votes
0answers
305 views

Why are runtimes different for a table including a primary key versus a table with a clustered unique index added after population [closed]

When I create a temporary table with a PK defined from the outset like this I get great performance in subsequent joins on that key: create table #temp ( field1 int not null field2 int ...
1
vote
2answers
132 views

SQL 2008 RTM Temp Tables

Today I implemented a monitoring solution on our SQL server using Cacti and the first thing i noticed is the growth in the number of temporary tables, in just 3 hours on our test system we had an ...
1
vote
3answers
4k views

Why does MySQL produce so many temporary MYD files?

On a Debian Linux server, hosting many PHP/MySQL websites (photo galleries), sometimes I have "many" files like /tmp/#sql_6405_58.MYD. For example today : [2012-12-15 15:18:11] /tmp/#sql_6405_6.MYD ...
1
vote
2answers
284 views

Should I load the data into Temp table or keep calling it from the normal table? [closed]

I am writing a procedure and find myself using the same select statement with the same where clause a lot. Currently the table holds over 55000 rows and grows about 100 or so rows each day. The two ...
1
vote
2answers
253 views

if block fails to create temp table in a procedure

I'm attempting to do this in a procedure: DECLARE @a bit = 1; BEGIN SELECT * INTO #aTemp FROM OPENROWSET( ... ); IF @a = 0 BEGIN SELECT ... INTO #bTemp FROM #aTemp; END ...
1
vote
2answers
300 views

Size of TempDB Microsoft SQL Server 2005

Are there drawbacks to having a large tempdb in a SQL Server database? Assuming the disk is not full. I am creating large temporary tables in a couple of queries. Once I am finished running queries, ...
1
vote
2answers
164 views

What is the size of currently-existing tmp tables in innodb?

This is yet another "how to add a column to a gigantic table" question. The table on disk takes 2.5GB, and I have around 10GB of ram I can use. I have a feeling I can crank up the tmptablesize option ...
1
vote
3answers
2k views

ON DUPLICATE KEY UPDATE question

I'm trying to compare data from two systems. The basic idea is: Create Temp Table. Insert Table1 Data. Insert Table2 data (Table a linked, remote server) with UNIQUE Ids respected. Filter where ...
1
vote
1answer
51 views

Oracle temporary table usage with XA

I'm confused about the compatibility of Oracle XA with global temporary tables. I've read several places on the internet and in the oracle documentation that say "Distributed transactions are not ...
1
vote
1answer
54 views

MySQL, temporary on disk table on primary key retrieval?

MySQL 5.5.24, Windows 7 64-bit. I've InnoDB table with primary key defined on three columns: CREATE TABLE `common_descriptions` ( `object_id` mediumint(8) unsigned NOT NULL DEFAULT '0', ...
1
vote
2answers
147 views

How long will a temporary MEMORY table persist if I don't drop it (MySQL)

I'm using a recursive stored procedure in MySQL to generate a temporary table called id_list, but I must use the results of that procedure in a follow up select query, so I can't DROP the temporary ...
1
vote
1answer
69 views

Can I remove #sql_ .MYD files?

I found some old files in the mysql datadir: -rw-rw---- 1 mysql mysql 0 2012-10-09 09:39 #sql_263c_0.MYD -rw-rw---- 1 mysql mysql 1.0K 2012-10-09 09:39 #sql_263c_0.MYI -rw-rw---- 1 mysql ...
1
vote
1answer
138 views

Binding errors with dependent stored procedures

We are getting strange errors on one of our stored procedures. This stored procedure calls other stored procedures & we are getting errors relating to columns that cannot be found in the sub ...
1
vote
1answer
524 views

How to handle temporary tables with mutliple connections?

In Is there a way to hint to query optimizer to mysql which contraints should be done first? I created a subquery. The subquery is often not optimized. I think a better way is to create a temporary ...
1
vote
1answer
40 views

MySQL: does Index Merge Sort-Union access algorithm use temporary table?

My question is about index_merge optimization in MySQL. I have a query with a set of range conditions connected with OR. EXPLAIN of the query shows that index_merge method is used and the Extra ...
1
vote
1answer
102 views

Should I use table hints with a process-keyed table?

I read on stackoverflow that locking can be a problem when using process-keyed tables, and I was wondering if you had any suggestions on how to minimize or avoid unnecessary contention (e.g. table ...
1
vote
0answers
33 views

Using #temp to work around Reporting Services blocking issue

Having utterly failed to resolve why a perfectly acceptable but admitidly large stored procedure was suffering the blocking process lck_m_s when called via a report in SQL Reporting Services I have ...
1
vote
0answers
22 views

Inside stored procedure, set column names of returned query according to variable

I have a query which I'd like to reuse, instead of having many different copies of it just for the sake of changing the column name. The report (for example) uses the same query, but there are either ...
1
vote
0answers
79 views

Why is this query creating 300+ temporary tables in Mysql?

SHOW global STATUS LIKE '%tmp%'; set @UID = 132; set @param2 = 'cde'; set @param3 = 20130331; set @param4 = 'C'; select p.id, p.column44, p.column42, ue.column35, u.id, p.column43, p.column45, ...
1
vote
0answers
131 views

TMD auto create

I used mysql as part of a project and found it to be a very practical tool. I have a question about mysql in regards to how it creates temporary table types TMD in /var/lib/mysql/. This partition ...
1
vote
0answers
61 views

Map records to a time interval

I have two tables: SamplesTbl contains samples every 5 seconds of a bike ride RxTbl contains the prescribed stages and other values of a bike ride SamplesTbl SamplesID int SessionNum int StageNum ...
1
vote
1answer
77 views

How big is a tmp table?

I've got an RDF table in MySQL 5.1 with the MyISAM engine. Here's the table definition: CREATE TABLE `node` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `graph` varchar(100) CHARACTER SET latin1 ...
0
votes
2answers
570 views

Can I copy the table structure of a #temp table to a new physical table?

I have a #tempTable that was created using SELECT * INTO #tempTable FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;Database=MyFileName.xls', 'SELECT * FROM [Sheet1$]') Is there an easy way ...
0
votes
1answer
346 views

Performance problem caused by joining with the result of a scalar function?

In the following example the performance of the first version using only one SQL query is very bad. The problem is the last join. When I store the result of the left side into a temporary table and ...
0
votes
2answers
102 views

table does not exist

CREATE OR REPLACE PROCEDURE ProcedureX() TYPE AVERAGE_COUNT_TBL IS TABLE OF INTEGER; AVERAGE_COUNT AVERAGE_COUNT_TBL; BEGIN INSERT INTO AVERAGE_COUNT VALUES(10); END ProcedureX; It ...
0
votes
2answers
68 views

Is a temporary table a good solution for importing temporary data into a database

i am facing the following scenario. I have a student database in which exam results are stored. Every now and then i get a list with the names of certain students for which i need to find out how well ...
0
votes
1answer
47 views

Optimize query to minimize tmp copying time

I have this mega query : SELECT s.eleve_id, crs.id_cours as 'cours_id', mc.code as cours_code, s.employe_id, s.id_seance, '0' as theorique, s.statut, s.type_vehicule_id, s.type_conduite_id, ...
0
votes
3answers
1k views

Creating a global temp table in MySQL

I am working on a MySQL stored procedure. I need to create a temp table and I want to access this temp table whenever I execute this stored procedure. I know we can't access the temp table in ...
0
votes
0answers
24 views

MySQL query hanging on copying to tmp table

I'm stuck with a query hanging on 'copying to tmp table' for about 25 seconds according to the profiling. The query is used for forecasting the number of results after filtering by job_type / ...
0
votes
0answers
178 views

SQLite UPDATE two tables from VIEW or TEMPORARY TABLE

Here is the query that doesn't compile just to show how it should look like. But since I can't do Multiple table UPDATE's. This fake query should be able to run the inner UPDATE's for both tables if ...