Questions tagged [optimization]

In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.

Filter by
Sorted by
Tagged with
110
votes
6answers
113k views

How to determine if an Index is required or necessary

I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
96
votes
4answers
89k views

Optimizing queries on a range of timestamps (two columns)

I use PostgreSQL 9.1 on Ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my ...
53
votes
3answers
74k views

When to use views in MySQL?

When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table? One reason that I would prefer to use views is that the database schema ...
43
votes
4answers
25k views

Are views harmful for performance in PostgreSQL?

The following is an excerpt from a book about db design (Beginning Database Design ISBN: 0-7645-7490-6): The danger with using views is filtering a query against a view, expecting to read a very ...
40
votes
3answers
4k views

Unexpected scans during delete operation using WHERE IN

I've got a query like the following: DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN ( SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL ) ...
35
votes
2answers
10k views

How (and why) does TOP impact an execution plan?

For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan. I would have guessed that when a query includes TOP n the database engine ...
33
votes
1answer
15k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
32
votes
5answers
114k views

Logical operators OR AND in condition and order of conditions in WHERE

Let's examine these two statements: IF (CONDITION 1) OR (CONDITION 2) ... IF (CONDITION 3) AND (CONDITION 4) ... If CONDITION 1 is TRUE, will CONDITION 2 be checked? If CONDITION 3 is FALSE, will ...
28
votes
2answers
35k views

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization ...
27
votes
6answers
5k views

Why does my SELECT DISTINCT TOP N query scan the entire table?

I've run into a few SELECT DISTINCT TOP N queries which appear to be poorly optimized by the SQL Server query optimizer. Let's start by considering a trivial example: a million row table with two ...
27
votes
4answers
529 views

Why does the estimated cost of (the same) 1000 seeks on a unique index differ in these plans?

In the queries below both execution plans are estimated to perform 1,000 seeks on a unique index. The seeks are driven by an ordered scan on the same source table so seemingly should end up seeking ...
26
votes
3answers
1k views

SQL Server splits A <> B into A < B OR A > B, yielding strange results if B is non-deterministic

We have encountered an interesting issue with SQL Server. Consider the following repro example: CREATE TABLE #test (s_guid uniqueidentifier PRIMARY KEY); INSERT INTO #test (s_guid) VALUES ('7E28EFF8-...
24
votes
2answers
2k views

SQL Server - Cardinality Estimate for LIKE operator (Local Variables)

I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and ...
23
votes
1answer
2k views

Why doesn't this query use an index spool?

I'm asking this question in order to better understand the optimizer's behavior and to understand the limits around index spools. Suppose that I put integers from 1 to 10000 into a heap: CREATE TABLE ...
23
votes
1answer
2k views

Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?

I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
20
votes
2answers
1k views

Queries without Good Enough Plan Found

I have a SQL Server 2012 database. I noticed value of Reason for early termination of statement optimization for some queries and all gave Good Enough Plan Found. Now my questions are: What are all ...
19
votes
3answers
11k views

Does “WHERE 1=1” usually have an impact on query performance?

I recently saw the question "where 1=1 statement"; a SQL construct I have used often in constructing dynamic SQL in an effort to write cleaner code (from the perspective of the host language). ...
19
votes
3answers
13k views

Trace Flag 4199 - Enable globally?

This may fall under the category of opinion, but I'm curious if people are using trace flag 4199 as a startup parameter for SQL Server. For those that have used it, under what circumstances did you ...
19
votes
2answers
2k views

Why are the plans different if the queries are logically alike?

I wrote two functions to answer Day 3's first homework question from Seven Databases in Seven Weeks. Create a stored procedure where you can input a movie title or actor's name you like, and it ...
18
votes
7answers
2k views

Optimizing numeric range (interval) searches in SQL Server

This question is similar to Optimizing IP Range Search? but that one is restricted to SQL Server 2000. Suppose I have 10 million ranges provisionally stored in a table structured and populated as ...
18
votes
3answers
615 views

Forcing Flow Distinct

I have a table like this: CREATE TABLE Updates ( UpdateId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ObjectId INT NOT NULL ) Essentially tracking updates to objects with an increasing ID. The ...
18
votes
3answers
13k views

Splitting SQL query with many joins into smaller ones helps?

We need to do some reporting every night on our SQL Server 2008 R2. Calculating the reports takes several hours. In order to shorten the time we precalculate a table. This table is created based on ...
18
votes
2answers
2k views

Is it possible to give the optimizer more or all time it needs?

Given that the optimizer cannot take all the time it needs (it has to minimize the execution time and not contribute to it) to explore all possible execution plans it sometimes get cut off. I was ...
18
votes
2answers
4k views

Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?

I am trying to understand why using a table variable is preventing the optimizer from using an index seek and then bookmark lookup versus an index scan. Populating the table: CREATE TABLE dbo.Test ...
18
votes
1answer
12k views

EXPLAIN ANALYZE shows no details for queries inside a plpgsql function

I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside: create function f1() returns integer as $$ declare event tablename%ROWTYPE; .... .... begin FOR event IN ...
16
votes
2answers
29k views

How to partition existing table in postgres?

I would like to partition a table with 1M+ rows by date range. How is this commonly done without requiring much downtime or risking losing data? Here are the strategies I am considering, but open to ...
16
votes
1answer
2k views

Monumental difference in execution time between queries when using RECOMPILE query hint

