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
0answers
38 views

Two-part question on how Postgres queries multiple columns and its performance

I have a Django "nominations" app that stores some personal information of nominees, e.g. name, email, shipping address. The email column has a max length of 255, where the shipping address would ...
0
votes
1answer
1k views

Optimize MySQL Self JOIN Query

I have a query which is running in 15+ seconds SELECT t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize` FROM `stocks` AS t1, `stocks` AS t2 WHERE ...
2
votes
2answers
123 views

Convert to Self JOIN

I have a correlated sub query, which is taking 16 Minutes to execute. How can I convert it to join or optimize it. SELECT SSF.SM_StockCode, SSF.ST_ItemSize FROM Stock AS SSF WHERE ...
3
votes
3answers
462 views

Is database partitioning for performance reasons meaningful in a virtualized environment?

When running SQL server on physical hardware, placing database data and log files on separate disks or RAID arrays can benefit performance, resilience, and maintenance. Partitioning the database over ...
0
votes
1answer
869 views

Query that only uses index takes long time to execute [closed]

I have a table with ~69,000 rows. I have this very simple query: -- SET STATISTICS TIME ON SELECT ID, FirstName, LastName FROM Customers -- SQL Server Execution Times: CPU time = 31 ms, elapsed time ...
0
votes
2answers
181 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
402 views

Simple PostgreSQL lookup table is inexplicably slow

I'm trying PostgreSQL 8.4.14 for storing triples, pieces of data of the form (String, String, String). For speed, I'm not repeatedly storing strings but rather using two tables: main table triples ...
0
votes
0answers
196 views

PostgreSQL 9.2 (PostGIS) performance problem

A month ago i got acquainted with PostgreSQL and only now i've noticed that some of my queries are pretty slow. Well, for example, i have table: CREATE TABLE sometable ( gid serial NOT NULL, geom ...
0
votes
2answers
411 views

Query gets slow when bind peeking is turned on

There is an old database that was upgraded from Oracle 10 to 11G R1. In Oracle 10 we had disabled bind peeking. Since Oracle 11 has adaptive cursor sharing, we want to turn bind peeking back on. When ...
0
votes
0answers
167 views

Slow query with ORDER BY with index created (more than 4mins on 0.6million records

I ran into a very weird query performance situation. If I run this query without order by, it finish in millsecs, with order by, it ends up with more than 4mins. EXPLAIN SELECT `book_book`.`id`, ...
1
vote
1answer
277 views

MySQL Query Optimization - Indexes & Joins 12 million+ records

I'm running the following query: SELECT `track_title`.`title`, `track`.`id`, `artist`.`name` artist, `album`.`title` album, `st_tag`.`tag`, `track`.`year` FROM `track` ...
1
vote
0answers
53 views

Optimizing a query using derived tables [duplicate]

As part of a daily cron job, I need to run a query that processes a whole lot of data. This data is related to the visitors coming to a website, and updating the data with what we have captured ...
0
votes
0answers
303 views

Mysql high delay in critical peak | Professional Help request

I have a Centos 6.4 under 2.6.32-358.18.1.el6.x86_64, running Asterisk 11& A2billing in real-time switching with Mysql 5.5.33. My Server it's a Dell R420 having dual Intel(R) Xeon(R) CPU E5-2470 ...
1
vote
0answers
244 views

Sql Execution Plan - Hash Match

I'm currently working on a Sql Query that's having performance issues. I ran the execution plan and saw the Hash Match has 50% cost and also, doing a index scan. I'm starting to learning about the ...
0
votes
2answers
3k 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
1answer
375 views

MySQL query optimization with aggregate ORDER BY

I have an analytics table that stores metrics aggregated the day level. I need to ask questions such as "For a given date range and client, what are the most popular items." The schema is as follows: ...
0
votes
1answer
136 views

Can I speed up this slow query which should be limited by MIN/MAX values?

The intention here is to look at the user retention over a period of time. My current solution employs a temporary table, since I do this over several ranges. CREATE TEMPORARY TABLE ...
1
vote
3answers
2k 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 ...
0
votes
1answer
128 views

Using an enum index to limit records

A common issue, just not sure on the keywords. I have a table of assessments (14k+ records). Each assessment can be in many different states ('draft', 'proofing', 'amends', etc... and 'done'). Most ...
0
votes
1answer
361 views

Efficient way to write query for multiple table which depends on column value of a table in mysql?

In the given schema, there is a table A having column A1,A2,A3,A4 There are three more tables i.e. B,C,D If the value in A4 is b, i have to lookup table B. If the value in A4 is c, i have to lookup ...
1
vote
0answers
4k views

How to speed up query on table with millions of rows

The Issue: I'm working on a big table that consists about 37mln rows. Data include measurements of many devices made in certain time e.g. '2013-09-24 10:45:50'. Each day all of those devices are ...
3
votes
1answer
398 views

SQL Server WHERE clause on CLR method (spatial) peformance

I have a query that joins to a table containing 2 million rows and need to filter on its geometry column by its geometry type (using geom.STGeometryType()). The query takes less than a second to run ...
1
vote
3answers
3k views

Simple select is taking too long

I am facing an issue with the performance. The selection of data from table is taking more than 20 sec to retrieve close to 300k records. Created a clustered index on col1 and col2 but no improvement. ...
0
votes
3answers
1k views

Duration Field in Profiler

I would like to monitor the script which takes more than 25 Sec. When i use filter i am not able to see the duration taken by the script. How can we get the time taken to execute the query. My ...
4
votes
5answers
1k views

How can I prove that the following T-SQL is bad for performance?

I have recently inherited a codebase with a large amount of Stored Procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
2
votes
1answer
532 views

SQL Server 2005 - Query optimization for fetching large number of rows from table with 750 million rows

Brief on application: This is audio fingerprinting application, being developed in Java with Microsoft SQL Server 2005 database. I have one application to create fingerprints of original songs and ...
0
votes
1answer
1k views

How to measure a perfomance of SQL Server database? [closed]

I have the task to improve the performance of a SQL Server 2012 database (one of 4 in an instance) by 60% confirmed by corresponding statistics. So, I need to measure "performance" of the RDBMS ...
0
votes
0answers
42 views

How to optimize this query more?

First at all I am nood into SQL thing, Now I am working on a class project where I have some tables like Table user user_id | username | name 1 | nihan | Nihan Dip 2 | dip ...
1
vote
1answer
438 views

Retrieving data speed tweaks SQL Server 2005

I have a database in Microsoft SQL Server 2005. I have table with 3 columns, namely HASHKEY (BIGINT), NOTE_ID (INT) and TIME_OFFSET (INT). Columns HASHKEY has 19 digit BIGINT values, which can ...
1
vote
0answers
201 views

Read vs. Write speed

In the data import procedures I have, I get external raw data and import it into tables. I don't just overwrite the data, even if it is only from one source, but before I allow an update, I check if ...
2
votes
1answer
280 views

Why would a query in code be slower than a query in a SQL tool

When we face performance issues with out application, one of the first things we do is try to isolate if the problem is the code, the queries or the database itself. The most frustrating part of this ...
-2
votes
1answer
119 views

How big the database should be in order to take 30 minutes to query

We have few tables each containing 2,5 million rows in average. Then there is a select query with joins. It takes more than 30 minutes to execute. The db server runs on a machine with 9GBs of RAM and ...
1
vote
1answer
61 views

What ways are there to limit the processor / ram requirements of a specific query?

Every once in a while, I need to run some very expensive query on a production server. Think spaghetti joins. This is likely to affect the server operation, and I don't want that. What ways are there ...
1
vote
1answer
234 views

MySQL looking up more rows than needed (indexing issue)

In our MySQL 5.5 database, we have following InnoDB table with 30M+ rows: +----------------+-------------+-----------------+--------+ | participant_id | question_id | given_answer_id | status | ...
1
vote
2answers
136 views

Improving performance of query with `WHERE <> ''`

I have a qeuery that checks where two columns are not '' (blank) - [blank and NULL are not the same in this case] Is there any way to improve the performance of the query SELECT * FROM myTable WHERE ...
1
vote
1answer
68 views

Does limit impact affected rows or not?

I have this table: CREATE TABLE IF NOT EXISTS `usergroups` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) unsigned NOT NULL, `group_id` smallint(5) unsigned NOT ...
0
votes
0answers
1k views

Oracle merge query vs correlated update

I have a query which updates a table with data from another table. The current query is written as a correlated update with an exists clause to remove unnecessary rows. The problem is since both of ...
1
vote
0answers
125 views

Mysql performance issue when selecting text column

I have a performance problem when selecting rows containing text fields: SELECT id FROM message WHERE NOT(message.sender_id = 368352 AND message.is_deleted_sender = 1) AND ...
3
votes
2answers
426 views

Efficiently query MAX over multiple ranges

When performing a MIN() or MAX() over a single range covered by an appropriately sorted index, SQL Server does a TOP() and so returns the value after fetching just one row. When the search criteria ...
1
vote
2answers
868 views

Improve performance with the WHERE NOT IN sub-select clause

In the following query, I have to count transactions for each customer. [EDIT]However, I have to exclude from the result set entirely, customers that have a transaction older than one year. ...
0
votes
1answer
261 views

T-SQL Gurus, I need help optimising these queries for performance

I would like to have suggestions on improving the performance of some queries. These will be run on SQL Server 2008 R2 against a single table called dbo.bids In a production environment the table ...
5
votes
2answers
2k views

Does a TRIGGER improve the performance?

I understand that TRIGGERs are useful, as they take care of subsequent queries, but do they have an advantage from performance point of view too? Replacing two or three queries as INSERT INTO table1 ...
2
votes
2answers
530 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 = ...
3
votes
1answer
212 views

Optimizing complex join predicates [closed]

Table Definitions CREATE TABLE [dbo].[Pin_Mtg] ( [MtgId] bigint NULL, [CntyCd] char(5) NOT NULL, [BatchDt] int NOT NULL, [BatchSeq] int NOT NULL, [MtgSeq] tinyint NOT NULL, ...
1
vote
1answer
73 views

Query for View with unique entries from Database with repeated entries

I have a table with columns of Hotel names, city name and country name (of the respective hotels). I need to create a view of unique entries which will contain city name and country name columns. Any ...
4
votes
1answer
372 views

performance issue caused by IO?

Just curious to get an issue sanity checked . . . I'm trying to ascertain whether a performance issue experienced by users is the result of the app, server it sits on or SQL Server. Looking at ...
0
votes
2answers
94 views

Understanding multiple indexes with MySQL (and performance)

I have a "phones" table with around 120 000 records, it contains phone_id, phone_number, phone_user_name, phone_last_contacted, etc. The phone_id is index and looks like: 1,2,3,4 ... 120 000. But I ...
0
votes
1answer
49 views

Query Optimization for added value

I have a query that's behaving a bit oddly. In my database I have a table called "records". It tells me a bunch of information about what applications a user ran on my company's machines. I'm ...
0
votes
1answer
56 views

strange “using where” in simple query

I have a table with this structure: mysql> describe user_groups; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | ...
0
votes
1answer
30 views

Condense Query Results with Wildcard

I have the following query: select programVersion, count(programVersion) from records R inner join Programs P on P.programID=R.usageProgramID where programName="word" ...