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

learn more… | top users | synonyms

6
votes
1answer
71 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
55 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
131 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
36 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
181 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
244 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
188 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
88 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
457 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
38 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
89 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
69 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
68 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 that(...
1
vote
1answer
81 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 ('DOMAIN\...
1
vote
1answer
49 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
39 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
44 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
55 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
55 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
61 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
169 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
33 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
58 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.
7
votes
2answers
1k 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
92 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
71 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
93 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
164 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
89 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 2....
1
vote
1answer
78 views

Decrease Query Execution Time

I have been having issues with 2 queries with query execution times up to 250 seconds for each query. The tables joined have each less than 10,000 rows, with exception to 2 tables that have around 60k ...
2
votes
1answer
40 views

Key/Value table with limiters

I'm sure someone must have hit this or a problem like this before but I'm not finding anything on a google or DBA exchange search. I have a table storing key/value pairs. There are also 2 limiting ...
0
votes
2answers
63 views

Perform join on multiple tables

I have three tables right now in a postgres DB: Artists ID Name Shows ID Date Venue Price Is_sold Ages Time Multi_day Pit Artists_shows ID Show_id ...
3
votes
1answer
55 views

Indices for a join query with datetime range

Given the following query: SELECT DISTINCT COUNT(DISTINCT "patients"."id") FROM "patients" INNER JOIN "transactions" ON "transactions"."patient_data_id" = "patients"."patient_data_id" WHERE "...
3
votes
1answer
62 views

UPDATE on column referenced in sub-query

We have a query that updates a column for all rows matching a condition which involves a subselect on said column, like the following: update CE_WORKSET_READ_ENTRY workset set workSet.MARK = 0, ...
1
vote
1answer
111 views

SELECT Causes Performance Issue [closed]

I have a SQL SELECT statement that has joins on 27 of our warehouse tables. Original statement was taking more than an hour everyday so I decided to optimize it. I created some indexes but still it ...
7
votes
1answer
164 views

Improving a DbGeography query

I am still new to database administration and I'm trying to optimise a search query. I had a query which looked like this and took 5-15 seconds to execute in some cases, and also was causing 100% CPU ...
3
votes
1answer
152 views

Update multiple Ids when SET = (Sql Query)

I want to update multiple records however the value being set is based on the primary key of the given record. This is not an issue with a single record as I can use: UPDATE T1 SET A1 = (SELECT ...
1
vote
0answers
87 views

High load on server because of one query

I have one query running from last stable version of wordpress on causing high load on my centos 6.7 server I enabled slow query log thought /etc/my.cnf file to record which sql cause this problem ...
4
votes
2answers
74 views

Indexed but still slow during query

I have a table that stores REST requests and responses: CREATE TABLE ws_call_logs ( id BIGINT NOT NULL, ws_method_id BIGINT NOT NULL, device_id ...
0
votes
1answer
31 views

Struggling to design addresses table for fast querying

I have two tables: addresses and locations. Locations zipcode city county state Addresses line1 line2 city county state zipcode Posts Id Address_id Body All my users are required to provide at ...
1
vote
1answer
27 views

What are the consequences of enabling slow_query_log and flushing the cache on mysql?

I have a large database that is performing poorly recently. I want to enable the slow_query_log to investigate the queries and flushing the cache but I need to know what are the consequences of doing ...
7
votes
1answer
169 views

Lead/Lag Implementation in SQL Server 2008 R2: Max memory exceeded

Background I'm trying to establish a "visits" sequence, wherein if an animal is detected at essentially the same place (General_Location) it counts as a single visit, but if it goes elsewhere and ...
18
votes
2answers
876 views

Huge data and performance in SQL Server

I've written an application with a SQL Server backend that collects and stores and extremely large amount of records. I've calculated that, at the peak, the average amount of records is somewhere in ...
0
votes
1answer
33 views

Track database name in anemometer queries

Hello i have installed anemometer on our server in order to see the queries that need optimization. From the website hosted a few have a wordpress blog on them so it is hard to guess witch one is ...
0
votes
1answer
64 views

9 Minute Long Running Query [closed]

We recently stood up Documentum. We have a query that runs 9 minutes. We have applied indexes, this is a cotts product, the query runs against a view that the product creates. Below is the syntax: ...
2
votes
2answers
71 views

Optimizing a MySQL “IN” query

I'm trying to optimize a MySQL table for a query of the form: SELECT Col1, Col2 FROM MyTable WHERE Col3 IN('Val1','Val2','Val3',...) I've added an index to the table: ALTER MyTable ADD INDEX(`COL3`...
0
votes
0answers
37 views

Is there something like Query Analyzer for Postgres?

We are running EF6 and I'm trying to find out exactly what query is running against our Postgres database. With Sql Server, I would just fire up Query Analyzer and look at the query there. Is ...
0
votes
1answer
29 views

Can I use the cost of a query to prove its efficiency when there is only a small number of rows?

Can I run EXPLAIN (Oracle) for a query when there is only a few dozen rows in the table and use the cost to prove whether the query is efficient or not ? Can you measure the efficiency of a query ...