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

0
votes
2answers
221 views

Attempting to avoid SORT in query planner

I have a query that is used VERY frequently in my application. Without modifying the query itself, I'm trying to optimize it by toying with different indexing schemes. Here's the query (basically): ...
0
votes
1answer
134 views

Help me to better design my query so I can gain more performance

I need to find so called first entry in database for items regarding their partners. I wrote query and it is giving me correct results but it sucks with performances. I suspect that I have missed ...
0
votes
1answer
347 views

Returning Multiple Result-Sets or Single XML Blods

I've been writing some new Stored Procedures, and find myself returning between 6-8 result-sets in a few cases. I've always worked under the assumption that there were certain inefficiencies with ...
0
votes
0answers
8 views

Design and queries for analysis of event-sequences

I will have to store sequences of events with certain properties in a database (MariaDB preferably) and I have some issues regarding the design and queries. The crucial part is the analysis of the ...
0
votes
1answer
49 views

SQL Server sometimes uses a lot more cpu for query

I have a stored procedure with a quite heavy SELECT statement that includes five joins and uses FieldName LIKE '%searchterm%' on multiple columns. This query usually finishes in about 2-5 seconds. But ...
0
votes
1answer
33 views

Slow response from SELECT query

I have 50 lac rows (i.e. 5 million rows) in student table. I fire select * from student where s_name like('%something word%') limit 0,5. I define indexing on s_name column. That query takes 2 ...
0
votes
0answers
22 views

My Query is waiting on “db file sequential read” for 5 minutes and getting timeout after that in Oracle

I have a very simple query when I run it individually from the SQL developer in my oracle database it is running perfectly, but when it is part of application flow then it is failing with timeout and ...
0
votes
0answers
13 views

mysql update a table from another table - performance

I have two mysql tables, table_A = 2500000+ (rows) table_B = 6000000+ (rows) I need to update rows in table_A from data in table_B (using a multithreaded application) What is the fastest way ...
0
votes
1answer
68 views

Complicated data

