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

learn more… | top users | synonyms

2
votes
0answers
40 views

Order by index unexpectedly slowing query down

I have two tables: houses has ~415,000 records, and account_houses has ~525,000 records. I've changed the table names for security reasons, but basically I am using this query: SELECT "houses".* FROM ...
7
votes
1answer
87 views

how come a table variable is improving the performance of a query in this circumstance?

for this specific case, that I will try to explain below, using a table variable is performing better than not using a table variable. I would like to know why, and if possible, get rid of the table ...
1
vote
1answer
122 views

placing conditions in a join query join on vs where

Is there and advantage to one of the following options over the other or are they the same ? select * from T1 Join T2 on T2.id=T1.t2_id and T2.field=value; vs. select * from T1 Join T2 ...
0
votes
3answers
50 views

Performance of updating row if content has changed

I'm trying to make a website where there is a form that contains input type fields that are editable. In fact I get the elements from one of my tables (containing an id column and a string column) ...
3
votes
1answer
78 views

Workaround for the limit of the number of missing indexes suggested by SQL Server's DMVs

In production, each instance of SQL Server has over 250 databases (what we call 'OrgDb's). The project I'm currently working on, aims to send all the reported missing indexes by SQL Server's DMVs to ...
1
vote
1answer
154 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 ...
0
votes
2answers
112 views

How to optimize this SQL Server query?

Anyone have any suggestions for how to optimize this query? Running SQL Server 2008.. The object is to look at accounts that were created yesterday, compare their email addresses and phone numbers ...
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
1answer
55 views

slow count on text column

