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

learn more… | top users | synonyms (1)

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
194 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
243 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
58 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
3k 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
124 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
636 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 ...
2
votes
1answer
144 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
263 views

Can I disable execution plan caching for debugging purposes?

I am trying to isolate poor performing queries in our application. While query execution plan caching is great in production it is interfering with our debugging efforts. Is there are way to ...
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
105 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
479 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
374 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 ...
2
votes
2answers
57 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, ...
2
votes
1answer
245 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
501 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
865 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
338 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
2answers
103 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
64 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
114 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
28 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
958 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('', ...
2
votes
3answers
83 views

SQL actually executed as planned in ORACLE?

I wonder if SQL is definitely executed as execution plan. I mean.. is the actual SQL execution process always consistent with execution plan, i.e. does the same SQL always give the same plan?
2
votes
1answer
609 views

Keep Execution Plan in Cache - SQL Server 2012

How can I keep a query execution plan in SQL Server's cache and use it several times? When I execute a query for the first time it takes 7 seconds. After that it takes only 1 second. This query is ...
2
votes
1answer
46 views

Does DBCC TRACEON() invalidate cached execution plans?

In Microsoft SQL Server, does DBCC TRACEON() invalidate cached execution plans, same as SP_Configure invalidates all stored procedure plans ?
2
votes
1answer
526 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 ...
2
votes
1answer
84 views

Understanding the simple execution plan in PostgreSQL

I'm new to execution plans still trying to learn. My problem is for below query select * from item limit 3; I got following execution plan, "Limit (cost=0.00..0.07 rows=3 width=75)" ...
2
votes
2answers
2k views

Estimated vs Actual Number of Rows

I have 5 tables that contain data split by quarter each table has a similar structure to the following: ID_Column bigint PK, EventType int, DateStamp datetime PK CustomerId int, ARG_1 nvarchar(32), ...
2
votes
1answer
633 views

Non-clustered Index Update not displayed in execution plan [duplicate]

Possible Duplicate: Nonclustered Index Insert I'm trying to work out the cost of having a nonclustered index on a particular table, but I don't see any maintenance cost in the execution ...
2
votes
1answer
273 views

Query tuning - SQL Server

One of our developers are trying to run the below query on a development server, which involves pulling data from a linked server, production. The query ran for more than 14 hours before it was ...
2
votes
2answers
526 views

Clustered monotonically increased index insert performance

I have a table with field Id (bigint, IDENTITY) as primary key and clustered index on it. I inserted 400 rows and saw execution plan. I got: the relative query cost for this insertion 36% and for this ...
2
votes
1answer
168 views

Reading a SQL Server Execution plan

I am very new to any type of tuning but have been learning more.. One of my specific databases one of the problems identified with slow queries are a number of Implicit conversions. It is my ...
2
votes
1answer
139 views

Why is my composite index not used for this table?

I am working with a fairly complicated schema, but I've simplified it for the purposes of this question. Table "public.scheduler_appointment" Column | Type ...
2
votes
1answer
405 views

Does a change in a query executed with sp_executesql cause recompilations?

I am experiencing a lot of re-compilations in my database. Most of the queries being executed are simple queries and all of them are being executed using the sp_executesql stored procedure. ...
2
votes
0answers
68 views

Performance differs on same query against two (almost) identical tables?

I'm running these two queries in SQL Server 2012: SELECT tpua.Field1, CAST(round(twg.Field2 * 2, 0) / 2 As Decimal(8,1)) As Field2, CAST((round((twg.Field3/10.0) * 2, 0) / 2) * 10 As int) As ...
2
votes
0answers
97 views

Execution plan of query inside pl/pgsql function

Briefly, we have a pl/pgsql function which collects some intermediate data in several temporary tables ("on commit drop") and then returns REFCURSOR which joins those temporary tables with some static ...
2
votes
0answers
187 views

This Extended Events session works on Dev but not on Pro. Why? [closed]

I do have the following Extended Event session on my Dev and Pro boxes: CREATE EVENT SESSION [sp_showplan] ON SERVER ADD EVENT sqlserver.query_post_execution_showplan(SET collect_database_name=(1) ...