Stack Overflow
Stack Overflow

Questions tagged [sql-execution-plan]

A execution, or query plan, is the breakdown of steps the database engine uses to get a result.

A execution, or query plan, is the breakdown of steps the database engine uses to get a result. Specifically it is the plan that the optimizer determines is the best, most efficient, way to get the data.

The execution plan can be analyzed to determine what is happening and how to improve efficiency. Including finding if an index is being scanned or missing an index altogether. There is also many points of analytic data that can be acquired via the plan including things such as estimated and actual number of rows.

1429 questions
40
votes
9 answers

How to improve performance on a clustered index seek

I'm trying to improve the performance on a query that is running very slowly. After going through the Actual Execution Plan; I found that a Clustered Index Seek was taking up 82%. Is there any way for me to improve the performance on an Index…
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
39
votes
2 answers

What does "Recheck Cond" in Explain result mean?

From an example in PostgreSQL document: EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx'; QUERY PLAN ------------------------------------------------------------------------------ Bitmap Heap…
Tim
  • 1
  • 141
  • 372
  • 590
38
votes
2 answers

SQL Server: Table-valued Functions vs. Stored Procedures

I have been doing a lot of reading up on execution plans and the problems of dynamic parameters in stored procedures. I know the suggested solutions for this. My question, though, is everything I have read indicated that SQL Server caches the…
IamIC
  • 17,747
  • 20
  • 91
  • 154
34
votes
3 answers

Measure time of query in Mongo

How can I measure execution time of query in MongoDB ? I found Mongo-hacker plugin but it looks like it measure time of query including time of displaying all results. In PostgreSQL I use Explain Analyze SELECT ... , but i didn't found any…
Алиса Метелева
  • 443
  • 1
  • 4
  • 6
34
votes
5 answers

Reset SQL Server execution plan

I've looked all over for this command....what's the command to reset the SQL Server's execution plan?
Brandon
  • 381
  • 1
  • 4
  • 6
33
votes
6 answers

Keep PostgreSQL from sometimes choosing a bad query plan

I have a strange problem with PostgreSQL performance for a query, using PostgreSQL 8.4.9. This query is selecting a set of points within a 3D volume, using a LEFT OUTER JOIN to add a related ID column where that related ID exists. Small changes in…
Mark Longair
  • 446,582
  • 72
  • 411
  • 327
31
votes
3 answers

Is there a way to force MySQL execution order?

I know I can change the way MySQL executes a query by using the FORCE INDEX (abc) keyword. But is there a way to change the execution order? My query looks like this: SELECT c.* FROM table1 a INNER JOIN table2 b ON a.id = b.table1_id INNER JOIN…
Jürgen Steinblock
  • 30,746
  • 24
  • 119
  • 189
30
votes
1 answer

How to Clear down Query Execution Statistics in SQL Server 2005/2008

Based on getting Query Execution Statistics using this extremely useful piece of SQL obtained from this post Most Executed Stored Procedure - Stack Overflow: SELECT TOP 100 qt.TEXT AS 'SP Name', SUBSTRING(qt.text, qs.statement_start_offset/2,…
Simon Mark Smith
  • 1,130
  • 3
  • 13
  • 19
30
votes
4 answers

How to let SQL Server know not to use Cache in Queries?

Just a general question: Is there a query/command I can pass to SQL Server not to use cache when executing a particularly query? I am looking for a query/command that I can set rather than a configuration setting. Is there no need to do this?
Abs
  • 56,052
  • 101
  • 275
  • 409
28
votes
3 answers

How can I analyse a Sqlite query execution?

I have a Sqlite database which I want to check the indexes are correct. MS SQL Analyser is great at breaking down the query execution and utilised indexes. Is there a similar tool for Sqlite?
Phil Hannent
  • 12,047
  • 17
  • 71
  • 118
25
votes
9 answers

Performance of SQL "EXISTS" usage variants

Is there any difference in the performance of the following three SQL statements? SELECT * FROM tableA WHERE EXISTS (SELECT * FROM tableB WHERE tableA.x = tableB.y) SELECT * FROM tableA WHERE EXISTS (SELECT y FROM tableB WHERE tableA.x =…
Sebastian Dietz
  • 5,587
  • 1
  • 31
  • 39
24
votes
1 answer

SELECT TOP is slow, regardless of ORDER BY

I have a fairly complex query in SQL Server running against a view, in the form: SELECT * FROM myview, foo, bar WHERE shared=1 AND [joins and other stuff] ORDER BY sortcode; The query plan as shown above shows a Sort operation just before…
richardtallent
  • 34,724
  • 14
  • 83
  • 123
24
votes
4 answers

Missing STOPKEY per partition in Oracle plan for paging by local index

There is next partitioned table: CREATE TABLE "ERMB_LOG_TEST_BF"."OUT_SMS"( "TRX_ID" NUMBER(19,0) NOT NULL ENABLE, "CREATE_TS" TIMESTAMP (3) DEFAULT systimestamp NOT NULL ENABLE, /* other fields... */ ) PCTFREE 10 PCTUSED 40 INITRANS 1…
Tsimon Dorakh
  • 356
  • 1
  • 10
23
votes
2 answers

query optimizer operator choice - nested loops vs hash match (or merge)

One of my stored procedures was taking too long execute. Taking a look at query execution plan I was able to locate the operation taking too long. It was a nested loop physical operator that had outer table (65991 rows) and inner table (19223 rows).…
stackoverflowuser
  • 22,212
  • 29
  • 67
  • 92
22
votes
8 answers

Will my index be used if all columns are not used?

I have an index on columns A, B, C, D of table T I have a query that pulls from T with A, B, C in the WHERE clause. Will the index be used or will a separate index be needed that only includes A, B, C?
anon
1
2
3
…
95 96