Questions tagged [query-optimization]

For questions about making database queries run faster. PLEASE INCLUDE QUERY PLANS, AND TABLE AND INDEX DEFINITIONS in your question. Please use additional tags to specify your query language and server maker. Some of these questions may belong on https://DBA.stackexchange.com, especially if they involve reindexing, query plans, etc.

Please include table and index definitions for all relevant tables in your question. Please also include the query plan: the EXPLAIN output. Read on for more explanation.

If you use this is a good guide to pulling together the right information for your question.

SQL is a declarative language that can have many ways to find the same data. As a SQL programmer, you declare what you want from it, and it figures out how to get your result. Because of how databases are structured, and how they retrieve data, the same results can be retrieved in various ways that the query interpreter needs to choose between.

Using sargable predicates, using indexes, and optimizing how the query works can make it run more quickly. Restructuring the query, removing unnecessary portions of the query, or using different features of different Database Engines can all be helpful.

If the question is not (just) about how to write the query, but involves how to create new indexes, or how to optimize the server, you may get good answers on https://DBA.stackexchange.com.

Your database's query planner, also known as its query optimizer module attempts to determine the most efficient way to execute a given query by considering possible query plans.

You'll hear a lot about query planners in this tag. Each DBMS software product has its own query planner. Here's a description of one query planner.

How to ask a good query-optimization question

If you're asking "Why is my SQL query so slow?" or something like that, you need to do a little extra homework to get good answers from the Stack Overflow community. Please include this information.

What database and version?

Put this tag on your question along with the tag for your database: , . , or whatever database you use. Don't use more than one database tag, please. Optimization works somewhat differently from database to database. And, please mention the version of your database server.

How many rows and how fast?

Please figure out approximately how many rows you expect in your result set, and approximately how many rows are in each table involved with the query. Mention those numbers in your question. Please mention how long you hope your query needs to take, and how long it's taking. (That is, tell us what you mean by slow).

Don't oversimplify

Don't try to simplify your question by replacing your actual SELECT column lists with *. Using * in SELECT clauses can be harmful to query performance compared to specifying the list of columns you need.

Show your table definitions, with indexes

In your question include your table definitions and index definitions for each table and view involved in your query. Make sure your question shows all indexes in all relevant tables. When you're asking a performance question, please don't oversimplify your question by omitting indexes or columns you think might be irrelevant. The strangest things can be relevant to performance!

Very important: the names and data types of columns included in each index, with their order.

Show your database's explanation of how it satisfied your query