I have a requirement i got from my father. He gave me a table and wanted me to digitalis it. So that he can the system can do all the dirty jobs for him ( that's why there are developers in the world, ...
0
votes
0answers
20 views

Query being executed beyond timeout in a transaction Oracle

I have a simple query as below when executed separately the result is coming very quickly. But when is part of the application flow the same query is being shown as in execution status for more than 5 ...
0
votes
1answer
65 views

How to increase sql server performance? denormalization (making cascade delete off) vs making duplicate data

I have a database with following related table ( sql server enterptise edtion 2014) each state have many citites which each city has may municpalArea and so on State => Cities = > ...
0
votes
0answers
39 views

Slow mysql UPDATE with IF() conditions

not sure why I can't even EXPLAIN this query. Mysql refuses to EXPLAIN it. Table contains a million meaningless test entries. Full schema and data dump of the table available here: ...
0
votes
0answers
76 views

Memory Issue - SQL Server 2008r2

I am trying to troubleshoot a memory issue on a 2008r2 instance. Very high resource semaphore waits are present and a general slowdown of queries,backups,dbcc checks. Only one instance of SQL Server ...
0
votes
1answer
65 views

Speed up a MySQL BETWEEN query

SELECT dateToFind FROM table1 INNER JOIN table2 ON dateToFind BETWEEN dateFrom AND dateTo I need to improve the performance of this kind of query. I added BTREE indexes on dateToFind, dateFrom, ...
0
votes
0answers
32 views

Speed up sorting in this query

I'm trying to optimize the following plan and noticed that PostgreSQL spends a lot of time (relatively speaking) on sorting before the merge joins. Is there a way (using indexes, cluster the table, ...
0
votes
0answers
5 views

DB2 LUW 9.5 ignoring indexes

OK, Stack Exchange - got another good one for ya'. I am seeing something in DB2 that I’ve never seen before. DB2_VERSION: 09.05.5 AIX VERSION: 7.1.0.0 I’ve got a query that is properly indexed. In ...
0
votes
1answer
44 views

Slow SQL Server authentication, but fast SAN, fast querying?

I am asking you after days of research but I think I don't have required skills to understand what is happening. I have a slow authentication on a SQL Server 2008R2, which leads to a timeout for the ...
0
votes
1answer
20 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
20 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, ...
0
votes
0answers
24 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 ...
0
votes
0answers
61 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
41 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 ...
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
25 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
13 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
0answers
54 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, ...
0
votes
0answers
37 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 ...
0
votes
0answers
12 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
3answers
106 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 ...
0
votes
0answers
47 views

same query different servers,different execution plan and time

I have this query and it runs less than 2 minutes on Test but on Prod it takes 10 minutes. Both are the same version,CPU,RAM,OS the same. I have four tempdb files on Prod but one one Test. I ...
0
votes
2answers
119 views

Query Optimization required - Subquery using Inner Join

Could anybody advise how can I optimize this query below please? SELECT IT.[iInternalTransactionID] , IT.[iInternalAccountID] , C.[iContractID] , IT.[dtTransactionDate] AS TransactionDate , ...
0
votes
0answers
58 views

A working view now starts timing out

I have 2 identical databases (one larger, one smaller) with identical views. Recently a view on the smaller DB started timing out. Google searches seems to point to deadlocking of some sorts. If I ...
0
votes
1answer
62 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` ( ...
0
votes
0answers
28 views

Help with MySQL query which is only occasionally slow? Temp tables/Disc caching?

We have an issue with one particular query whch runs regularly on our MySQL database, though I am not necessarily sure it is a problem with the query itself. 90% of the time it runs perfectly fine ...
0
votes
0answers
43 views

Performance getting rows with max column

I have an historic table called 'historic', with three columns: object_id, day, value. For historical reason, day(date) is an integer number (number of day since 01/01/1904) Example: object_id | day ...
0
votes
0answers
24 views

Select changes in table by date

I have following table schema name type id int key string count int date date url_id id with example data id key count date url_id 1 system 100 ...
0
votes
0answers
44 views

Why is PostgreSQL doing a seq scan on column != column?

Why is PostgreSQL doing a table scan on column != column? Isn't this query guaranteed to return no results? Isn't anything != anything guaranteed to be always false for any record on a not null ...
0
votes
0answers
39 views

Slow query (Wordpress)

Can I speed up somehow the following query? Somehow to stop from creating a tmp table/filesort. Tried a couple of indexes but no luck. mysql> explain SELECT wp_posts.ID FROM wp_posts LEFT JOIN ...
0
votes
0answers
17 views

View in View slower to execute?

Sometimes it's easier and clearer arranged to create a View from another View. Can this cause some speed losses in relation to selecting everything just in one Big and long View-Command?
0
votes
1answer
28 views

selecting rows that have n foreign key conditions satisfied

I'm trying to filter a master table rows based on 2-more conditions from the relative foreign key relation, i came up with this which is working fine, but i just can't convince my self that it's that ...
0
votes
1answer
339 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
0answers
59 views

INDEX SKIP SCAN performance on Index where first columns only contain 1 value

We're running an Oracle 11.2.0.3 database and have a table with above 10 columns in it. There is a non-unique index over all these columns. For simplicity in this example, lets assume the columns are ...
0
votes
0answers
18 views

Analize Query in MySQL Server 5.1

I try use MySQL Workbench 6 to analyze the cost, but they don't show the cost of query. How i get this costs?
0
votes
1answer
195 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
147 views

group by and order by making query very slow in mysql?

Query works fast: select SQL_NO_CACHE users.id, users.name from useres left join user_statistics on users.id = user_statistics.user_id where users.is_deleted = 0 group by users.id ...
0
votes
1answer
52 views

Rerunning query in the same connection takes significantly longer

I am having a weird issue when a running a query that joins some semi-large tables (one table is about 2 million rows and the other about 21 million) together. What happens is the query does not run ...
0
votes
1answer
49 views

NTEXT,Indexing, and lob logical reads

We have an query that would benefit from a covering index but one of the columns that would be returned is a NTEXT data type. I know this can't be added to the index and I also know it would be good ...
0
votes
0answers
59 views

Latest of group in subquery is bottleneck, how to optimizing query?

See Original question I'm fetching the activity data for the latest activity for each company as part of a bigger query. I've been using the method provided by ypercube, and it works fine, but I find ...
0
votes
0answers
30 views

Can't run subqueries on my server (MySQL)

I have a weird problem, where I can run sub-queries just fine on my local machine, however they seem to be hanging forever when I attempt to run them on my server. Note that my server has plenty of ...
0
votes
0answers
34 views

MySql query keep running for a long time

Following query keep running for a long time and I'm using MySql 5.5.34 on XAMPP on Windows SELECT id_package, `pck`.`fk_sales_order`, `pck`.`fk_picklist`, count(pci.id_package_item) as items, ...