A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.

learn more… | top users | synonyms

1
vote
1answer
84 views

Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

I have a poorly performing query: SELECT user_id, count(item_id) as count FROM table items WHERE category = 'magazine' AND created_at > 1384754400 GROUP BY user_id ORDER BY count(item_id) desc ...
0
votes
2answers
19 views

Best way to store few rows of data every minute without creating 1440 columns a day?

I will be calling a server that returns some JSON data every minute. The values are along the lines of Value, Currency, DateTime, and maybe 1 or 2 more values. What is the best way to keep save these ...
0
votes
2answers
115 views

Database is slow once in a while

I am executing a web service for 100 times. The average response time of this service is 1.5 seconds. But when I look at the response time of each execution individually, there are a few executions ...
0
votes
0answers
13 views

Can having a LIMIT on DELETE degrade performance dramatically?

I have a database project in php and mysql. For, hopefully redundant, data security I added a LIMIT 1 to the delete($uniqueID) php function we use. Nobody cared because records really aren't ...
0
votes
0answers
14 views

EXPLAIN (with subqueries) is giving results I don't understand

I have a query that relies on a subquery, and that subquery relies on another subquery. When I run an EXPLAIN on the full query, in does a full table scan of the table in the primary select. However, ...
1
vote
1answer
81 views

PostgreSQL 9.3 - Performance Issue: Counting table entries slower with jdbc postgres driver

I'm using PostgreSQL 9.3 and want to ask if it is possible to improve the performance of my queries based on the following database schema: CREATE TABLE "entities" ( "id" BIGSERIAL, "type" INT ...
1
vote
1answer
68 views

Reducing execution time for this gnarly SQL

I'm creating an order system of some kind. There is a function that I use to get orders (in PHP) and retrieve the required information about the order. However, it seems that I am pulling a lot of ...
1
vote
0answers
15 views

Why do these two queries (which I think are equivalent) rely on different keys?

We have a query that we don't like, since it ends up doing a full table scan. EXPLAIN SELECT count(*) AS c, location.name FROM study LEFT JOIN location ON study.location_id = location.id ...
0
votes
1answer
85 views

How to optimize query

