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

learn more… | top users | synonyms

3
votes
2answers
321 views

Sudden Query Performance Degradation On A Linked Server

I have an application that uses a view on a SQL Server 2008 R2 instance. This view actually queries a linked server (SQL Server 2008) that has replicated data from our main ERP database. Yesterday, we ...
3
votes
1answer
1k views

MYSQL Slow SELECT with multiple joins and ORDER BY

I have this query SELECT r0_.id AS id0, r0_.deleted_at AS deleted_at1, r0_.updated_at AS updated_at2, r0_.created_at AS created_at3, r0_.is_vehicle_offer AS is_vehicle_offer4, ...
3
votes
1answer
111 views

Query looks optimal but logical reads is huge

I run the following (really simple) query in SQL Server: SELECT MAX(PK_Field1) FROM MainTable kh WHERE kh.PK_Field1 >= '2014-12-01T00:00:00' AND kh.PK_Field2 = 1572 AND ...
3
votes
3answers
308 views

Slow SQL Server authentication, but fast SAN, fast querying?

I am asking you after days of research but I think I don't have required skills to understand what is happening. I have a slow authentication on a SQL Server 2008R2, which leads to a timeout for the ...
3
votes
1answer
2k views

Is there any difference on performance when choosing DISTINCT or GROUP BY to bring distinct values?

I would like to know if there is any difference concerning performance when choosing DISTINCT or GROUP BY to bring distinct rows from a query. I've tried comparing the execution plans, but they seem ...
3
votes
2answers
199 views

Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

I have a poorly performing query: SELECT user_id, count(item_id) as count FROM table items WHERE category = 'magazine' AND created_at > 1384754400 GROUP BY user_id ORDER BY count(item_id) desc ...
3
votes
1answer
514 views

Need help with long running query

UPDATE: Adding a clustered index to each work table covering all fields reduced the query run times to su-bsecond. I consider this question closed. Thanks for taking the time to read this post - ...
3
votes
2answers
84 views

Looking for a possible multi-column index

I have a table highscore containing the columns: game (text) date (timestamp) score (integer) more irrelevant ones... The query most often run on it is: SELECT * FROM highscore WHERE game = ...
3
votes
1answer
819 views

MySQL Handler_read_rnd_next == tablescans without indexes?

I am in the process of tuning our Percona 5.5 server, and have been using percona's tools to plot our database's performance. In the MySQL Handlers Plot, i have been noticing alot of ...
3
votes
1answer
1k views

MySQL 5.5.8 InnoDB Foreign Key, JOIN performance

We are using aws/rds, MySQL 5.5.8. all InnoDB. For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; ...
3
votes
1answer
137 views

How to search for an ID and its descendants in this query?

I have the following tables: Project Unit ------------- --------------- ID | IDUnit ID | IdParent I have to select all projects that have the IDUnit ...
3
votes
0answers
76 views

query optimization of a left join

I have the following query written into the slow log (mysql-slow.log). I don't have much idea how to optimize this query. I already have indexes for: mode, f.user_id... etc but doesn't seem to ...
3
votes
0answers
41 views

Efficiently computing aggregate functions over subqueries with incremental data

I have a PostgreSQL database (version 9.3.6) containing a large number of orders. As the orders are processed, scan_events are triggered and stored, with multiple events per order. Scan events have a ...
3
votes
1answer
74 views

Problem with clustered index? Or bad indexes? (SQL Server 2008 R2)

