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
5
votes
3 answers

Execution plan oddity after re-enabling foreign key constraint

I have a weird problem where after setting nocheck on a foreign constraint and re-enabling it, I am getting a same out-dated execution plan that was used with nocheck on. Why would SQL server generate an execution plan as if foreign constraint…
dance2die
  • 35,807
  • 39
  • 131
  • 194
4
votes
1 answer

SQL Table Split-Is it necessary

I've a table with around 6-7lacs records and it's going to grow as time passes.It has around 16-20 columns in it. There are no one-many relationship to any of these columns. User data entries are stored in these table. So would it be feasible to…
Ninad Ajnikar
  • 183
  • 1
  • 11
4
votes
4 answers

How accurate is Oracle's EXPLAIN PLAN?

Are there any good ways to objectively measure a query's performance in Oracle 10g? There's one particular query that I've been tuning for a few days. I've gotten a version that seems to be running faster (at least based on my initial tests), but…
Jason Baker
  • 192,085
  • 135
  • 376
  • 510
4
votes
2 answers

ADO.NET Commands and SQL query plans

I've been reading up on query plans and how to minimise duplicate plans being created by SQL Server for the same basic query. For example, if I understand correctly, sending queries with inline values may result in 2 different query plans.…
ingredient_15939
  • 3,022
  • 7
  • 35
  • 55
4
votes
1 answer

SQL Server pick random (or first) value with aggregation

How can I get SQL Server to return the first value (any one, I don't care, it just needs to be fast) it comes across when aggregating? For example, let's say I have: ID Group 1 A 2 A 3 A 4 B 5 B and I need to get…
Karl
  • 5,573
  • 8
  • 50
  • 73
4
votes
2 answers

Preferred method of T-SQL if condition to improve query plan re-use

I want to understand which is the better method of implementing a "IF" condition inside a stored procedure. I have seen this method used extensively. Which is comparable to iterative coding... declare @boolExpression bit = 1 --True if…
sqlconsumer.net
  • 455
  • 3
  • 10
4
votes
1 answer

Why there are both filter and access predicates on the same index on this execution plan?

Considering the execution plan for this query : SQL_ID 1m5r644say02b, child number 0 ------------------------------------- select * from hr.employees where department_id = 80 intersect select * from hr.employees where first_name like 'A%' Plan…
oramas
  • 881
  • 7
  • 12
4
votes
2 answers

How to use ts_headline() in PostgreSQL while doing efficient full-text search? Comparing two query plans

I am experimenting with a full-text search system over my PostgreSQL database, where I am using tsvectors with ts_rank() to pull out relevant items to a user search query. In general this works really fantastic as a simple solution (i.e. no major…
Jason
  • 14,517
  • 25
  • 92
  • 153
4
votes
2 answers

How to fix PostgreSQL generic plan estimate for any($1) array parameter?

I've run into an issue where PostgreSQL (13.1 on windows, default config) would start preferring a generic plan after 5 executions because the plan estimate is clearly wrong. Thing is, the custom plan is up to 10 times faster than the generic plan,…
griffin
  • 1,261
  • 8
  • 24
4
votes
2 answers

Strange problem with SQL Server procedure execution plan

I was wondering if you guys could help me get to the bottom of a weird problem I have recently had on SQL Server. I have a stored procedure (lets call SPold) which is reasonably large with a lot of calculations (can't possibly do this in app as info…
Milambardo
  • 330
  • 2
  • 14
4
votes
2 answers

Sorting a large spatial selection is not using GiST index (Postgres 11.5)

I'm having a table (demo) with a sequence as its primary key (seqno) and a geometry property contained within a JSONB column (doc). I have configured a primary key constraint for the sequence column and a GiST index for the geometry. I have already…
JFX
  • 432
  • 1
  • 4
  • 10
4
votes
1 answer

HashAggregate in SparkSQL Query Plan

I am just trying to understand the query plans generated in SparkSQL (2.4). I have the following query and it's corresponding query plan (below). (The query is just a test query). create temporary view tab_in as select distinct mth_id from…
marie20
  • 723
  • 11
  • 30
4
votes
1 answer

Oracle & PL/SQL Developer - Access predicates and Filter predicates no longer shown

I'm using Allround Automation PL/SQL Developer with Oracle 10g, and today I've noticed that explain plans no longer display anything for Access Predicates or Filter Predicates. The only thing that's changed (that I know of) is that yesterday I tried…
FrustratedWithFormsDesigner
  • 26,726
  • 31
  • 139
  • 202
4
votes
1 answer

Improve performance of PostgreSQL query with LEFT JOIN and subquery

Using 9.4.20 version. I am having a hard time trying to optimize a query. I think after looking at the EXPLAIN that the issue is in the subquery for the second left join. Notice that I decided to do a subquery because I have multiple sources that…
Nobita
  • 23,519
  • 11
  • 58
  • 87
4
votes
1 answer

Performance Issues due to wrong Cardinality Estimate in SQL Server Nested Loops operator

I have a SQL Server query with an INNER JOIN between a large table containing 1 million rows (Acct) and a smaller table containing 10,000 rows (AcctTxns), but SQL Server produces an execution plan with wrong cardinality estimates. I've boiled down…
Y-Mi Wong
  • 86
  • 1
  • 5
1 2 3
…
95 96