Use this tag for questions about improving the performance and/or efficiency of database queries.

learn more… | top users | synonyms

0
votes
1answer
61 views

execution plan question

I have this query running for long time (@P0 nvarchar(4000)) select emaildmo0_.Id as Id7_ ,emaildmo0_.CreatedDate as CreatedD2_7_ ,emaildmo0_.EmailAddress as EmailAdd3_7_ ...
7
votes
1answer
164 views

sp_cursoropen and parallelism

I'm running into a performance problem with a query that I can't seem to get my head around. I pulled the query out of a cursor definition. This query takes seconds to execute SELECT A.JOBTYPE FROM ...
1
vote
2answers
67 views

Should I stop an awfully inefficient MySQL DELETE statement?

I'm running the following statement on MySQL 5.5 in a 1GB 1CPU SSD virtual machine from Linode: DELETE table FROM ( SELECT MAX(id) id, field FROM table GROUP BY field ) temp_table INNER ...
8
votes
1answer
136 views

Overcome MERGE JOIN(INDEX SCAN) with explicit single KEY value on a FOREIGN KEY

Added 7/11 The issue is deadlocks occur because of index scan during MERGE JOIN. In this case a transaction trying to get S lock on the whole index at the FK parent table, but previously another ...
1
vote
1answer
46 views

How can I improve this query in PostgreSQL?

I've been using PostgreSQL for a little over a year. I am continually whacked by queries that have counts in them, such as: SELECT COUNT(*) AS count_all, listings.id AS listings_id FROM "listings" ...
4
votes
2answers
132 views

Postgres 9.4.4 query takes forever

We're running Postgres 9.4.4 on CentOS 6.5 and have a SELECT query that has worked for years, but stopped working and hangs after we upgraded from 9.2 (it took a while to notice it, so I don't know if ...
2
votes
1answer
55 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 ...
9
votes
2answers
161 views

SARG cardinality estimate, why not full-scan?