I have two almost identical queries running on the same SQL Server 2005 instance: The first one is the original SELECT query as generated by LINQ (I know, I know... I'm not the application developer, ...
16
votes
2answers
679 views

Why does referencing a variable in a join predicate force nested loops?

I came across this issue recently and couldn't find any discussion of it online. The query below DECLARE @S VARCHAR(1) = ''; WITH T AS (SELECT name + @S AS name2, * ...
16
votes
3answers
4k views

Predicates: in the WHERE clause or the JOIN clause? [duplicate]

Looking at "SQL Server 2008 Internals"1 by Kalen Delaney, on page 13, it states the following: "The first step in producing such a plan is to normalize each query, which potentially breaks down a ...
16
votes
1answer
729 views

Modification to GEQO (Genetic Query Optimization) of PostgreSQL

I need to implement a functionality that is in line with the GEQO functionality of PostgreSQL. I understand that GEQO approach is to encode query plans as integer strings and GEQO generates these ...
15
votes
3answers
2k views

Why is my EXISTS query doing an index scan instead of an index seek?

I am working on optimizing some queries. For the query below, SET STATISTICS IO ON; DECLARE @OrderStartDate DATETIME2 = '27 feb 2016'; DECLARE @OrderEndDate DATETIME2 = '28 feb 2016'; SELECT o....
15
votes
3answers
26k views

Why does MySQL ignore the index even on force for this order by?

I run an EXPLAIN: mysql> explain select last_name from employees order by last_name; +----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+ | ...
15
votes
2answers
83k views

“Copying to tmp table” extremely slow

This is my example of query: SELECT nickname, CASE class_id WHEN 1 THEN 'Druid' WHEN 2 THEN 'Necromancer' WHEN 3 THEN 'Mage' WHEN 4 THEN 'Priest' WHEN 5 THEN '...
15
votes
2answers
2k views

Optimization: Moving variable declarations to the top of your procedure

While working on optimizing some stored procedures, I sat down with the DBA and went through some stored procedures with high blocking and/or high read/write activity. One thing the DBA mentioned was ...
15
votes
2answers
16k views

Does an empty column value occupy same storage space as a filled column value?

I have a table with 2 columns. The type of both columns is set to varchar(38). If I create a row with an empty value for one of the columns, will it take same storage space as if the value was not ...
15
votes
2answers
16k views

How to speed up select distinct?

I have a simple select distinct on some time series data: SELECT DISTINCT user_id FROM events WHERE project_id = 6 AND time > '2015-01-11 8:00:00' AND time < '2015-02-10 8:00:00'; And it takes ...
15
votes
3answers
2k views

Oracle is not using a unique index for a long key

I have a table with 250K rows in my test database. (There are a few hundred millions in production, we can observe the same issue there.) The table has an nvarchar2(50) string identifier, not null, ...
15
votes
1answer
307 views

Why aggregate operator used after unique index scan

I have a table with a unique index filtered for non-nullable values. In the query plan there is use of distinct. Is there a reason for this? USE tempdb CREATE TABLE T1( Id INT NOT NULL IDENTITY ...
15
votes
3answers
3k views

Indexes for SQL query with WHERE condition and GROUP BY

I am trying to determine which indexes to use for an SQL query with a WHERE condition and a GROUP BY which is currently running very slow. My query: SELECT group_id FROM counter WHERE ts between ...
15
votes
2answers
13k views

How to optimise T-SQL query using Execution Plan

I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire ...
15
votes
3answers
2k views

Optimizing a CTE hierarchy

Update below I have a table of accounts with a typical acct/parent account architecture to represent a hierarchy of accounts (SQL Server 2012). I created a VIEW using a CTE to hash out the hierarchy, ...
15
votes
3answers
3k views

Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?

The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very ...
15
votes
2answers
2k views

Join query taking 11 mins to run on 300,000 rows table

Below query is taking more than 11 minutes to execute. SELECT `c`.*, `e`.`name` AS `employee_name`, `e`.`emp_no`, `d`.`code` ...
15
votes
2answers
436 views

Index on a persisted computed column not seekable

I have table, called Address, that has a persisted computed column called Hashkey. The column is deterministic but not precise. It has a unique index on it that is not seekable. If I run this query, ...
15
votes
1answer
405 views

When can SARGable predicates be pushed into a CTE or derived table?

Sandbag While working on Top Quality Blog Posts®, I came across some optimizer behavior I found really infuriating interesting. I don't immediately have an explanation, at least not one I'm happy ...
15
votes
2answers
478 views

Why do SQL Server row estimates change when I add a join hint?

I have a query that joins a few tables and performs pretty badly - row estimates are way (a 1000 times) off and Nested Loops join is chosen, resulting in multiple table scans. The shape of the query ...
15
votes
1answer
5k views

Why is Clustered Index Scan Number of Executions so high?

I have two similar queries that generate the same query plan, except that one query plan executes a Clustered Index Scan 1316 times, while the other executes it 1 time. The only difference between ...
14
votes
4answers
2k views

Is a join optimized to a where clause at runtime?

When I write a query like this... select * from table1 t1 join table2 t2 on t1.id = t2.id Does the SQL optimizer, not sure if that is the correct term, translate that to... select * from table1 t1, ...
14
votes
4answers
7k views

Is it possible to increase query performance on a narrow table with millions of rows?

I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
14
votes
4answers
10k views

Good, Bad or Indifferent: WHERE 1=1

Given this question on reddit, I cleaned up the query to point out where the issue was in the query. I use comma first and WHERE 1=1 to make modifying queries easier, so my queries generally end up ...