Table t1 with 500k rows, 2TB in size, one text column. SQL Server 2008 R2 SP3. CREATE TABLE [dbo].[t1]( [TId] [int] IDENTITY(1,1) NOT NULL, [Weight] [int] NULL, [TDate] [datetime] NULL, ...
0
votes
1answer
53 views

Searching from Database having billion records, which database should i use [duplicate]

We are having 1 billion phone numbers and we have to search them against numbers which we send for promotion to filter out which are not promotional registered. That is we have lets say 1 million ...
2
votes
1answer
89 views

Optimize performance for query with CTE using a multicolumn index

I've got this slow query: WITH flks AS (SELECT id FROM public.ja_feedlog WHERE gtime BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2016-01-24')::BIGINT AND EXTRACT(EPOCH FROM ...
3
votes
2answers
61 views

Slow MySQL query

I have 3 tables: Name Engine Rows Data Size Index Size Total Size product MyISAM 11.06M 859.59M 861.20M 1.68G product_manufacturer MyISAM 3.09K ...
0
votes
0answers
24 views

MySQL user variables vs repeated parameter literals. - performance

I am currently designing a large query which uses a number of parameters numerous times throughout the query and subqueries. After checking out some posts on multiple repeated params, I decided to ...
2
votes
1answer
107 views

Stored procedure suddenly very slow, updating statistics not helping

Like the title says, I have a stored procedure that suddenly started running very slowly. Previously it took about 5 seconds to execute, now it is taking seven minutes or more. This seems to have ...
0
votes
0answers
37 views

How could I improve speed of fetch in Oracle?

I've got a table of taxi GPS trajectory records with approx 60 million rows, including date, time, ID, longitude, latitude, speed, bearing etc., which were imported from .csv files with setting each ...
3
votes
2answers
261 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
2
votes
1answer
177 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 ...
4
votes
2answers
442 views

Postgres 9.4.4 query takes forever

We're running Postgres 9.4.4 on CentOS 6.5 and have a SELECT query that has worked for years, but stopped working and hangs after we upgraded from 9.2 (it took a while to notice it, so I don't know if ...
3
votes
2answers
47 views

IN Clause - listing values VS query

My app runs a lot of queries like this SELECT ... FROM Table WHERE CategoryID IN (SELECT CategoryID FROM OtherTable WHERE ..) To save performance I decided to cache the second subquery in the ...
3
votes
1answer
66 views

Can this SQL Query be improved

Related to one of my previous questions: How to select a subset from a query result I now have this Query: CREATE TABLE #JourneyLogs ( JourneyStartId INT, JourneyEndId INT, ...
1
vote
1answer
116 views

pt-query-digest results are empty

I have a slow log generated by MySQL 5.1.71-log, that I've copied onto another machine. It appears I'm not getting any results from pt-query-digest. $> pt-query-digest slow.log # 2.1s user time, ...
4
votes
2answers
417 views

What is the most efficient way to count the number of rows in a table?

I am using Postgres with the following query: select count(*) from image; The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app ...
1
vote
1answer
45 views

SQL getting IDs from one table with multiple entries in another table

In my database I have two tables: Persons and PersonAttributes. One Person may have several Attributes associated with it. What I would like to do is get a list of all Persons(/IDs) that have at ...
2
votes
1answer
168 views

Query Time is doubled because of a column in select

I have a Stored Procedure which is calculating some data and inserting it into a Temp Table. Then I am selecting the Data from the Temp Table. Select AssetId = iAssetId, ..... SpeedKM = ...
2
votes
1answer
950 views

query performance gains by removing operator hash match inner join

While trying to apply the contents of this question below, to my own situation, I am a bit confused as how I could get rid of the operator Hash Match (Inner Join) if any way possible. SQL Server ...
31
votes
4answers
17k views

Can a single PostgreSQL query use multiple cores?

In recent versions of PostgreSQL, can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
2
votes
3answers
76 views

Update a column for every row of a large table to a unique value in a set range

We have a SQL Server table with over 3 million rows in it. Each row represents a product. These products are provided by about 500 suppliers, and are updated each night via a file from each supplier. ...
1
vote
1answer
36 views

Query taking more than 5 minutes to execute

I've a query that runs in 2 identical environments: Production and QA. In QA server, it takes around 6 seconds and in production it takes 5 minutes or more! Query is as follows: mysql> SELECT ...
11
votes
3answers
230 views

Why is my EXISTS query doing an index scan instead of an index seek?

I am working on optimizing some queries. For the query below, SET STATISTICS IO ON; DECLARE @OrderStartDate DATETIME2 = '27 feb 2016'; DECLARE @OrderEndDate DATETIME2 = '28 feb 2016'; SELECT ...
0
votes
0answers
23 views

Advice on query optmization - Multiple Unions

I'm trying to understand how this might be better optimized. I currently have these three different queries to union the results of each. The first case finds all records with the where condition of ...
0
votes
1answer
32 views

Is the same thing(internally) putting conditions in a query over a view that directly on the view?

I'm having trouble understanding performance with queries on views. Given this two scenarios, the queries internally will be the same? SCENARIO 1 View: CREATE VIEW TEST.SIMPLEVIEW AS SELECT ...
7
votes
1answer
318 views

Better execution plan if parameter is stored in local variable

I have two stored procedures. This one is incredibly fast (~ 2 seconds) CREATE PROCEDURE [schema].[Test_fast] @week date AS BEGIN declare @myweek date = @week select distinct serial ...
4
votes
1answer
121 views

Efficiently search for a prefix range in SQL Server

Let's say I want all records with a prefix between two user-supplied alphanumeric values of the same length. Thus, if the user enters A010 and A025, I want to return A0101, A0200 and A0259. What I've ...
1
vote
1answer
69 views

Awful performance on joined queries while great separately

I have a simple queries: SELECT t1.[IntField], CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField' , t1.[FloatField1], t1.[FloatField2] , t2.[FloatField3] FROM ( SELECT ...
3
votes
1answer
53 views

Updating multiple columns using Case

I have a query that looks like below: UPDATE some_table SET column_x = CASE WHEN x_specific_condition THEN new_value_for_x ELSE column_x END, column_y = CASE WHEN y_specific_condition THEN ...
6
votes
2answers
240 views

Is there a SQL Server implementation of the Longest Common Substring problem?

Is there a SQL Server implementation of the Longest Common Substring problem? A solution that checks with all rows of a column in SQL Server? I have seen solutions that take two strings as input, but ...
0
votes
1answer
31 views

Which columns to index with LEFT JOIN and GROUP_CONCAT

So i have a complicated MySQL search query, and I wonder which columns I need to index to speed up searching: SELECT wpp.ID, post_title, wp_terms.name AS category, wp_terms.slug AS slug, ...
36
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 ...
2
votes
0answers
44 views

where to add a column with low cardinality, decimal type into an index?

I got a question regarding the best way to put a bad column for an index in an index, so that I can satisfy my query below, and similar others that follow the same pattern. on the query below, all ...
1
vote
3answers
3k views

Increased query_cache_size, Queries slowed drastically on increased traffic

I increased our servers query_cache_size to 2gb, hoping for performance benefits. After sometime the query performance would drastically decrease. Once I dug in deeper, I found the queries in State ...
8
votes
2answers
377 views

Query 100x slower in SQL 2014, Row Count Spool row estimate the culprit?

I have a query that runs in 800 milliseconds in SQL 2012 and takes about 170 seconds in SQL 2014. I think that I've narrowed this down to a poor cardinality estimate for the Row Count Spool operator. ...
3
votes
1answer
4k views

PostgreSQL 9.1 - Query on VIEWS taking a lot of time

Using PostgreSQL 9.1, we are having problems while executing queries on a VIEW of PostgreSQL. Following is the situation: We have a partitioned Table "buz_scdr" over which we have built a VIEW ...
-7
votes
2answers
133 views

Why over 1.3TB desired memory for this query, it should have no memory grant? [closed]

SQL Server version is 2014 Developer SP1. Trace Flags 272, 610, 1118, 1206, 1222, 8048, 9481 are globally turned on. We had to turn on flag 9481 since the new cardinality estimator badly effected ...
6
votes
3answers
340 views

Which is the most efficient way to run a particular select query?

If I run the following code: select PolicyNumber, MAX(decpageid) as decpageid, Risk from StatRiskDecpages where PolicyNumber = 'AR-0000301132-04' group by PolicyNumber, Risk I get the following ...
2
votes
1answer
63 views

Why is index not used for IN expression with unnested array

I have a query that joins two tables but refuses to use use index unless I add a limit. The case is a bit complicated so best described by showing you. I have two tables, users and ...
0
votes
1answer
49 views

getting the total of amount of money of each user between two dates even if no record is found

I am trying to get the total amount of money which the user spent between a certain period. The code that I have come up with can successfully get the result but I also want to show the users who ...
1
vote
1answer
92 views

Slower running Query with better Filter?

mysql> SELECT * FROM history_uint h WHERE h.itemid='1825689' ORDER BY h.clock DESC LIMIT 2 OFFSET 0 ; Empty set (0.00 sec) Explain: id: 1 ...
3
votes
2answers
996 views

How to speed up select distinct?

I have a simple select distinct on some time series data: SELECT DISTINCT user_id FROM events WHERE project_id = 6 AND time > '2015-01-11 8:00:00' AND time < '2015-02-10 8:00:00'; And it ...
0
votes
1answer
113 views

Huge database… queries too slow. How to improve performance? [closed]

I'm currently managing a MySQL database with 30 million rows(many more to come). The entire database is 280GB. The server has 4GB of RAM and 3.2Ghz 4 core processor. The largest column stores an array ...
0
votes
0answers
44 views

Recursive CTE Optomization

I have the following rCTE used in several different queries to determine the requesting user's place in the hierarchy. Buyer is the User who is purchasing. ItemID is the thing that they can ...