Why there is no full-scan (On SQL 2008 R2 and 2012)? Test data: DROP TABLE dbo.TestTable GO CREATE TABLE dbo.TestTable ( TestTableID INT IDENTITY PRIMARY KEY, VeryRandomText VarChar(50), ...
1
vote
1answer
46 views

Update records from one table to another in different databases where condition is accomplish

I have two tables stored in two differents DBs and I need to perform some sync between both tables based on lastmodifieddate and lastSyncAt. Let's call them as db1 and db2. This is the DDL schema for ...
5
votes
3answers
265 views

Postgres is performing sequential scan instead of index scan

I have a table with about 10 million rows in it and an index on a date field. When I try and extract the unique values of the indexed field Postgres runs a sequential scan even though the result set ...
0
votes
2answers
86 views

Minimizing SELECT latency in PostgreSQL

I am evaluating Postgres 9.4 performance on a machine with Intel i7 quad-core 3.6 GHz CPU, 8 GB ram, and 7400 rpm HDD (no RAID) running Linux Mint. The DB schema has the following table: ...
1
vote
0answers
58 views

Select SQL_Calc_Found_Rows query extremely slow

I'm Wordpress 4.2.2 on an Ubuntu 14.04 LTS server, with PHP 5.9x and MariaDB 10.0.17. When clicking on a post on my site I see many slow queries that all resemble this and it ends up taking as 8 or 9 ...
1
vote
2answers
36 views

Reorganize Index Task [closed]

Can the Reorganize Index task (maintenance plan or SSIS package) be used to tune performance? Is this an adequate step of triage for degraded application/query performance?
1
vote
1answer
37 views

Statistics needs to be captured for query analysis

What statistics are required to theoretically prove that X query work better than Y query. As per my knowledge I need to look for: Query elapsed time Logical read Logical write Physical read ...
7
votes
0answers
130 views

Why is query thousands of times slower with DESC?

I have a PostgreSQL database (AWS RDS) and ran the following queries in psql: livenet=> EXPLAIN ANALYZE SELECT * FROM outputs ORDER BY height LIMIT 10; Limit (cost=0.57..36.82 rows=10 ...
9
votes
2answers
112 views

Why isn't the primary (clustered) key being used in this query?

I have a SQL Server 2008 R2 table whose schema structure looks as follows: CREATE TABLE [dbo].[CDSIM_BE] ( [ID] [bigint] NOT NULL, [EquipmentID] [varchar](50) NOT NULL, [SerialNumber] ...
2
votes
1answer
66 views

Improve performance on SQL Server Query

I'm trying to increase the performance of a query and I'm out of ideas: The query does this: gets a 'Process' with specific priorities on selection namely: Picks up a process that has been waiting ...
2
votes
1answer
46 views

How do I prevent my query from needlessly generating rows it'll throw away later?

I have the following query: SELECT stream_points.time AS point_time, stream_points.value AS point_value, stream_predictions.value AS prediction_value, ...
0
votes
0answers
71 views

Need your help in query tuning

I read ​one article which shows how to use different aggregations effectively. http://sqlblog.com/blogs/paul_white/archive/2011/12/04/is-distinct-aggregation-still-considered-harmful.aspx I am ...
2
votes
1answer
176 views

Mongodb high lock percentage / slow queries

I'm working with an application using MongoDB that is getting a fairly large number of incoming requests and I've noticed that the responses are getting slower as the number of requests get higher. ...
1
vote
2answers
74 views

Is there's another function to optimize this query in MySQL?

I want to use a function that returns for me the same result in one line is that's possible?? MIN( CASE WHEN (data.pro LIKE '1 %' ) THEN 1 WHEN (data.pro LIKE '2 %' ) THEN 2 ...
0
votes
0answers
56 views

PostgreSQL query taking a long time

I have a query that is taking hours to run. There is a large table to parse, is there any way to optimise this? with VehData as ( SELECT * from dblink('host=databasesrv port=1234 ...
4
votes
1answer
99 views

Should I use a subquery to help SQL Server find the correct plan

I have a query (which has components which are built on the fly depending on the selections a user makes in the interface) that runs in SQL Server 2008-R2 that runs in about 100 databases. Users are ...
1
vote
1answer
77 views

Why isn't Postgres doing an index-only scan?

I am using Postgres 9.4. This is my table: id | integer | not null default nextval('frontend_prescription_id_seq'::regclass) presentation_code | character varying(15) ...
1
vote
0answers
31 views

Delete using join or using sub-query?

I need to delete rows from a table based on what is present in a temporary table. For me, both of these statements work : DELETE from main_table where `id` in (select `deletable_id` from ...
1
vote
0answers
60 views

Query execution taking long time

SELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.total_elapsed_time FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext select ...
3
votes
1answer
61 views

Why is the secondary selective index not used when the where clause filters on `value()`?

Setup: create table dbo.T ( ID int identity primary key, XMLDoc xml not null ); insert into dbo.T(XMLDoc) select ( select N.Number for xml path(''), type ) from ( ...
3
votes
3answers
77 views

Is it better to create another table to possible null values in relational databases?

I'm designing a relational database to use in MySQL. I have the next situation: In one table there are fields that in the most of situations are NULL. The fields would be like: Table name: tabla ...
5
votes
0answers
56 views

6GB Table - 37Mi Rows - 'System.OutOfMemoryException' - SQL 2008 R2

I have a proc, looping through databases( the insert is in a fix database...the select uses the loop ), making inserts (Will hide names for security): insert into Main_Database..Table(16 Fields) ...
1
vote
2answers
66 views

Is LIMIT processed before or after retrieving data?

MySQL. My heavy query crashed our MySQL server. If I add LIMIT 10 will it make my query more lightweight? (both for indexed and unindexed queries). Which way LIMIT are processed: before the rest of ...
3
votes
3answers
157 views

Optimizing a query which runs through all hours in a day

I need help optimizing a query. I'm using PostgreSQL 9.3.4 currently but can upgrade to 9.4 if needed. I have a table with 60+ millions of records which looks like this: ...
1
vote
0answers
224 views

INSERT INTO with TABLOCK taking longer than without TABLOCK [closed]

I'm doing something like below to ensure the insert is minimally logged: insert into AnotherDatabase.dbo.HeapTable with (Tablock) ( <column fields ) select <column fields> from ...
0
votes
0answers
34 views

Foreign table not using index

I have a table on database temp_db named external_objects and an index on the field id, which is an MongoDB id (like 5458d717cd07870859000003). When I explain this select at this database, I got this ...
1
vote
3answers
116 views

Index optimization and cardinality

I'm by no means a DBA expert. Rather, a Magento developer having to troubleshoot some slow queries. I'll jump right in -- here's my attempt to EXPLAIN the troublesome query: EXPLAIN SELECT ...
0
votes
1answer
24 views

database with same schema and same AWS instance type, but different performance

Hi I am getting a new set of production servers ready. I generated a DB script from SSMS using the schema from Server A to deploy to a new AWS instance of the same type as Server A. After populating ...
0
votes
1answer
50 views

improve long running query

I have this query INSERT INTO interactions( user_id, external_object_id, origin, interaction_type, interaction_sub_type, published_at, origin_id, created_at, updated_at) SELECT ...
1
vote
2answers
197 views

MySQL JOIN two tables and get latest result

I want to JOIN two tables and get the latest result from each one of those two in a single table. I'm kind of concerned about speed too since tables are growing kinda fast. Close to 60-70k records a ...
0
votes
1answer
40 views

Better way to full text query across multiple tables?

I've got a rather large query across multiple tables and their fulltext-indices which (unsurprisingly) is rather slow: SELECT t0.[Key], t0.[Nr] FROM dbo.[Customers_View] AS t0 WHERE (t0.[Deleted] ...
2
votes
1answer
47 views

Optimizing a 3 table join query

SELECT job.id_job, job.fk_company, job.fk_user, job.fk_job_category, job.fk_job_type, job.fk_place, job.job_identifier, job.external_job_id, job.title, job.short_description, job.status, ...
1
vote
0answers
100 views

How to speed up for huge tables in SQL select query?

I have the following huge tables. Table_1 with 500000 (0.5m) rows Table_8 with 20000000 (20m) rows Table_13 with 4000000 (4m) rows Table_6 with 500000 (0.5m) rows Table_15 with 200000 (0.2m) rows ...
1
vote
1answer
51 views

Why is MariaDB 10 refusing to use my index on a date field?

I have the following table definition: CREATE TABLE `bets` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `id_mp` bigint(20) NOT NULL DEFAULT '0', `coin` char(3) COLLATE utf8_unicode_ci NOT NULL, ...
0
votes
1answer
55 views

GROUP BY very slow when returning many rows

I have a query to aggregate data per day. When I run the query for a single day it is very fast. However, when I run the query for multiple days the time cost increases much faster than ...
1
vote
4answers
506 views

Improve delete speed for SQL Server

We have huge production database, its size is around 300GB. Is there any approach to improve performance of a delete query? Right now deletion speed is between 1-10k per minute, it is very slow for ...
0
votes
0answers
101 views

MySql Innodb Database Slow INSERT Query to Remote Server

I have searched thoroughly on how to improve general performance of my database which is hosted on Hostgator. I have mostly come across performance tuning strategies which are catered to only local or ...
1
vote
1answer
62 views

Postgres not using the index even when rows returned is 5% of the table

I have a postgres table with the following structure: +---------+-------------+-------------+----------+---------+---------+ | id | timestamp | numvalues | text1 | text2 | text3 | ...
0
votes
0answers
44 views

Postgres Query Optimisation (Rails App)

I have a very slow running SQL query that is run from my Rails app using the Model.find_by_sql method. I've made a number of optimisations but it's still extremely slow. It would be great if anyone ...
5
votes
2answers
234 views

how to increase speed of this mysql query?

How I can increase speed of this search query? This search bring news based on time and title and text. each item has own factor. SELECT * , MATCH (`title`) AGAINST ('tax in work') * 1.65 AS ...
0
votes
1answer
179 views

Memory usage on select for large number of rows

I am trying to dump the entire contents of a large table, from the command line using pqsl, but am running into a problem where memory usage goes up, to the point where the process is killed, before ...
0
votes
1answer
107 views

Optimizing key lookup in subquery with UNION ALL

I am trying to optimize following query (little bit more complex actually, but this is important part): SELECT Id, StatusDate, [...Lot Of Columns...] FROM ( ( SELECT Id, StatusDate, [...Lot ...
3
votes
1answer
130 views

Pagination performance, with subquery, inner join and where

I am trying to optimize following query (this is most simplified version I could came up with): SELECT tr.Id, StatusDate FROM ( SELECT tr.Id, tr.StatusDate FROM mon.ArchivedTaskResults_201504 ...