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
9
votes
1 answer

How to view execution plans in SQL Server on Linux

I have SQL Server installed on Linux. It was installed from Microsoft's repos as described here: https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu In MySql I used to write EXPLAIN in front of my query to see the execution…
Gherman
  • 6,768
  • 10
  • 48
  • 75
9
votes
1 answer

Postgresql compare 2 querys for optimization

I just created a couple of queries that bring the same data but in a different. the first one uses a sub query and the second one uses a self join strategy. checking the documentation, i found the ANALYZE and EXPLAIN commands, Now i'm trying to…
OJVM
  • 1,403
  • 1
  • 25
  • 37
9
votes
1 answer

SQL Queries, execution plans and "Parallelism"

So I'm (still) going through some slow legacy sql views used to do calculate some averages and standarddeviations on a (sometimes) large set of data. What I end up with are views joining views joining views etc. So I though I would review the…
Christian Wattengård
  • 5,543
  • 5
  • 30
  • 43
9
votes
3 answers

SQL Server sp_ExecuteSQL and Execution Plans

I have a query which is super fast in SQL Server Management Studio and super slow when run under sp_ExecuteSQL. Is this to do with caching of execution plans not happening when run under sp_ExecuteSQL?
AJM
  • 32,054
  • 48
  • 155
  • 243
9
votes
3 answers

How do I explain a query with parameters in MySQL

I have a query SELECT foo FROM bar WHERE some_column = ? Can I get a explain plan from MySQL without filling in a value for the parameter?
Michael Barker
  • 14,153
  • 4
  • 48
  • 55
9
votes
2 answers

SQL function very slow compared to query without function wrapper

I have this PostgreSQL 9.4 query that runs very fast (~12ms): SELECT auth_web_events.id, auth_web_events.time_stamp, auth_web_events.description, auth_web_events.origin, auth_user.email, customers.name, …
Mmeyer
  • 381
  • 6
  • 14
9
votes
1 answer

Postgres uses wrong index

I've got a query: EXPLAIN ANALYZE SELECT CAST(DATE(associationtime) AS text) AS date , cast(SUM(extract(epoch FROM disassociationtime) - extract(epoch FROM…
Tony
  • 3,605
  • 14
  • 52
  • 84
9
votes
1 answer

How reliable is the cost measurement in PostgreSQL Explain Plan?

The queries are performed on a large table with 11 million rows. I have already performed an ANALYZE on the table prior to the query executions. Query 1: SELECT * FROM accounts t1 LEFT OUTER JOIN accounts t2 ON (t1.account_no = t2.account_no …
ADTC
  • 8,999
  • 5
  • 68
  • 93
9
votes
1 answer

How can LIKE '%...' seek on an index?

I would expect these two SELECTs to have the same execution plan and performance. Since there is a leading wildcard on the LIKE, I expect an index scan. When I run this and look at the plans, the first SELECT behaves as expected (with a scan). But…
blackboxlogic
  • 557
  • 6
  • 13
9
votes
3 answers

hash value for sql statement

When we execute any sql statement in Oracle, a hash value is being assigned to that sql statement and stored into the library cache. So, that later, if another user request the same query, then Oracle find the hash value and execute the same…
Ravi
  • 30,829
  • 42
  • 119
  • 173
8
votes
3 answers

What's the difference between these T-SQL queries using OR?

I use Microsoft SQL Server 2008 (SP1, x64). I have two queries that do the same, or so I think, but they are have completely different query plans and performance. Query 1: SELECT c_pk FROM table_c WHERE c_b_id IN (SELECT b_id FROM table_b WHERE b_z…
Michel de Ruiter
  • 7,131
  • 5
  • 49
  • 74
8
votes
1 answer

DBMS_XPLAN.DISPLAY_CURSOR vs Explain Plan if not using gather_plan_statistics hint

Just requesting some clarification on the difference between the 2. From what I understand, EXPLAIN PLAN gives you the theoretical execution plan while DBMS_XPLAN.DISPLAY_CURSOR gives you the actual execution plan with execution statistics for the…
BYS2
  • 5,199
  • 5
  • 24
  • 32
8
votes
5 answers

Execution plan over query with temp tables

I've a stored procedure that does something like this: SELECT Id INTO #temp FROM table WHERE ... DELETE FROM #temp INNER JOIN table2 ON a=b WHERE ... But it's running slowly. When I try to view the Execution Plan I can't since the SQL Server…
Diego Jancic
  • 7,280
  • 7
  • 52
  • 80
8
votes
1 answer

How can a table scan return more rows than are in the table?

I have a complex query on a database with bad statistics and fragmented indices. What I'm puzzled by is that when I examine an actual query plan I get 54 M rows from a table scan on a table that has 23 K rows. Much further up the query plan this…
PavelR
  • 123
  • 6
8
votes
5 answers

mysql explain different results on different servers, same query, same db

After much work I finally got a rather complicated query to work very smootly and return results very quickly. It was running well on both dev and testing, but now testing has slowed considerably. The explain query which takes 0.06 second on dev…
pedalpete
  • 21,076
  • 45
  • 128
  • 239
1 2 3
…
95 96