In your question please show the EXPLAIN PLAN output (it's just EXPLAIN or SHOWPLAN in some table server makes and models) for your query.

Things to know

  • Creating an appropriate index for a slow query is often, not always, a good solution to a problem. Answers on will often contain a suggestion for an index.

  • Avoid creating indexes on lots of single columns. Single-column indexes are often not very useful to speed up complex queries.

  • As your database grows you will, most likely, discover new slow queries. Operations can be trivial on hundreds of rows, but much more time-consuming on tens of thousands of rows. In a growing database, plan to review performance issues routinely.

  • If you use SSMS -- Microsoft's SQL Server Management Studio -- this tip is for you: Right-click in a query window, then select Show Actual Execution Plan, then run the query. The execution plan display sometimes recommends a new index to create.

Things you might do when optimizing queries

  1. Join ordering
  2. Query planning for nested SQL queries
  3. Cost estimation
  4. Parametric Query Optimization
  5. Multi-Objective Query Optimization
  6. Multi-Objective Parametric Query Optimization

Reference

Please be aware that query performance optimization is an inexact science. This is a good reference. http://use-the-index-luke.com/

7749 questions
2
votes
1 answer

Why cursor.skip() is sometimes 3 times slower if all-keys are indexed?

I asked Is cursor.skip() on indexed keys always faster? Now I am trying to leverage the multi-key indexing (page 94) to perform faster queries. Used this script to create 2 databases: var a = 0; while(a++ < 4096*2){ var…
0x90
  • 39,472
  • 36
  • 165
  • 245
2
votes
2 answers

Need help to optimize Oracle query in 12c

This particular query was working fine till we were in 11i but as soon as we switched to 12c the query has been doing really bad. I tried to remove the hint and it seems to be doing little better but our DBA wants us to remove all the multiple…
Ditty
  • 521
  • 7
  • 24
2
votes
2 answers

Query optimization possibilities?

I've embarked on attempting to optimize a rather large query that has 3 nested subqueries (like Russian dolls). The query itself is generated by south from a Django project, and I freely admit I'm no expert at SQL optimization. My strategy thus far…
neezer
  • 19,720
  • 33
  • 121
  • 220
2
votes
2 answers

possible ways of profiling an asp.net website in a production server?

I have an asp.net website up and running in my production server. I want to get the possible ways of profiling an asp.net website in a production server because my application is really slow? As i say slow i don't mean the delivery of static content…
ACP
  • 34,682
  • 100
  • 231
  • 371
2
votes
4 answers

How to handle massive data query and control the time within 1 sec?

I am thinking through a problem, if I get a table, and the data in it keep growing, thousand, million, billion .... One day, I think even a simple query it will need several seconds to run. So is there any means which we can use to control the time…
MemoryLeak
  • 7,322
  • 23
  • 90
  • 133
2
votes
10 answers

How can I handle the time consuming SQL?

We have a table with 6 million records, and then we have a SQL which need around 7 minutes to query the result. I think the SQL cannot be optimized any more. The query time causes our weblogic to throw the max stuck thread exception. Is there any…
MemoryLeak
  • 7,322
  • 23
  • 90
  • 133
2
votes
3 answers

MySql - Further Query optimization SELECT WHERE IN

I am currently working on a financial application. A MYSql Database with 8 columns. 1 Million records. INSERTS : 30 rows per minute. UPDATES : 0. SELECTS : A query that runs once every 200ms. Returns around 200,000 records for each…
2
votes
1 answer

ORA-29494: invalid state for run task error with DBMS_PARALLEL_EXECUTE

I am running a PL/SQL procedure with DBMS_PARALLEL_EXECUTE. I get stuck with ORA-29494: invalid state for run task error. My code is as below: Below is the sample output for the DBMS_OUTPUT statement: PROCESSING 5743 PROCESSING 5744 When I…
2
votes
1 answer

How to avoid " Using index; Using temporary; Using filesort " in MySQL, 21 table JOIN

mysql Ver 14.14 Distrib 5.1.58, for redhat-linux-gnu (x86_64) using readline 5.1 I am taking over an old project. I have been asked to speed things up. I have by enabling the slow-query-log. I am examining the queries that appear in the…
lorm
  • 3,141
  • 3
  • 15
  • 20
2
votes
3 answers

Distinct and Inner join when millions of records exists

I have my SQL Query is like this INSERT INTO staging.lps_data ( col1 ,col2 ,col3 ,col4 ,col5 ) SELECT DISTINCT col1 ,col2 ,col3 …
2
votes
1 answer

Optimizing Stored Procedure by eliminating redundant 'SELECT' statement/query

I've below Stored Procedure named GetFilteredArticles which returns 7 seperate tables for each day of a week i.e. Monday to Sunday CREATE Procedure [dbo].[GetFilteredArticles] …
user5426326
2
votes
1 answer

SQL deadlocks - high traffic

I have a stored procedure that takes about 15 seconds to execute. When hundreds of requests come in, we are seeing unacceptable page load times. Sometimes two minutes. The page loads results based on an ID, so it's not the same results for…
Induster
  • 733
  • 1
  • 6
  • 15
2
votes
4 answers

How can I optimise this query in MYSQL? What needs to be done

Please scroll down to the "25/08/2010 Update". I have a query which I have turned into a view. It runs very slowly because (as far as I understand) there are several issues with indexes on the joined tables. explain select * from…
Jason
  • 15,064
  • 15
  • 65
  • 105
2
votes
2 answers

Optimising SQL involving IF EXISTS

I am trying to keep track of whether I need to update certain tables. I have an Events table which I am wanting to monitor for changes and have another table, called DictionaryRefresh, which keeps track of the changes made to that table. If the…
Simon Woods
  • 905
  • 1
  • 6
  • 13
2
votes
2 answers

Optimize query with two "in" clauses

I've trying to optimize a query on MySQL that takes around 15-20 seconds to run. My Data table has about 10M rows, and the query is trying to return 68,000 records which match 144 "run" fields and 35 "name" fields. Because the query is using two…
Will Glass
  • 4,800
  • 6
  • 34
  • 44