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

learn more… | top users | synonyms

6
votes
1answer
12k views

Optimizing large database query (25+ million rows, using max() and GROUP BY)

I'm using Postgres 9.3.5 and I have a large table in the database, currently it has over 25 million rows and it tends to get even larger rapidly. I'm trying to select specific rows (all unit_ids with ...
3
votes
2answers
212 views

Updating large table worked before, now taking hours

BACKGROUND: I am using a non-production SQL Server to manipulate a large data set and run a few basic calculations. The original data file I have, which is about 30M rows, is missing records (not NULL;...
2
votes
2answers
89 views

Is 3 minutes to join a 50 million table too much?

I apologize if this is the wrong place to ask, but I really need some opinion on whether or not my query is good or not. Basically, I need to get a count of old and invalid entries per group, which ...
0
votes
1answer
26 views

Random performance of stored procedure across databases

I have a stored procedure that is installed on 30 databases across 4 servers. We encountered an issue last night where 3 of these stored procs are now taking over 8 minutes (one 18 minutes!) to ...
1
vote
1answer
132 views

placing conditions in a join query join on vs where

Is there and advantage to one of the following options over the other or are they the same ? select * from T1 Join T2 on T2.id=T1.t2_id and T2.field=value; vs. select * from T1 Join T2 ...
0
votes
1answer
41 views

Can I get an equivalent query for this Not in Query

SELECT ID FROM dbo.SomeTable WHERE ID not in ( SELECT MIN(ID) FROM dbo.SomeTable GROUP BY SystemNo ) As this query ...
9
votes
1answer
432 views

Update queries slower after enabling SQL Server Full-Text Index

I have an asp.net website with many insert, update, and delete queries running against my database. A few days ago I create a Full-Text Index on two columns of one of tables. After that I realized ...
1
vote
0answers
35 views

what if all the non clustered indexes on my table were filtered indexes?

I have a table that is highly hit on my database. this table has 370,370 records. when I run the following select: select [ImageSize] , the_num = count(*) from [dbo].[ProductImages] group by [...
5
votes
2answers
279 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------...
0
votes
1answer
34 views

Database ExecuteSQL(MySQL client run out of memory)

I've an online game the game server execute more than 1M queries [ SELECT , REPLACE , UPDATE ] every 10 sec the problem the memory usage still increasing i dun know why ?? i mean if we suppose that ...
0
votes
2answers
60 views

Same query with less data takes more time [on hold]

I have a same query to find MIN(download_id) but different data ranges in where clause. The criteria which has more number of rows takes less time but query result which has relatively less number of ...
0
votes
0answers
27 views

Execution time for CONTAINSTABLE/WHERE query

I am facing an issue for a seemingly trivial database query on SQL Server 2012 accessing a full-text index: SELECT MyTable.ID FROM MyTable INNER JOIN ( SELECT [KEY] FROM CONTAINSTABLE(MyTable,...
0
votes
1answer
21 views

Why does a query on 4 times more data take 300 times more processing?

To score the price preference of our individual customers in, we run the below query. Basically it is a weighted average of a product score. The score depends on the product group and the packaging ...
0
votes
0answers
18 views

query cost in postgresql using explain

I read the following link and try to understand the output. https://www.postgresql.org/docs/9.1/static/sql-explain.html EXPLAIN SELECT sum(i) FROM foo WHERE i < 10; ...
3
votes
1answer
44 views

Slow query on primary server runs fast on hot standby

I have a primary PostgreSQL 9.3.4 server with 64GB of RAM that is replicated using streaming replication to a hot standby server with 32GB of RAM. My problem is as follows: I've detected a query that ...
3
votes
3answers
261 views

Long running delete query

I have a table with roughly 11 million rows, defined as: CREATE TABLE [sko].[stage_närvaro]( [datum_fakta] [datetime] NULL, [person_id] nvarchar NULL, [läsår_fakta] nvarchar NULL, ...
0
votes
0answers
17 views

Very slow query with percona XtraDB Cluster

I'm trying a percona xtradb cluster (3 nodes) on a rancher environment (automatically created from rancher catalog). Replication works great, but I've lots of problems with slow queries. First of ...
1
vote
1answer
167 views

Optimizing intersect query in MySQL

I'm facing a performances problem in our MariaDB server. We have a table linking users to pages, a user belongs to 1+ pages. I have a page that have users and I want to get the 10 best pages based ...
12
votes
2answers
244 views

How to track blocking that happen for less than a second - SQL Server

I am trying to troubleshoot a blocking issue which happens for less than a second. The OLTP application is very sensitive and has to have an response time of less than 200ms for some transactions as ...
2
votes
2answers
2k views

Improve MySQL Query Performance - Too many records

I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during ...
1
vote
3answers
20 views

Why is determining existence (or not) of rows matching certain criteria using a 'LIMIT 1' query on indexed columns examining more than 1 row?

Consider a table created like this: CREATE TABLE `someTable` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `owner_id` bigint(11) unsigned NOT NULL, `device_id` bigint(11) unsigned NOT ...
1
vote
1answer
45 views

Performance of BINARY vs CAST AS BINARY in MySQL

Using MySQL 5.6.23, I recently changed a prepared statement from: SELECT * FROM table WHERE BINARY column = :boundValue to: SELECT * FROM TABLE WHERE CAST(column AS BINARY) = :boundValue ...
1
vote
1answer
538 views

MySQL Query with good execution plan is very slow due to Sending data operation

I have the following query: SELECT a.borrowerId AS borrower_id, a.created, NULL AS initiator_team_id, NULL AS initiator_user_id, NULL AS old_team_id, NULL AS old_user_id, ...
0
votes
1answer
29 views

Full scans/sec metric seems very high, dropped after killing 2 queries

Our app today (java spring web application) suffered a major slump and Investigating the SQL Server metrics I found what seems to me a very high full scans/sec value. I killed the 2 SPIDs that where ...
0
votes
0answers
32 views

Postgres: Understanding Hash Left Join?

Using Postgres 9.4, I'm trying to a query by conditions ==> no hash left join. EXPLAIN ANALYSE SELECT CEAR.RESULT_ENUM_TYPE_ID, CEAR.CE_CE_COMMUNICATION_EVENT_ID, MC.CAMPAIGN_NAME, MC....
2
votes
1answer
171 views

Mysql Partitions with Partition limit

I have a partition table on basis of Primary key having 1.5 Billion rows in it. But when i check ibd files only 80 ibd show in GB's whereas 120ibd shows 1MB file. I would like to know how does the ...
3
votes
2answers
153 views

SQL Prove memory pressure - high Buffer Cache Hit Ratio, but low Page Life Expectancy

Our production server (2012, VM + SAN) has 32 GB of RAM, the database size is ~80GB. The application uses TempDB heavily - disk is hit ~100 MBps both reads & writes. Seeing tons of SQL ...
0
votes
1answer
50 views

how to judge sql performance through these two explain?

If I give you 2 explain results from MySQL,how do you know which query is better? the first sql explain *************************** 1. row *************************** id: 1 select_type: ...
1
vote
1answer
860 views

MySQL: Optimize query with a range and distinct count

I have a query on a large table with millions of rows that looks like this: SELECT COUNT( DISTINCT clicks.tx_id ) AS unique_clicks_count , clicks.offer_id FROM clicks WHERE ...
0
votes
2answers
79 views

Get all records from inner joined tables if second table does not have data

I follow below syntax by which I achieve the result. but it takes time IF NOT EXISTS(select * from #tblxyz) BEGIN INSERT INTO #tblxyz select NULL END Select a.* FROM tblabc a ...
3
votes
2answers
552 views

Slow fulltext search due to wildly inaccurate row estimates

Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB. The database schema is ...
1
vote
1answer
47 views

three-table JOIN query on a MySQL Cluster has become unusably slow

I have a select query called by a script to our MySQL Cluster that has gone from running in a second or two to taking well over a minute per query (and it has to run this query 24000 times). Nothing ...
3
votes
1answer
236 views

High I/O for part of query

I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF): In the above part of ...
1
vote
0answers
37 views

Under which conditions would selecting a small column off a primary key be slow?

Usually querying the table is in the millisecond range at most. But sometimes it takes 30 seconds and then times out. What could cause SELECT smallColumn FROM SomeTable WHERE pkID=@pkID to be slow? ...
3
votes
1answer
207 views

Mysql join table order by index

First Master Table CREATE TABLE `edipostingmaster` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `applied` int(11) DEFAULT '0', `fileName` varchar(255) DEFAULT NULL, `checkNo` varchar(100) ...
-2
votes
2answers
63 views

Query taking very long time in 12C environment, but the same query is taking just a few seconds in 10G production environment

We are migrating a database from 10G to 12C and currently we are testing in a dummy 12C production environment. Particularly 3 SQL queries are taking almost 50 min. time to complete and show the ...
0
votes
2answers
91 views

Optimizing sql server MIN in the select blah where blah IN (SELECT MIN(blah)

is there any effective way to tune the query below? I have a filtered index on OrganisationID <> 0 Primary key is on another field, not relevant for this query ItemCode varchar(20). I have tagged ...
12
votes
2answers
939 views

SELECT TOP 1 harms query performance; is there a dba-accessible way to overcome this?

In a production application (C# talking to SQL Server 2014 Standard) there's a query that looks like this, below. Most of the time it runs in milliseconds. But occasionally (for certain values of @Id),...
2
votes
1answer
138 views

pt-query-digest results are empty

I have a slow log generated by MySQL 5.1.71-log, that I've copied onto another machine. It appears I'm not getting any results from pt-query-digest. $> pt-query-digest slow.log # 2.1s user time, ...
3
votes
2answers
1k views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
24
votes
2answers
30k views

Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with 16GB of RAM. The server is dedicated to this database. Given that the default postgresql.conf is quite conservative regarding ...
0
votes
1answer
79 views

Is it beneficial to include ORDER BY fields in the list of SELECT fields? [closed]

I'm just wondering when you do a query and you use ORDER BY should the fields you use in the ORDER BY clause always be included in the SELECT list even if you don't need them? Is this beneficial in ...
0
votes
2answers
5k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
4
votes
2answers
682 views

Is it possible to rebuild a dropped index?

Is it possivle to drop and index, and rebuild it, instead of disable it and rebuild?
1
vote
1answer
167 views

How to speed up queries in MySQL on a table with ~50 million rows

The table has 4 columns of type datetime, smallint(6), tinyint(4) and decimal(15,5). The first 3 columns are PK. There are four indexes on the table: one with all the PK columns, and one with each ...
1
vote
2answers
174 views

mysqld is terminated because the system is low on memory; what to do?

I did recently a site migration; during that I had to erase all plugins and the reinstalling only the minimum as the site was causing the server to crash (A Vps with 1gb of ram), the most problematic ...
1
vote
2answers
28 views

How can one identify if FK's needed to be indexed apart from the NC indexes already residing

I was going through many articles, which said indexing the FK's is beneficial for good performance, specially for the environment where most of the reporting is done. I have a similar env where there ...
1
vote
1answer
43 views

Single table having large no of rows [closed]

I am about to start a project and currently working on data design. While I am done in defining schema of db, I am just wondering to ask from experts. I have only one table in which I am going to ...
2
votes
0answers
37 views

Understanding Database Query Cost Optimzation

I have been learning Query Optimization from "Fundamentals of Database Systems by Elmasri and Navathe (6th edition)" and I am having trouble understanding on how some variables are being derived in ...
1
vote
0answers
13 views

NuoDB Statistics

I'm currently doing a research using a TPC-H table and I'm trying to get the running time for 3 queries in NuoDB: SELECT * FROM LINEITEM SELECT * FROM LINEITEM WHERE L_PARTKEY BETWEEN 1 AND 80000 ...