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
7
votes
0 answers

How can i use a TVP in an EF6 query? (not a stored proc)

I'm having SQL performance issues on Azure with complex EF6 queries. The environment is configured as an elastic pool. In the development area, the eDTU limit is hit frequently via CPU usage which I have attributed to query plan generation. The…
Tristan
  • 165
  • 6
7
votes
1 answer

Nested loops in MySQL

In this O'Reilly presentation, there is a paragraph introducing some key concepts for understanding MySQL's EXPLAIN: What is a JOIN? Everything is a JOIN, because MySQL always uses nested-loops Even a single-table SELECT or a UNION or a…
RADA
  • 455
  • 5
  • 14
7
votes
2 answers

Checking variable for NULL kills performance

I have following queries: DECLARE @application_number CHAR(8)= '37832904'; SELECT la.LEASE_NUMBER AS lease_number, la.[LEASE_APPLICATION] AS application_number, tnu.[FOLLOWUP_CODE] AS note_type_code -- catch codes not in codes table FROM…
Dmitrij Kultasev
  • 5,447
  • 5
  • 44
  • 88
7
votes
3 answers

Why would MySQL use index intersection instead of combined index?

From time to time I encounter a strange MySQL behavior. Let's assume I have indexes (type, rel, created), (type), (rel). The best choice for a query like this one: SELECT id FROM tbl WHERE rel = 3 AND type = 3 ORDER BY created; would be to use…
Ivan Virabyan
  • 1,666
  • 2
  • 19
  • 25
7
votes
1 answer

Combining datasets with EXCEPT versus checking on IS NULL in a LEFT JOIN

I'm currently working my way through the Microsoft SQL Server 2008 - Database Development (MCTS Exam 70-433) certification. In one of the earlier chapters on Combining Datasets, I came across the EXCEPT (and INTERSECT) commands. One example shows…
Julian
  • 20,008
  • 17
  • 77
  • 108
7
votes
2 answers

Different execution plan when executing statement directly and from stored procedure

While developing a new query at work I wrote it and profiled it in SQL Query Analyzer. The query was performing really good without any table scans but when I encapsulated it within a stored procedure the performance was horrible. When I looked at…
Markus Olsson
  • 22,402
  • 9
  • 55
  • 62
7
votes
5 answers

What is the "filtered" column in MySQL EXPLAIN telling me, and how can I make use of it?

The MySQL 5.7 documentation states: The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the…
Michael Moussa
  • 4,207
  • 5
  • 35
  • 53
7
votes
1 answer

Tools for visualizing mysql explain

I know that query execution plan visualizer exists. But never see it for mysql. Do you know one?
Andrew
  • 8,330
  • 11
  • 45
  • 78
7
votes
4 answers

Order of execution of ORDER BY and LIMIT in a MySQL query

I have a query like this where I want to display sorted records in a paginated format. This is my query. SELECT * FROM answers WHERE userid = '10' ORDER BY votes LIMIT 10, 20 The two arguments to LIMIT will be used for generating page-by-page…
vikmalhotra
  • 9,981
  • 20
  • 97
  • 137
7
votes
2 answers

What's this column [Bmk1002] in the table scan operator of my execution plan?

I have a simple script. IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers; GO CREATE TABLE dbo.Customers ( custid INT NOT NULL, companyname VARCHAR(125) NOT NULL, phone VARCHAR(120) NOT NULL, address VARCHAR(150) NOT…
Just a learner
  • 26,690
  • 50
  • 155
  • 234
7
votes
2 answers

Postgres EXPLAIN ANALYZE is much faster than running the query normally

I'm trying to optimise a PostgreSQL 8.4 query. After greatly simplifying the original query, trying to figure out what's making it choose a bad query plan, I got to the point where running the query under EXPLAIN ANALYZE takes only 0.5s, while…
EMP
  • 59,148
  • 53
  • 164
  • 220
7
votes
3 answers

Are execution plan for functions cached in SQL server?

Can any body help me in understanding if the execution plan for functions cached in SQL server? Any online resource for this?
Ashwani K
  • 7,880
  • 19
  • 63
  • 102
7
votes
3 answers

SQL Server - why is scanning done twice for the same table?

Does anyone know why sql server chooses to query the table 'building' twice? Is there any explanation? Can it be done with only one table seek? This is the code sample: DECLARE @id1stBuild INT = 1 ,@number1stBuild INT = 2 ,@idLastBuild INT =…
Emarian
  • 73
  • 3
7
votes
2 answers

Does PostgreSQL cache execution plan of a view

Does PostgreSQL cache execution plan of a view, as it does for stored procedures?
Mariola Bączek
  • 83
  • 1
  • 3
7
votes
6 answers

SQL Query execution shortcut OR logic?

I have three tables: SmallTable (id int, flag1 bit, flag2 bit) JoinTable (SmallTableID int, BigTableID int) BigTable (id int, text1 nvarchar(100), otherstuff...) SmallTable has, at most, a few dozen records. BigTable has a few million, and…
richardtallent
  • 34,724
  • 14
  • 83
  • 123
1 2 3
…
95 96