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

learn more… | top users | synonyms

0
votes
0answers
27 views

Optimize Query - PostgreSQL 9.2

This is related to my previous Question: slow-query-optimize-postgresql The new Query is good, the missing piece is that the quantity & cost selectors are interchangeable (in code) with: if ...
2
votes
5answers
1k 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
18 views

Query doesn't respond after specific data timestamp [on hold]

I am doing SSRS reports which include multiple queries. Most of the time I am selecting from a view which has three columns: Tag Timestamp Value This view retrieves data from multiple tables ...
0
votes
1answer
27 views

Execution plan not understanding correctly

I am running a query: SELECT * FROM message where fr_user = 1 ORDER BY created DESC LIMIT 10; Without the LIMIT there would be 170 rows returned where fr_user = 1; I am now trying to understand ...
2
votes
2answers
68 views

How to optimize query on MYSQL with multiple group by clause

I have a web application where users can view data of performance. There will be around 200 to 250 users. Each user will have like 100,000 rows of data per day. It is only table and here is ...
4
votes
2answers
132 views

How to optimize this tricky SQL self-join?

I want to perform a complex self-join on a table. I know that this could in theory be done very efficiently (see below), but I have trouble getting SQL (on Microsoft SQL Server) to do so. My question ...
1
vote
2answers
4k 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` ...
0
votes
1answer
114 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
12 views

How to set query_cache_type=2 on mac?

I want to enable query_cache_type and change its value to DEMAND. When i am doing it via terminal its giving this error:- ERROR 1651 (HY000): Query cache is disabled; restart the server with ...
3
votes
1answer
68 views
2
votes
3answers
60 views

Count is slow Oracle

this is my query : SELECT Count(last_upd) FROM S_CONTACT WHERE last_upd_by = '0-1' AND LAST_NAME <> 'Wait' AND last_upd + 1/24 > SYSDATE - (1 / (24 * 60)); It takes 84 seconds to return ...
6
votes
3answers
285 views

The fastest way to get the most recent records [duplicate]

I am looking for the fastest way to return the most recent record for each reference number. I quite liked the solution from BrentOzar.com, but it does not seem to work when I add a third condition ...
0
votes
1answer
67 views

How to optimize database for specific queries?

Hello I have built this queries thanks answers to my questions: How to get data from three different tables and How to show rows with 0 without any records in database But now I have performance ...
3
votes
1answer
80 views

Improve performance of query with multiple group by column and count function in select

Brief Background of issue We have a aggregate table to store activities of competitors and this table have currently 3 Million activities and we need to retrieve data from this table group by ...
3
votes
1answer
30 views

What indexing to be used over string columns(text type) with string length around 3000 characters

I am using open source columnar dbms (postgresql+Cstore). I am trying to speed up the response time of queries. My database has tables with strings mostly. String columns contain huge data (up to ...
33
votes
5answers
4k views

Why does adding a TOP 1 dramatically worsen performance?

I have a fairly simple query SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc JOIN CORRESPONDENCE_JOURNAL cj ...
0
votes
0answers
42 views

Retrieving the latest forecast

I am writing an application which stores weather forecasts for different cities, and each day there is a new forecast for each city. I want to be able to provide daily forecasts, and also the latest ...
1
vote
1answer
139 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 ...
1
vote
1answer
147 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 ...
24
votes
2answers
759 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 ...
4
votes
1answer
56 views

Advice on how to improve the efficiency of a stored procedure using lots of joins

I have a stored procedure that is returning search results for items in a database grouped by customers who own them. The user performing the search is restricted as to what items they can see by ...
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 ...
0
votes
2answers
28 views

Combine multiple row's records in one row with MS Access Query?

I have a table (name- tbl2) with 21 column but I want to combine multiple rows records in a row by using a unique reference number as below: Table Field and Records Type Expected Result: Please ...
4
votes
2answers
70 views

Optimized date compare in WHERE clause, convert or datediff=0?

In my query I am comparing two dates in the WHERE clause once using CONVERT: CONVERT(day,InsertedOn) = CONVERT(day,GETDATE()) and another using DATEDIFF: DATEDIFF(day,InsertedOn,GETDATE()) = 0 ...
7
votes
1answer
162 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 ...
2
votes
3answers
81 views

MySQL query takes over a minute to complete

I am trying to pull calls associated with website visits from our huge database... I have got SQL that works, pulls correct results it just takes over minute to complete... Here is the SQL SELECT ...
1
vote
1answer
213 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning. Actually the tuning of my.cnf is like below: My database engine is MyISAM | Mysql 5.1.73 key_buffer = ...
2
votes
1answer
43 views

How to create an index to be picked by query

I have created multiple indexes one after another, but my query doesn't want to pick them :( CREATE INDEX x ON gan.paid (project_id, country_iso_code, source, date(created_at)); CREATE INDEX x ...
4
votes
3answers
83 views

Make queries and BCP faster on a very large table

This question is somewhat of a continuation to this: Huge data and performance in SQL Server, but different enough to merit its own question. I'm going to copy and paste some of the text from that ...
6
votes
1answer
59 views

Is it useful to split a table into a frequently used and a archive table?

I have a table with 1.000.000+ entries. Only the newest 100.000 entries are used very frequently. The other 90% are used very seldom. Is it useful to split this table into a frequently used table ...
2
votes
1answer
200 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
27 views

PostgreSQL 9.4: Index Only Used For Lower INET Ranges

For some reason queries on the high range of are extremely slow and queries on the low range are extremely fast. My index is an adapted version of the answer in Optimizing queries on a range of ...
1
vote
2answers
49 views

Optimising a query to get rows where x is between pairs of values from another table

PostgreSQL 9.4.5 In my database there are 2 tables, vcfentries and exons. I want to query for rows in vcfentries where pos is between a list of pairs of positions, exonstart and exonend in the ...
1
vote
1answer
245 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, ...
2
votes
1answer
64 views

PostgreSQL 9.4 query seems very slow

I'm experiencing a very long response time for a query returning a relatively small number of rows. It takes over three minutes to return ~1.3 million rows. I would assume it was an indexing problem ...
2
votes
1answer
95 views

Subquery ORDER BY doesn't work on MySQL 5.6, but works on 5.5

I try to join 3 tables (products, prices, categories) together to get a result of latest & lowest prices from the joined tables. I was able to get the expected result on MySQL 5.5, but after ...
0
votes
1answer
33 views

Mysql Slow Log when using UNION ALL?

I have following query coming up in mysql-slow log, however i am not sure how to optimized this query and make it much faster. Any ideas on this ? I have already used indexes in following in the user ...
1
vote
1answer
60 views

Vertica performance on select from view with union

We have a huge number of rows(tens of millions), and for each table we have an associated view that is a select with UNION ALL between that table and several others. Now, we are doing a select from ...
1
vote
3answers
162 views

Improve a query using cursor to SET Based approach

I am still new to Query optimisation, and I have a Stored Procedure which uses a Cursor to go through each row within the Table, and performs the following operations: Calculate the Time Difference ...
2
votes
1answer
56 views

SQL Query for Internal Messaging System

I'm creating an internal messaging system (No SMTP/POP3) and I'm looking for a way to query a user's list of messages. Here's my schema: Messages _MessageID (PK) _MessageParentID (if a reply this ...
2
votes
2answers
808 views

Why does innodb_flush_method = O_DSYNC provide such a performance boost with SAN-backed storage?

I've seen some people suggest that O_DSYNC should be used with a SAN. The MySQL docs have this to say about O_DSYNC in general: In some versions of GNU/Linux and Unix, flushing files to disk with ...
2
votes
1answer
160 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 ...
3
votes
2answers
241 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
5
votes
3answers
355 views

Warning about implicit convert

I have table with 2 name columns: CREATE TABLE Test ( TestID int identity primary key clustered , Name_Eng nvarchar(50) , Name_Nat nvarchar(50) ) Now I need a query to get this Name column ...
2
votes
1answer
712 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 ...
3
votes
2answers
174 views

Any SQL where clause performance similar to where 1=0

(i'm using SQL Server 2008R2 or later) For centralize manage and consistent, there is a view definition as follow: CREATE VIEW view_all_situation ( select null as src, tf.col1, sum(col2) as fee, ...
1
vote
1answer
126 views

intermittent performance issue - query with multiple CTE and trashed execution plan

I'm trying to optimize a SQL query that shows variable performance (less than 2 to more than 15s). Overview This is a ColdFusion website, with a SQL Server 2008 R2 backend The query is part of the ...
1
vote
1answer
47 views

What are the cons of implementing faceted search with WHEREs and JOINs?

I'm implementing a filter search system (I understood today that is called faceted search) for an ecommerce website. The fron end looks something like this: Country: [Select a country] Categories: ...
1
vote
1answer
73 views

How to increase performance of UPDATE of ~ 50.000.000 entries

We have a query that uses a temporary table to update certain columns in the database: CREATE TABLE #NEW_OLD ( NEW nvarchar(20), OLD nvarchar(20) ); INSERT INTO #NEW_OLD VALUES ...
1
vote
0answers
68 views

Performance measuring on complex query

Now that I have overworked a very very complex sql statement by going over all those little parts and investigating on their missing indexes, index design, queries column order, join vs. exists... I ...