All Questions
Tagged with temporary-tables optimization
15 questions
0
votes
1
answer
4k
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 ...
- 151
0
votes
1
answer
371
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 ...
0
votes
0
answers
16
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 ...
- 1
0
votes
1
answer
71
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 ...
10
votes
1
answer
4k
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 ...
- 370
0
votes
1
answer
447
views
Postgres: extracting multiple bit-columns from single 'flags' column after join with 2nd table
I have a query similar to this below. As you can see, there are multiple parts which are very similar.
SELECT
id,
aaaa,
bbbb,
tags
,(
SELECT COUNT(*) > 0
...
- 109
4
votes
1
answer
11k
views
Slow CREATE TEMPORARY TABLE from SELECT in MySQL
I asked this on StackOverflow, but there are not many hints. So:
SHOW CREATE TABLE cashadv_txn:
CREATE TABLE `cashadv_txn` (
`txn_id` int(11) NOT NULL AUTO_INCREMENT,
`cashadv_id` int(11) NOT ...
- 165
1
vote
1
answer
735
views
Join external data in SELECT query
I have few (~20) names of products corresponding to an integer id that needs to be joined on products while exporting products data (using COPY (<query>) TO). The burden is that these names do ...
- 113
2
votes
1
answer
1k
views
MySQL Filesort/Order By Slow
I've been struggling with a query for the past few weeks and it now just appears as gobbly-gook to me when I try to optimize. The query runs - at about 2.3 seconds, but I know there has to be a more ...
0
votes
2
answers
3k
views
How can i update my database from another database using temporary tables?
I want to update tables of my database using another big database every day with using temporary tables and without returning result. How can i update it?
This my temporary table :
use data_pro;
...
- 121
0
votes
1
answer
860
views
Get the latest record and count them with time interval
I have a table as below
id fk_sales_id status created_at
1 001 5 2015-02-17 16:45:44
2 001 8 2015-02-18 18:45:44
3 002 30 2015-02-20 16:45:44
...
- 101
3
votes
1
answer
650
views
When is mariadb creating tmp tables?
I have found, that copying to tmp table is taking most of the time of a slow query, that I wanted to optimize. I watched the output of 'profiling' in a terminal. This happens on a pics table with 6000 ...
- 161
0
votes
1
answer
84
views
Optimize query to minimize tmp copying time
I have a query when Checking the profile i get this :
I read in order to optimize this i'll have to work on my joins .
I also added index on the fields that i'm grouping by (date_seance , ...
- 123
18
votes
2
answers
99k
views
"Copying to tmp table" extremely slow
This is my example of query:
SELECT
nickname,
CASE class_id
WHEN 1 THEN 'Druid'
WHEN 2 THEN 'Necromancer'
WHEN 3 THEN 'Mage'
WHEN 4 THEN 'Priest'
WHEN 5 THEN '...
- 181
6
votes
3
answers
14k
views
Avoiding temporary tables while sorting by a column in a different table
So I have this MySQL InnoDB query here:
SELECT s.vid, s.body, s.timestamp, COUNT(v.id) AS votecnt
FROM stories s
LEFT JOIN votes v ON s.vid = v.vid
WHERE s.lv = 1
AND s.status = 1
GROUP BY s.vid
...
- 525