The strategy selected by the query optimiser to process a query.

learn more… | top users | synonyms (2)

3
votes
2answers
248 views

Any SQL where clause performance similar to where 1=0

(i'm using SQL Server 2008R2 or later) For centralize manage and consistent, there is a view definition as follow: CREATE VIEW view_all_situation ( select null as src, tf.col1, sum(col2) as fee, ...
3
votes
1answer
45 views

Execution plan for COMPUTE BY

I understand that the COMPUTE BY feature is deprecated. Out of curiosity I would like to know how the query processor executes a COMPUTE BY query. The execution plan does not contain any hint: How ...
3
votes
1answer
381 views

Trace Flag 4199

Microsoft's KB article about Trace Flag 4199 is a bit confusing: ...any hotfix that could potentially affect the execution plan of a query must be controlled by a trace flag. Except for fixes ...
3
votes
1answer
446 views

SHOWPLAN does not display a warning but “Include Execution Plan” does for the same query

I was running the following query in SSMS and it showed me a warning in the "Include Execution Plan" window select CLIENT_GUID, PMT_AMOUNT, DATE_COMPLETED from ( select payment.CLIENT_GUID, ...
3
votes
1answer
2k views

Fast paginated result, for various filter clauses

I have been working on obtaining a paginated result from a table in my database (11g). While I have a query that does work (ie, the result is correct) it does not perform as well as I'd like to and I ...
3
votes
3answers
6k views

Revert to old plan in Oracle

A weekly, scheduled, schema wide stats update was run on our Oracle 11R2 RAC cluster on Sunday. One of the queries had no apparent data or code change but now runs at 3 hours instead of 20 minutes. ...
3
votes
1answer
68 views

Inconsistent Execution Plan for Stored Procedure

We have a business-critical stored procedure that normally runs daily at 2am from a scheduled job (in an SSIS package) on the production DB server. The same procedure/package is called from a second ...
3
votes
1answer
121 views

Understanding SQL Server plan cache

I've been reading about the SQL Server plan cache and optimizing for adhoc workloads, etc. I'm fairly new to the topic and have a few general questions to help paint a better picture: Suppose I have ...
3
votes
2answers
4k views

SQL Query too slow on SUM function

I've a table with around 32 million rows having clustered unique index on CountryID,RetailerID,ProductID,DateID,EventID,TypeID and query is SELECT f.RetailerID , TypeID ...
3
votes
2answers
264 views

Query taking too much time

My query is taking too much time to execute. Please help me here. mysql> explain select pcm.catalog_id from cat_produ_catal_map_defer pcm, cat_catal_catal_map_defer ccm, cat_catal_defer c ...
3
votes
1answer
680 views

How are foreign keys resolved when creating tables in batch?

I am creating a database version management tool with the goal of being able to recreate a database from scratch using text/sql files contained in source control. As part of establishing a baseline ...
3
votes
1answer
78 views

Having hard time understanding expressions in below query plan

I was going through Temporal tables new features in SQL 2016,one of the examples was to query tables with as of clause which returned below query plan Rows Executes StmtText StmtId NodeId ...
3
votes
1answer
571 views

Monitoring the Postgres query parser

As I understand it, when you send a query to Postgres like: SELECT id FROM table1 WHERE name = $1; Postgres will create a query plan. The plan will be cached for the same query in the same session....
3
votes
1answer
140 views

Why does my query end up with two seeks instead of one and how do I fix that?

I have these two tables: CREATE TABLE [TaskItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL UNIQUE PRIMARY KEY, [LastOperationTime] DATETIME NULL DEFAULT GETUTCDATE(), [OwnerId] ...
3
votes
1answer
197 views

How do I deal with query getting slower when a select parameter is not specified inline?

I faced this problem twice with SQL Azure and two different queries so far. Something like this: select top(100) * from SomeView WHERE AccountId='actual-guid-typed-in-here' runs super fast but ...
3
votes
1answer
244 views

Optimizing complex join predicates [closed]

Table Definitions CREATE TABLE [dbo].[Pin_Mtg] ( [MtgId] bigint NULL, [CntyCd] char(5) NOT NULL, [BatchDt] int NOT NULL, [BatchSeq] int NOT NULL, [MtgSeq] tinyint NOT NULL, [...
3
votes
1answer
104 views

PostgreSQL + query planner + amount of entities

I'm working on a college assignment about how the query planner uses statistics and makes the most optimal query plan. I have read the 57.1. Row Estimation Examples article and I know how PostgreSQL ...
3
votes
1answer
62 views

Query plan missing ParameterCompiledValue

Recently I've started to analyze execution plans. Today I stumbled on an execution plan that has no ParameterCompiledValue (and no ParameterList tag). It is a plan of a select with one inner join and ...
3
votes
1answer
4k views

Is there any difference on performance when choosing DISTINCT or GROUP BY to bring distinct values?

I would like to know if there is any difference concerning performance when choosing DISTINCT or GROUP BY to bring distinct rows from a query. I've tried comparing the execution plans, but they seem ...
3
votes
1answer
1k views

Is there any way to get the estimate cost of executing a query in MySQL?

In PostgreSQL, EXPLAIN or EXPLAIN ANALYZE will show the estimate cost of executing a query. But EXPLAIN in MySQL doesn't provide this information. How can I get the estimate cost without installation ...
3
votes
1answer
128 views

Simple SELECT takes forever, but works as a subquery. Why?

I'm running a MySQL 5.1 server and I have a MyISAM table called my_table with nearly 5 million records. Its structure is the following: +---------------------------------------------------------------...
3
votes
1answer
641 views

Execution plan in simple query in PgSQL depends on offset clause, why?

Update on 2013-01-16 11:08: see below I am working on PostgreSQL 8.4.14 with the following table fed with 53k lines: CREATE TABLE botany.taxon_data_cache ( taxon_id int NOT NULL, ...
3
votes
0answers
97 views

Bad Plan after a couple of days

I have this query that runs fine for a couple of days (from the application) and then slows down to where it can't even finish. If I rebuild the plan, it flies. There is a signifigant amount of data ...
3
votes
1answer
229 views

High I/O for part of query

I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF): In the above part of ...
2
votes
1answer
83 views

Is it worth to optimize queries with EarlyTermination Timeout?

Right after running Ola's IndexOptimize to rebuild all indexes and statistics, I cleared the data and plan cache and ran a very complex ugly query to get a baseline of it's performance. The query ...
2
votes
1answer
148 views

Estimated rows very wrong when using declared variables

I am getting very, very wrong estimates for the clustered index seek step of this query (estimated rows is 8,637,530; actual is 74,723): Declare @StartDate as date = '10/1/2014'; Declare @EndDate as ...
2
votes
2answers
1k views

How can you tell when a query plan reuse is causing performance problems

When investigating a performance issue, a workmate suggested running DBCC FREEPROCCACHE to clear the plan cache. He came to this conclusion after noticing performance improved after a reboot. This ...
2
votes
2answers
83 views

On which server does a query execute?

Given two Microsoft SQL servers: Say ServerA has a view titled "View1": SELECT * FROM ServerB.database.dbo.Table1 WHERE Column1 IN (1,2,3) and a stored procedure "sp_Foo": SELECT * FROM View1 WHERE ...
2
votes
1answer
123 views

Execution Plan not matching Stored Proc

Below is a stored proc to count the number of widgets made in a month. If no widgets are made, no records will exists. The execution plan shows an INNER JOIN of the M and A tables, where in the ...
2
votes
1answer
6k views

What I can do to speed up Key Lookup in my Query

I am using SQL2005 and against them I calling query simila to this declare @art table (id int primary key, naziv varchar(35) null, sifra varchar(15) null, jm int null); insert ...
2
votes
2answers
106 views

SQL Server changes execution plan

I have SQL code that is executing slow DECLARE @ccode varchar(500) SET @ccode = '%NIP%' SELECT [CVN_UID] FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS] WHERE DESCRIPTOR_TEXT LIKE @ccode Why is SQL ...
2
votes
1answer
510 views

Does SQL check each And condition if first condition fails? [duplicate]

For a query of below kind does sql process data for each And condition if first And condition fails? If Emp_ID is not 12 for a row then does it check condition of Emp_Name? So how SQL process this ...
2
votes
1answer
383 views

How do you figure out what planner cost constants to use in Postgres?

I'm using Postgres 8.4 right now. Performance has started to become an issue as our tables have grown in size and our queries in complexity, so I've started to look into some performance tuning, but I ...
2
votes
3answers
2k views

Why would recompile query hint result in different plan for same adhoc statement after freeproccache?

This is on SQL 2005 SP2, but I suspect this is something that applies to all query hints in general. I've got an adhoc sql statement that gets a different query plan solely because of OPTION(...
2
votes
2answers
61 views

SQL Server - SQL Plan Management for baseline

I am a .Net developer. I have a database upgrade coming up (from SQL Server 2005 to SQL Server 2012). The database team will be handling the migration. But I have seen forums posts saying that after ...
2
votes
2answers
1k views

Is it correct, order of where clause doesn't matter when it is used with join?

I have question on execution of queries in which join is used with where clauses. I read this question on join with where conditions. I tried options like disabling rules and checked actual query ...
2
votes
1answer
2k views

SQL Server 2008 R2 sys.dm_exec_sql_text question

If i run the following query on our production server: SELECT DISTINCT TOP 10 t.TEXT QueryName, s.execution_count AS ExecutionCount, s.max_elapsed_time / 100000 AS MaxElapsedTime, ISNULL(s....
2
votes
1answer
268 views

Execution Plan in Microsoft SQL Server

I have a stored procedure in my database. As per my knowledge it's pre-compiled subroutine. Question 1: If I execute following queries from Hibernate, will database create a separate execution plan ...
2
votes
1answer
542 views

Is there any way to save the execution plan in a file in PostgreSQL?

By saving a plan in a file, I don't mean to view it, but force the optimizer to use the saved plan to execute some query. For example, if the optimizer chooses to do a hash join for a two-table ...
2
votes
2answers
868 views

Why is query using Clustered Index when it shouldn't?

Let us presume I have a table named Category in a SQL Server 2005 database. Category has category_id (bigint, identity) as its primary key and name (nvarchar(50)). There is obviously a clustered ...
2
votes
1answer
339 views

Postgres planer JOIN removal

I have two tables, one contains a lot of rarely update data, and one contains a little of frequently update data. Every record in second table has corresponding record in the first, like following: ...
2
votes
1answer
67 views

Incorrect statistics are used on index seek

I have a table-valued function and ordinary table and need to perform INNER JOIN on them. Because, the engine thinks that the function is returning always one row, I try to store the results in ...
2
votes
2answers
106 views

Execution plan has TOP operator for query without TOP or ORDER BY

I have product sales by month temporary table.(subset of much larger historical table) I'm running query to find new product not previously sold in the previous months for a specified 6 month range. ...
2
votes
1answer
122 views

Same query sometimes uses a Table Spool

I'm checking out a query which exhibits strangely inconsistent performance. Using SolarWinds DPA, I can see that the query sometimes uses a Table Spool, sometimes it doesn't. When it actually used a ...
2
votes
2answers
1k views

In DB2, how can I disable NLJOIN?

I want to indicate to the optimizer, via SET or optimization profiles, that NLJOIN shouldn't be used at all when optimizing a query. I've been looking for this for a while without any success. I ...
2
votes
2answers
69 views

How to prevent PostgreSQL from rewriting OUTER JOIN queries?

My query is: SELECT Acol1, Acol2, Bcol1, Bcol2, Ccol1, Ccol2 FROM tableA LEFT JOIN (tableB FULL JOIN tableC ON (Bcol1 = Ccol1)) ON (Acol1 = Bcol1) EXPLAIN ANALYZE gives ...
2
votes
1answer
126 views

SQL Server 'ignores' drop table command [duplicate]

I have a 500+ lines stored proccedure which I am writting and testing using the Microsoft SQL Management studio. Today I encounter the following 'strange' case. My query (in pseudocode) look like the ...
2
votes
1answer
29 views

Turn off auto qualification of table names when creating a view

Problem Definition I am trying to modified a shared development database, for a dev team to use for application development/testing. Most of the collective work (table names, views, etc) are stored ...
2
votes
1answer
2k views

query performance gains by removing operator hash match inner join

While trying to apply the contents of this question below, to my own situation, I am a bit confused as how I could get rid of the operator Hash Match (Inner Join) if any way possible. SQL Server ...
2
votes
1answer
46 views

Oracle and execution plans

I have used the following procedure to view the last execution plan create or replace procedure display_plan as begin for c in (select plan_table_output t from table(dbms_xplan.display_cursor('', ''...