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

Full-text search Stored Procedure runs slowly, except when it runs fast

I just don't get it. The results for calling the stored procedure below come back within a few seconds or less. But when the @SearchPhrase parameter gets changed, it takes minutes to complete. You can see I've used the WITH RECOMPILE option. The…
Jarvis
  • 681
  • 8
  • 33
0
votes
1 answer

how to write optimize query from given query?

I have a table whose structure is - CREATE TABLE `cdr` (`id` bigint(20) NOT NULL AUTO_INCREMENT, `dataPacketDownLink` bigint(20) DEFAULT NULL, `dataPacketUpLink` bigint(20) DEFAULT NULL, …
Aamir
  • 738
  • 2
  • 17
  • 41
0
votes
0 answers

Why nscannedObjectsAllPlans increases in mongodb?

I am wondering about nscannedObjectsAllPlans/nscannedAllPlans in the output of mongodb explain(). As far as I understand, Mongo runs several plans (if available) and picks the fastest. The information about the plans are kept for another 100…
user3111525
  • 5,013
  • 9
  • 39
  • 64
0
votes
1 answer

How can I efficiently query and index a view that involves a full outer join?

We have a data processing application that has two separate paths that should eventually produce similar results. We also have a database-backed monitoring service that compares and utilizes the results of this processing. At any point in time,…
Chris Phillips
  • 11,607
  • 3
  • 34
  • 45
0
votes
4 answers

Is my execution plan trying to trick me?

I am trying to speed up a long running query that I have (takes about 10 minutes to run...). In order to track down what part of the query is costing me the most time I included the Actual Execution Plan when I ran it and found a particular section…
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
0
votes
2 answers

Oracle SQL: additional restriction causes performance issues

I have a strange performance problem with a oracle SQL statement. The statement is a more or less giantic subselect / inner join statement, therefore I'll only be able to post the structure of it here. It looks like this: SELECT "A".COL1, [...] FROM…
techmuc
  • 25
  • 4
0
votes
1 answer

Execution Plan - Remove key lookup

I am doing a join on 2 tables (car and Work). I am searching on the reg number to find out all the work done on a particular car. SELECT C.RegNum ,W.StartDate ,W.ShortDesc ,w.LabourCost FROM Work W INNER JOIN Car C ON …
Jay
  • 81
  • 2
  • 10
0
votes
2 answers

Oracle 10 G - Reject/Prevent CBO from using a Plan

Is there a way in Oracle 10G to tell the CBO that it should never consider/ Reject a particular PLAN of Execution (If we know the Plan Hash Value and if we know that the given plan always provokes performance issues). Likewise, is there a way to…
Guddu
  • 1,588
  • 4
  • 25
  • 53
0
votes
2 answers

Is it possible to get explain plan with bind variables in DB2?

With Oracle, the syntax is: explain plan for select * from users WHERE user_name = :user_name AND user_dob = :user_dob Is it possible to do the same in DB2? The statement below does not seem to work. explain plan with snapshot for select * from…
Andy
  • 2,770
  • 9
  • 35
  • 42
0
votes
1 answer

Sybase worktable created for reformatting - why?

I have the following contrived tables car (id char(8) NOT NULL, make char(10) NOT NULL) with NONCLUSTERED INDEX i0 ON car(make) ~80k rows of which ~2k are makes with code I need carmake (make char(10) NOT NULL, code int NOT NULL) with NONCLUSTERED…
Failing Coder
  • 646
  • 1
  • 10
  • 16
0
votes
1 answer

T-SQL query plan to help choose best form of query?

I have a simple data logging table with two data columns A and B for which I want to generate the sum of A+B. However either or both A and B could be null. The table also has a timestamp column which is the primary key I can see several ways to…
Peter M
  • 7,309
  • 3
  • 50
  • 91
0
votes
1 answer

Mysql alter query plan type

Hi I have a query like this SELECT (SELECT CSN FROM apps as b WHERE a.key1=b.key1 AND a.key2 = b.key2 AND b.seq=MAX(b.seq) LIMIT 0,1) AS CSN, a.key1 FROM apps as a GROUP BY key1, key2 Now when I use EXPLAIN I get that…
Martin Kosicky
  • 471
  • 4
  • 12
0
votes
0 answers

Different execution plans for remote DELETE and INSERT / same JOINs

I'm working in an environment where data exists on numerous client databases and one by one is pulled into a central data repository via sql. To automate a testing process, I've written a really nice, streamlined push-button script that backups,…
Trent Baur
  • 95
  • 1
  • 9
0
votes
5 answers

Why would a SELECT statement be 45% of the execution plan cost in SQL Server 2008?

I have a query where I select a few columns from each of 5 left outer joined tables. I did an execution plan in SQL Server 2008, and there are basically table scans on all of the joined tables, but the cost is all 0% for them - I'm assuming because…
chucknelson
  • 2,328
  • 3
  • 24
  • 31
0
votes
1 answer

SQL Server Getting Parsed Query

I need to get each statement of given SQL Server stored procedure. To achieve this, I generate execution plan XML and look for "StmtSimple" nodes in query. This approach is quite slow for large procedures. Is there any way that I can get every…
Ahmet Altun
  • 3,910
  • 9
  • 39
  • 64
1 2 3
…
95 96