using SQLite3 and the following query, it seems like there should be a better performing way to write this query? Is there? Select Name, Sum(Case When Used Is ...
2
votes
2answers
222 views

SQL server query is slow *without* including execution plan

I have just set up fresh installation of SQL Server 2012 on a new server and I'm experiencing very strange performance problems. Long story short, for some reason not including execution plan makes my ...
0
votes
0answers
19 views

Is it best practice to include partition keys within foreign keys when referencing partitioned tables?

The large tables in our database are partitioned by an id in order to separate data and speed up IO. The table of the data is partitioned by the partition key, but the primary key is only on an ...
1
vote
2answers
29 views

Why is MySQL ignoring my indexes?

I have a query select distinct id from patient where company_id in (1) and (name_last like 'peter%' or name_first like 'peter%' or remote_id like 'peter%') running on a table that looks like this ...
0
votes
0answers
39 views

Oracle 11.2 Explain Plan takes too long

I'm using Oracle 11.2 SE on a 16GB Xeon E3 Linux machine as well as 11.2 XE on a 8GB i7 Win8 machine: SELECT banner FROM v$version: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production PL/SQL ...
0
votes
1answer
37 views

If a query contains two identical subqueries, will they both run?

Say I have a SELECT query that contains two identical subqueries, like the below: SELECT (SELECT SUM(Price) FROM Product p WHERE p.GroupID = g.GroupID) AS 'TotalCost', (SELECT SUM(Price) ...
0
votes
1answer
49 views

A single, simple update takes 10+ seconds

We are running MySQL 5.1.71-log and have a table with 2.7 million rows. Simple updates to it are regularly taking more than 10 seconds to execute. The table structure is: CREATE TABLE `person` ( ...
-4
votes
0answers
34 views

Expensive Queries - by CPU [closed]

I check in Reports by cpu & by Duration there is coordinate graph of that. Top 20 quires in it which use more CUP i.e.(2000000 ms). is there any solution to reduce it.
0
votes
1answer
885 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` ...
1
vote
0answers
44 views

Slow compilation of a SQL statement

I have SQL Server 2014 and a large table (more than 200Gb) with 100M rows and more than 300 columns. For one "unpivot" query I wanted to see its estimated execution plan and it took more than 90 ...
4
votes
0answers
40 views

How to handle bad query plan caused by exact equality on range type?

I'm performing an update where I require an exact equality on a tstzrange variable. ~1M rows are modified, and the query takes ~13 minutes. The result of EXPLAIN ANALYZE can be seen here, and the ...
0
votes
1answer
29 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning ... Actualy the tunning of my.cnf is like below My database is in MyISAM | Mysql 5.1.73 key_buffer = 500M ...
2
votes
2answers
169 views

UPDATE query with JOIN taking forever

I'm not very good with databases so please keep in mind even if the solution sounds like something everyone would have already tried, please post it because I likely have not tried it and it would ...
1
vote
0answers
22 views

Postgres Performance Over Group by with MAX and MIN

I just want to retrieve the data for specific location of customers with their ever first and last purchases made in the system. Table details : CREATE TABLE customer_location ( id UUID, location_id ...
0
votes
1answer
45 views

Does it make sense to order condition in where query?

We have a clustered index in table Tbl with order of A,B,C. Does it make senses to write a query to have WHERE A = @a, B = @b, C = @c or it'll be as same as WHERE C = @c, B = @b, A = @a Does the ...
0
votes
0answers
53 views

Last few rows from a non-indexed table in Oracle

I'm trying to run a query on an Oracle DB through SQL Server [dont' ask, it's a requirement] The Oracle table in question [lets call it hugeTable] has over 200 million rows and no index. I'm not ...
0
votes
0answers
17 views

Mysql query with LIMIT much slower under RDS (5.6) than old 5.1 system

We inherited an old legacy system running on mysql 5.1, and moved it to RDS running 5.6. One query is running much slower - it can take up to 20 minutes to run, where previously it took only a few ...
0
votes
0answers
12 views

Documents with many potentially overlapping geolocations (maybe MongoDB)

Let's say I have a large list of coordinates coupled with the type of building is at this location (e.g., restaurant, doctor's office). In this scenario, there would potentially be many different ...
0
votes
1answer
29 views

How can I time SQL-queries using psql from command line?

I am sorry, I cannot comment, so I am posting a new question. There is already a question "How can I time SQL-queries using psql?" but I am missing answer how to do that from command line. How to run ...
3
votes
2answers
4k views

SQL SERVER - Linked Server and query performance

I have a performance problem related to SQL Server/linked servers/views. I hope you can help me understand what is the best way to do what I want =). I have a database K with 3 linked servers ...
0
votes
1answer
129 views

Select from a list of values from JSON encoded data

I have saved the details of the product in JSON format. I am wondering how to select from a list of values from a "JSON_ENCODED DATA" For Example I want to select TV with screen_type as LED or LCD. ...
0
votes
1answer
89 views

Couldn't find the long running SQL under v$session_longops

I have following SQL running from a long time. I couldn't see the status When I tried to look the time remaining for the query to complete. Can someone explain me why I couldn't find the SQL status ...
2
votes
1answer
83 views

Improve select query performance

The following select query from my application is taking more than 200ms. The table has around 2 million rows. Is there any ways to improve the performance of this query ? select count(id) from ...
0
votes
0answers
52 views

Index Tuning Question

I have the following query: SELECT T1.ITEMID, T1.ITEMTYPE, T1.PURCHMODEL, T1.HEIGHT, T1.WIDTH, T1.SALESMODEL, T1.COSTGROUPID, T1.REQGROUPID, T1.EPCMANAGER, ...
1
vote
1answer
75 views

MySQL: Which join is better between left outer join and inner join

Which join is better peforming if all of them provides the same result? For example, I have a two tables employees(emp_id,name, address, designation, age, sex) and work_log(emp_id,date,hours_wored). ...
1
vote
1answer
32 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) ...
1
vote
1answer
102 views

Minimizing Page Fetches

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
1
vote
1answer
162 views

How can I use mongoDB efficiently in this scenario?

I'm trying to build a system of documents that have hundreds of tags assigned to them, but each document-tag association has a certain condition it has to also meet so the document would be returned: ...
3
votes
1answer
537 views

Oracle: How to gather stats in a logical standby database?

I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced. Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On ...
2
votes
1answer
75 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 ...
0
votes
1answer
143 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 ...
0
votes
0answers
32 views

How to optimize having bit_or?

Let's start with SQLFiddle http://www.sqlfiddle.com/#!2/64fc5/1 I have some urls and I am making some tasks (crons) related to them. Task can be successful or not. I want to try to do it only three ...
5
votes
2answers
334 views

Index seek much slower with OR condition compared with separate SELECTs

Based on these questions and the answers given: SQL 2008 Server - performance loss possibly connected with a very large table Large table with historical data allocates too much of SQL Server 2008 ...
0
votes
0answers
8 views

Optimization of an old query multiple unioned subqueries

I am currently in the process of optimizing a large query that was written for oracle 8i. My company currently uses oracle 10g which gives us a lot of options for optimization. Please Reference the ...
0
votes
2answers
73 views

View with left joins are very slow

I have a view based on join of 3 tables and and query on view is taking 10 mins to fetch the results. Can anybody help me in reducing the time taken to fetch results? VIEW CREATE view ...
0
votes
2answers
1k views

Oracle query is slower the second time

I have a general question about something I'm seeing in performance tuning in Oracle. I have a query that I'm testing. It's too big and complex for me to provide a simple example, sorry! (I tried ...
1
vote
1answer
1k views

statistics State in MySQL Processlist

I have a busy MySQL Server having query SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION ...
0
votes
3answers
102 views

Why is my select statement so slow?

SELECT name FROM sys.databases WHERE name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB','1303VISBOC') AND CASE WHEN state_desc = 'ONLINE' THEN ...
3
votes
4answers
148 views

Are we a good fit for SQL Server Enterprise and table partitioning?

We currently have a production database that contains millions of records (14 million in the largest table). One function of the database is to provide time sheet information so a single table could ...
1
vote
2answers
68 views

Improving `GROUP BY` query performance in sqlite3

I have a little web-application that is using sqlite3 as it's DB (the db is fairly small). Right now, I am generating some content to display using the following query: SELECT dbId, dlState, ...
0
votes
1answer
25 views

PostgreSQL - If I run multiple queries concurrently, under what circumstances would I see a speedup? Under what circumstances would I see a slowdown?

I approach you all humbly as one who is NOT a DBA, and I'm sure that my question is fraught with conceptual shortcomings and "it depends on" land mines. I'm also pretty sure that all of you who ...
0
votes
1answer
41 views

Last row from a query without Order by

I have something like SELECT var1,var2 FROM table where var3=X AND var4=Y order by var5 desc limit 1; Running this query takes roughly 5 seconds on my database which is far to slow. I was ...