I have this query (it was running in 2 minutes). With new indexes, it takes 40 sec: This is the execution plan: [![100% use][1]][1] [![it's not heavy][2]][2] [![not at all][3]][3] I can't ...
3
votes
0answers
68 views

Tagged questions

I have similar to StackExchange network that utilises tagged questions. In my web application Users also can find questions by tags. The query below returns a set of tagged questions: SELECT ...
3
votes
2answers
662 views

Query planner thinks doing a sequential scan on a very large table is faster than using an index?

I've got a query that selects the most favorited photo for each of the last 12 editions for a particular domain (this is just a segment of a larger query, but the problem seems to stem from here): ...
3
votes
1answer
174 views

MySQL table architecture

Background information: I have a table containing upload information, every day I get more and more uploads but most of the queries I run center around information gathered within the last 12 months; ...
3
votes
3answers
1k views

PostgreSQL 8.3: Slow GROUP BY on large table

I have a table with about 10 million records. I want to do a simple group by, but it's using a sequential scan and is slow... select run_id, count(*) from result group by run_id; I have an index ...
3
votes
1answer
1k views

Mysql - show results from 3 tables excluding NULL or EMPTY values for certain fields

I have 3 simple tables with polls, users and votes. I'd like to show all polls ordered by clean count of votes (sometimes either poll_id or user_id don't get inserted). Furthermore I'd like to show ...
3
votes
2answers
3k views

MySQL COUNT Query taking too long time to execute

I have below query that takes 7 secs to run SELECT COUNT(*) AS `count` FROM `yuldi`.`businesses` AS `Business` LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory` ON ...
2
votes
2answers
237 views

DISTINCT not using an index [duplicate]

I have table (2139868 rows): COLUMN1 NUMBER(20,0) COLUMN2 XMLTYPE COLUMN3 XMLTYPE COLUMN4 VARCHAR2(50 BYTE) COLUMN5 VARCHAR2(50 BYTE) COLUMN6 VARCHAR2(50 BYTE) COLUMN7 VARCHAR2(50 BYTE) COLUMN8 ...
2
votes
4answers
2k views

Remove sub-string from WHERE clause

I have a stored procedure that is using the SUBSTRING function in the WHERE clause. SELECT DISTINCT ColumnBB, ColumnCC FROM TableAA WITH (NOLOCK) WHERE SUBSTRING(ColumnAA, 1, 17) = @VariableA; ...
2
votes
2answers
242 views

Why the WHERE clause is not pushed down in the view's query?

With Postgres 9.4, I'm doing the following query quite often: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE timestamp BETWEEN timeA ...
2
votes
1answer
485 views

Performance of query with complex where clause on 10M rows

We have a query that destroys our production server when run. It is part of a reporting functionality and the bad part looks as follows: SELECT DISTINCT mt.ID AS ID FROM ...
2
votes
2answers
340 views

What can be done to further enhance performance of Multiple Join and Aggregate Queries?

I have a typical star schema simulated here, and I am mentioning two queries: first query simply joins the fact table with 2 dimension tables and 1 calendar table, and the second query joins and ...
2
votes
2answers
2k views

Get table name from union query?

This is my query SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1 union SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 ...
2
votes
3answers
384 views

selecting multiple instances of a record based on record lifespan over years

I have a table of items that have a origin date and a life span. I want to create a procedure that selects all of the items that will need to be replaced in a specific duration. I feel like there is a ...
2
votes
2answers
100 views

which option is better for not slow down the performance of queries?

I am developing a system that records customer payments, when a customer makes a payment, the system must update the account and show the payment made and all previous payments, is here when the ...
2
votes
1answer
230 views

Why is the following query so bad performance-wise

After asking this question, I got to thinking about why a certain query was doing all the problems. In short, there was a query which took 500ms, I ran some execution plans and applied the ...
2
votes
4answers
155 views

Finding all date ranges that overlap a target date range

Say I wish to store when staff are on holiday (FromDate, ToDate) and then I wish to find all staff that are on holiday between two given dates (QFromDate, QToDate). Now assume that I have many such ...
2
votes
2answers
9k views

Get username and/or IP address responsible for a query

I use the following query to find performance improvements in queries: SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN ...
2
votes
2answers
2k views

Update performance: clustered versus covering index

I have a simple, 3-column table with about 50 million rows in it. This table gets about 5,000 inserts/updates per second, and perhaps 20 queries per second are executed against the table. The table ...
2
votes
3answers
424 views

mariadb ignoring force index

I'm trying to tune a relatively simple query: select bt.col1,bt.col2 from bigtable bt join smalltable st on bt.smalltable_id = st.smalltable_id where st.name = 'some name occuring only once in st' ...
2
votes
1answer
369 views

Does SQL check each And condition if first condition fails? [duplicate]

For a query of below kind does sql process data for each And condition if first And condition fails? If Emp_ID is not 12 for a row then does it check condition of Emp_Name? So how SQL process this ...
2
votes
2answers
87 views

Comparing Queries

In SQL Server, if you're comparing the statistics IO from two queries, how do you ensure that caching doesn't become a factor? For example (I'm making this up as I type, not a real example): SELECT ...
2
votes
2answers
692 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 = ...
2
votes
2answers
254 views

Changing ORDER BY direction causes performance issue

I use MySQL 5.6, and I have two tables each with 16M rows: CREATE TABLE IF NOT EXISTS `newsstudios` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, ...
2
votes
1answer
306 views

Optimize simple query in SQL Server

This should be a quite easy query, but I honestly think its execution time can be improved. select idTag,MAX(pctimestamp) AS PCTIMESTAMP,getdate() AS NOW, datediff(SECOND,MAX(pctimestamp),getdate()) ...
2
votes
2answers
4k views

Will altering order of columns in GROUP BY affect performance?

I have a SQL query like this: SELECT A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END), COUNT(D) FROM SomeTable GROUP BY A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END) On a huge dataset the actual execution plan ...
2
votes
1answer
167 views

Why is this second query more expensive than the first?

While reading Graph Databases book, I came across this excerpt: I can't figure out why the second query is more expensive than the first. Indeed, both filter on the Bob person. May anyone ...
2
votes
2answers
565 views

Query hangs forever at first but works after one day

I have two schemas namely A and B. I regularly copy contents of 10 tables from A to B. Here's is how I do it First I rename the original tables in schema B Then, using SQL Developer I copy the table ...
2
votes
2answers
824 views

Improve CTE performance

is there a way to improve the performance of a recursive CTE like below, I am unsure if I can add indexes on joins when the joins are using ROW_NUMBER? DECLARE @File_Name VARCHAR(8000), @Disk ...
2
votes
2answers
1k views

Why does my query run faster after I force full table scan?

I have a MERGE statement that took forever to execute the select as the table it is running from (SUB_DATA) is rather big (read 1.4 Tb, 700 million rows). My SELECT looks like this. SELECT ...
2
votes
1answer
168 views

Need some help with this query get all status and their associated comments of me and all my friends

I've several days dealing with this query but can't get it to works as I want. This post is related to this one Several values from different tables in which I tough found the solution but I was ...
2
votes
2answers
1k views

How expensive are SELECT FROM LIKE statements from a design perspective?

I have a situation running out of control regarding an ever increasing number of small static files I need to access randomly in a large directory structure. I MUST reduce the number of those files ...
2
votes
3answers
3k views

Is it normal to use many Triggers?

I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
2
votes
2answers
119 views

MySQL taking too much CPU

Below is my .cnf file and if I see my CPU consumption 99.99% of it my the mysqld commands. The MySQL server is connected from remote machines that update data in it frequently, but I make sure that ...
2
votes
2answers
42 views

Is it a better idea (performance is key) to store in a normalized table and retrieve by JOIN or in a single nvarchar column and retrieve using LIKE?

NOTE: The application is not really about websites or keywords, I am using that here only to provide reference points for discussion. I have a data set that is really just a string of words. Think of ...
2
votes
1answer
92 views

Indexed View - how to verify if it is worthy?

I have the following indexed view: CREATE view VProcessStatus WITH SCHEMABINDING AS select processstatus, StatusCount = COUNT_BIG(*) FROM DBO.tbltaxtransaction GROUP BY ...
2
votes
1answer
2k views

MySQL SUM with multiple GROUP BY on same table

What is the best way to produce several sum results based on different group by conditions on the same table in MySQL? I believe I'm doing a bad select, but I can't figure out a better way. Imagine ...