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

learn more… | top users | synonyms (1)

0
votes
3answers
76 views

MySQL optimizer won't use my index

With the following query: SELECT * FROM geoname LEFT JOIN alternatename ON geoname.geonameid = alternatename.geonameid AND alternatename.isPreferredName = 1 AND ...
2
votes
2answers
65 views

Understanding Execution Plan

I am really having hard time understanding the below execution plan. Query 1: (Simple Select Query) Record count of ##SALES_UNIT_RESULT is 168. In first execution plan it is fine. ;WITH ...
4
votes
1answer
53 views

Optimizing “random” choice in PostgreSQL

I have the following table CREATE TABLE article ( id bigserial PRIMARY KEY, text TEXT NOT NULL, category_id bigint REFERENCES categories (id), status TEXT NOT NULL, locale TEXT ...
2
votes
1answer
215 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
624 views

Oracle 11g bug ? not returning the record until I triggered index to invisible and then to visible

We are using Oracle 11g, 11.2.0.3. We know a record exists in a table but a select is not returning it for some odd reason. The execution plans the general query, not forcing any index, shows that ...
1
vote
1answer
165 views

Joining two inline functions, slows considerably when filtering on second function

I have two inline functions that run quickly enough alone. When I left join them like this: select * from Function1(-1) F1 left join Function2(-1) F2 on F1.key = F2.key ...I get my result set in ...
0
votes
1answer
86 views

Optimizing a DMV query

I have found a query that is a major performance issue in my environment. I looked at the actual execution plan for this query and found out the main problem is the remote scan (98%). Its remote ...
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
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
231 views

Does Oracle ADO.NET varchar parameter size influence query plan reuse?

I am considering to apply a fix which will generate constant parameter sizes for (n)varchar parameters. The application is based on NHibernate with the old OracleClientDriver (using Microsoft Oracle ...
2
votes
0answers
156 views

Multi-Statement Template Plan Guide

A piece of, um, software seems to be issuing ~20k queries of this form every few seconds: DELETE FROM macVendor WHERE companyId=N' 08-00-'; INSERT INTO macVendor (companyId,organization) VALUES (N' ...
2
votes
0answers
245 views

Postgres - Partitioning old tables, partition query planning, optimisation

I've been trying to optimise our database, but not being a DBA and not having done a whole lot of tuning before I'm struggling a little with "too many options". I've already run pg_tune and bumped a ...
1
vote
0answers
44 views

Why does the first query scan and the second seek?

Please could somebody explain why the first query below scans on every JOIN to the REFERENCE_VALUES table but the second query seeks on the same JOINs. The first query does have other JOINs but I ...
1
vote
0answers
32 views

Reasons why plans of sys.dm_exec_cached_plans do not match actual plans?

I re-assess index / query optimization and actually focus on key/bookmark lookpus that could be eliminated. Using a script of Jonathan Kehayas I go through the list of query plans that used those ...
1
vote
0answers
89 views

When will SQL Server automatically use a supporting indexed view?

I have the following expensive query that runs against our data warehouse: SELECT d.DateYear, COUNT_BIG(*) AS WidgetCount FROM Widgets AS w JOIN Dates AS d ON d.DateId = w.DateId WHERE ...
1
vote
0answers
73 views

Odd Execution Plan

I have a table in SQL Server that is performing very slowly and I have been trying different indexing options to try to speed it up to no avail. I suspect all of the varchar(max) fields may be a ...
1
vote
0answers
131 views

Different Execution Plans Picked for Similar Queries

Thank you in advance for reading it. This question is kind of long and need a little patience to read. I will provide as much details as I can to void interference factors to solve this problem. ...
0
votes
0answers
41 views

How to make sure vacuum is happening in postgres sql db

I have created a table with two columns and created index for the first column. Then inserted 2 million data using generate series function for first column (2nd column is having null). And executed ...
0
votes
0answers
53 views

Large differences between estimated and actual row counts but stats are OK

I'm having a problem with a Function which is taking 10 times longer to run than it used to and high CPU usage. And I'm puzzled by what I'm seeing in the execution plan with some index seek operators ...
0
votes
0answers
73 views

Eager Spool 100%

I have a query like below, when I execute it, the parallel query always takes very long time to finish, I found there is index spool(Eager Spool) which costs 100% in execution plan... How can I ...
0
votes
0answers
69 views

SQL query running slow compared to its earlier execution time

One of our application using an SQL query is reported running slow. Current execution time:- 15 minutes and so Earlier execution time:- 30 seconds. SQL-Sentry Plan for the slow query I've ...
0
votes
0answers
91 views

Query plan reads vs statistics io reads

I have a very strange problem. When I run a query with SET STATISTICS IO ON, it shows that I had a total of 183 reads. But when I look at the query plan dmv it shows I did 555,132 reads. I cleared the ...
0
votes
0answers
91 views

Index Tuning Question

I have the following query: SELECT T1.ITEMID, T1.ITEMTYPE, T1.PURCHMODEL, T1.HEIGHT, T1.WIDTH, T1.SALESMODEL, T1.COSTGROUPID, T1.REQGROUPID, T1.EPCMANAGER, ...
0
votes
0answers
35 views

Analize Query in MySQL Server 5.1

I try use MySQL Workbench 6 to analyze the cost, but they don't show the cost of query. How i get this costs?