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
6
votes
4 answers

SQL Server query plan differences

I'm having trouble understanding the behavior of the estimated query plans for my statement in SQL Server when a change from a parameterized query to a non-parameterized query. I have the following query: DECLARE @p0 UniqueIdentifier =…
soren.enemaerke
  • 4,770
  • 5
  • 53
  • 80
6
votes
2 answers

Why do I get "The log file for database 'tempdb' is full"

Let we have a table of payments having 35 columns with a primary key (autoinc bigint) and 3 non-clustered, non-unique indeces (each on one int column). Among the table's columns we have two datetime fields: payment_date datetime NOT NULL edit_date…
horgh
  • 17,918
  • 22
  • 68
  • 123
6
votes
3 answers

Postgres query is very slow when using a parameter instead of an hardcoded string

I am running into this Postgres issue where the same query takes a long time to execute if I use a parameter vs hardcoding its value on the query string. The column name is 'media_type' and it's a VARCHAR(20). I am running these queries from PHP,…
luis
  • 1,849
  • 3
  • 16
  • 17
6
votes
2 answers

SQL Execution Plan shows an "Actual Number of Rows" which is larger than the table size

I have an execution plan for a fairly complex join which shows an index seek being performed on a table with the "Actual Number of Rows" reading ~70,000, when there are in fact only ~600 rows in the table in total (the estimated number of rows is…
Justin
  • 84,773
  • 49
  • 224
  • 367
5
votes
2 answers

Oracle foreign key execution plan?

Consider the following (simplistic) situation: CREATE TABLE PARENT ( PARENT_ID INT PRIMARY KEY ); CREATE TABLE CHILD ( CHILD_ID INT PRIMARY KEY, PARENT_ID INT NOT NULL, FOREIGN KEY (PARENT_ID) REFERENCES PARENT (PARENT_ID) ); There…
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
5
votes
1 answer

How to find out the temp space that will be required by a sql query on a database?

we have customer who faced an issue with some inconsistent data and we gave them a query to fix it. Now before running the query, the customers asks me for a query that will give the temp space required for running that query. This is actually…
MozenRath
  • 9,652
  • 13
  • 61
  • 104
5
votes
2 answers

How can I make MS-Access choose a different/the right execution plan for my query

I have a problem with a relatively simple query and the execution plan Access choose for it. The query is of this form SELECT somethings FROM A INNER JOIN (B INNER JOIN (C INNER JOIN D ON ...) ON ...) ON ... WHERE A.primaryKey= 1 AND D.d = 2; C and…
Mathieu Pagé
  • 10,764
  • 13
  • 48
  • 71
5
votes
2 answers

Need to Know about query execution plan

Is there any way to view the mysql/oracle query execution plan as like java debugging. i want to know how mysql/oracle executes our query and what are the steps involved in execution.
LOURDHU KUMAR
  • 91
  • 1
  • 7
5
votes
3 answers

Simple SQL to check if parent has any child rows or not

I show a grid with parent data and need to show icon if it has a relevant child row(s) exist. My DB is in SQL Server 2008. Let me simplify, I've the following two tables - Order (PK : ID) File (PK: FileID, FK: OrderID) An Order can have zero or…
Hemant Tank
  • 1,724
  • 4
  • 28
  • 56
5
votes
2 answers

SQLite: Downsides of ANALYZE

Does the ANALYZE command have any downsides (except a slighty larger db)? If not, why is not executed by default?
Maestro
  • 9,046
  • 15
  • 83
  • 116
5
votes
4 answers

Why does SQL Server use a non-clustered index over the clustered PK in a "select *" operation?

I've got a very simple table which stores Titles for people ("Mr", "Mrs", etc). Here's a brief version of what I'm doing (using a temporary table in this example, but the results are the same): create table #titles ( t_id tinyint not null…
gerrod
  • 6,119
  • 6
  • 33
  • 45
5
votes
1 answer

How to force evaluation of subquery before joining / pushing down to foreign server

Suppose I want to query a big table with a few WHERE filters. I am using Postgres 11 and a foreign table; foreign data wrapper (FDW) is clickhouse_fdw. But I am also interested in a general solution. I can do so as follows: SELECT id,c1,c2,c3 from…
ctlaltdefeat
  • 778
  • 1
  • 6
  • 12
5
votes
3 answers

User-Defined Functions - Are they poor coding practice?

I'm writing reports with fairly complex datasets, lots of joins. To simplify matters, and because I'm basically an OO developer, I've been writing little (usually scalar) functions to do the work that could be done by joining onto a subquery. This…
immutabl
  • 6,857
  • 13
  • 45
  • 76
5
votes
1 answer

Why does auto_explain only log nested statements on first call of PL/pgSQL function?

On my Postgres server I am using the auto_explain module with log_nested_statements on to log other function calls in PL/pgSQL functions. 594 session_preload_libraries = 'auto_explain' 595 596 auto_explain.log_min_duration = 0 597…
aerust
  • 53
  • 2
5
votes
1 answer

How can I display the Executing Query % which is enabled by Live Query Statistics in MSSQL in a windows form using C#?

In SSMS, when I enable the Live Query Statistics, I can see the execution percentage on the bottom left of the window. I want to display this incrementing percentage to the end user on a windows form. So far, I tried to implement this using…
user2663530
1 2 3
…
95 96