The temp-tables tag has no wiki summary.
-4
votes
1answer
36 views
Update date Column values? any please help? [closed]
I Want to copy and data in same Table and Update date Column values? any please help?
1
vote
2answers
37 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 ...
0
votes
2answers
47 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 ...
1
vote
0answers
43 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
112 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 ...
115
votes
3answers
18k 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?
12
votes
1answer
2k 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 ...
4
votes
1answer
376 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 ...
2
votes
1answer
135 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 ...
7
votes
2answers
1k 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 ...
1
vote
3answers
1k 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
3k 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 ...
-7
votes
2answers
502 views
Display month in columns and the sum of sales below the columns in sql [closed]
My result is this:
Sitecode Month Amount
-------- ----- ------
XX Jan 1000
XX Jan 3000
XX Apr 3000
XX Apr 1000
Result will show like ...
3
votes
2answers
440 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 ...
1
vote
1answer
89 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. ...
1
vote
2answers
157 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
3answers
716 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
0answers
54 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
76 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
2answers
101 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 ...
0
votes
0answers
278 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 ...
13
votes
2answers
391 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
2answers
872 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
323 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 ...
3
votes
0answers
204 views
Why are runtimes different for a table including a primary key versus a table with a clustered unique index added after population
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
1answer
70 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 ...
5
votes
1answer
547 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 ...
2
votes
2answers
302 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
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
614 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
1answer
420 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, ...
0
votes
1answer
290 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
342 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 ...
