Tables used to store transient data.

learn more… | top users | synonyms

14
votes
2answers
936 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 ...
2
votes
1answer
14k 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 ...
0
votes
1answer
648 views

Inserting query result to another table hangs on “Copying to temp table on disk” on MySQL

I started the process of inserting returned results to another table. The query groups the rows in respect of indexed IDs. This causes 149,000,000 rows to be decreased to 460,000 rows. The query ...
1
vote
1answer
889 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 ...
2
votes
0answers
361 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 ...
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'! ...
1
vote
1answer
118 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 ...
206
votes
4answers
55k 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?
8
votes
2answers
4k 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 ...
18
votes
1answer
5k 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 ...
2
votes
1answer
3k 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 ...
6
votes
3answers
811 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 ...
2
votes
2answers
478 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 ...
1
vote
3answers
3k 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 ...
2
votes
1answer
649 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, ...
6
votes
1answer
699 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 ...
0
votes
1answer
406 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 ...
5
votes
1answer
450 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 ...