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

learn more… | top users | synonyms

0
votes
1answer
21 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
64 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 ...
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 ...
0
votes
0answers
23 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
3answers
59 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 ...
4
votes
2answers
131 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 ...
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 ...
6
votes
3answers
284 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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
1
vote
1answer
59 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 ...
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 ...
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 ...
0
votes
1answer
35 views

Joining 4 tables of which one is unrelated

I have 4 tables as follows. I want to find a wholesum resultset, limited to 25 records, in the following way: Users from Table1 based on a condition And those Users should not be in Table2 [Non ...
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
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 ...
0
votes
0answers
51 views

How to optimize this query on oracle?

I have built this query in oracle to get some data i need from the two tables below. At SELECT REGEXP_REPLACE i get the IN_01 as input parameters from the user and it can be much bigger list than ...
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
1answer
42 views

Performance issue using view vs. direct statement

I have a table with structure similar to this: table values ( id1 int, id2 int, value real, value_at timestamp, constraint primary key (id1,id2,value_at) ) I need to get the "current value" for a ...
2
votes
1answer
33 views

How to optimize this query on Postgres

I'm trying to make a relatively simple query, but it's taking much longer than I'd expect. I have an index in place, but it doesn't seem to be helping much. Here's the query. It sometimes takes up to ...
1
vote
0answers
41 views

Slow query - PostgreSQL 9.2

I've got a Query that takes some time to be executed... Can you guys help to improve the total time? Thank you! QUERY: SELECT file.inode_id AS file_id, file.parent_inode_id AS ...
2
votes
2answers
49 views

How to unite 2 subqueries to reduce retrieving data from cumbersome tables?

There are 3 tables: user, conversation, message. Table user: id, login, last_login_time (time of last user's login), ... Table conversation: id, user1_id, user2_id, ...
0
votes
0answers
22 views

In which order does a mongodump --query executes

In which order does a mongodump --query executes, is from last insert to first insert or the other way around, if it is other way around is there any option that I can pass to mongodump --query, so ...
0
votes
0answers
33 views

How to concatenate using cast(collect()) in Oracle DB?

My current query: select distinct mb.BP_TYPE,mb.ABS,mb.BLD_SEQ_CODE, mb.BPCODE||'~'||(select LISTAGG(mbp.PROCEDURE,' ') within group(order by mbp.LINE,mbp.SLN1,mbp.SLN2) from MIS_BS_BB_PROC mbp ...
4
votes
2answers
131 views

slow select query on datetime column

I have a table in MySQL which has approximately 10 million records, and now a query which used to take micro seconds now takes more that 8 seconds to execute! Here is my table structure: CREATE TABLE ...
0
votes
1answer
29 views

New column to a base table

I want to add a new column to a base table, and add that new column to an existing view... So all selects/updates/deletes translate back to the original table How can I do that? Thanks ...
3
votes
1answer
48 views

Query Plan Error

I have an interesting situation. I use SQL2014 Enterprise version with SP1. The following query results an error message : The query processor ran out of internal resources and could not produce ...
-3
votes
0answers
39 views

Procedure taking so much time [closed]

Can anyone help me out. This is first time im working on performance tuning can anyone help me out. If want i can paste the proc also.
6
votes
2answers
207 views

Effect of an index on update statements where update column is not in an index

I constantly see people say that indexes slow down update, delete and insert. This is used as a blanket statement, as if it is an absolute. While tuning my database to improve performance, I keep ...
3
votes
2answers
60 views

SQL Server “fix” query for Hash Match instead of Nested Loops

At the moment I have a view that is running very slow. It takes about 3 minutes to execute. This suddenly happened. To debug what was going on I used an old dataset and compared both queries. On the ...
1
vote
0answers
42 views

Oracle, Cursor_Sharing = Force and Queries with literal comparisons forcing FTS

I have the following scenario: Oracle 11.2.0.3 A 3rd party application, that requires cursor_sharing = TRUE A table (T2310) with a indexed field (C1000000161) that has no duplicated values Result ...
-1
votes
2answers
56 views

SQL query execution differs each and every time in SSMS

I have used below query to fetch maximum of 6000 records in SQL Server 2012. Select A.Date, A.City From dbo.TBL_Auction (NoLock) A Inner Join dbo.TBL_PLACE (NoLock) B On A.Place_Fk_Id = B.Place_Pk_Id ...
3
votes
2answers
56 views

What's the best way to diagram this “relation” looking for performance and|or disk space savings

I am working in a DB diagram and I have a table called cm_identifier_type. Each record of this table can have or not a downloadable file (saved a file path). I have identified two possibles ways to ...
3
votes
1answer
159 views

Do WHERE IN queries reindex the table after each set within the query?

I have a basic WHERE IN query that takes too long (30+ secs) to process. DELETE FROM `table1` WHERE ( djID, localID ) IN (("128","26"),("110","32")) The table I'm querying on has about 20 million ...
4
votes
2answers
60 views

Update fields that contains certain prefix by lookup table

I have a temporary table #NAMEOLD_NAMENEW which contains a column NAMEOLD and a column NAMENEW where every row contains the old and new value of a value to be updated. This table consists of roughly ...