Skip to main content
102 votes
Accepted

Why is my query suddenly slower than it was yesterday?

Dear [your name here]! Oh no, I'm sorry to hear that! Let's start with some basics to get you fixed up in a jiffy. The thing you're running into is called Parameter Sniffing It's a way out wiggy ...
Erik Darling's user avatar
  • 40.8k
61 votes
Accepted

Indexes: integer vs string performance if the number of nodes is the same

Short answer: integer is faster than varchar or text in every aspect. Won't matter much for small tables and / or short keys. The difference grows with the length of the keys and the number of rows. ...
Erwin Brandstetter's user avatar
47 votes
Accepted

Performance of a=0 and b=0 and ... z=0 vs a+b+c+d=0

In your question, you detail some tests that you've prepared where you "prove" that the addition option is quicker than comparing the discrete columns. I suspect your test methodology may ...
Hannah Vernon's user avatar
  • 70.1k
47 votes
Accepted

Eliminate Key Lookup (Clustered) operator that slows down performance

Key lookups of various flavors occur when the query processor needs to obtain values from columns that are not stored in the index used to locate the rows required for the query to return results. ...
Hannah Vernon's user avatar
  • 70.1k
46 votes

How does SQL Server determine key column order in missing index requests?

When SQL Server creates a missing index recommendation for a particular query plan, it separates possible key columns into 2 groups. The first set contains all of the recommended columns that are ...
Bryan Rebok's user avatar
  • 1,169
43 votes

Can a single PostgreSQL query use multiple cores?

PostgreSQL 9.6+ onwards, would start to see Parallel-Query finally coming to PostgreSQL. For e.g. Concepts like Parallel Scan / Parallel Join / Parallel Aggregates are now already baked in, with ...
Robins Tharakan's user avatar
35 votes
Accepted

Warning about memory "Excessive Grant" in the query plan - how to find out what is causing it?

The most common two types of operators that consume memory are: Sorts Hashes If a plan is parallel, memory requirements will go up some amount to compensate for exchanges for threads to pass rows ...
Erik Darling's user avatar
  • 40.8k
34 votes

Why is my query suddenly slower than it was yesterday?

Parameter sniffing is not the only possible cause for a query's varying performance. Any of the following common reasons may show the same symptoms: Data distribution / volume changed, crossing an ...
SQLRaptor's user avatar
  • 4,098
33 votes
Accepted

SQL Server--If logic in stored procedure and the plan cache

SQL Server 2012 and 2016 Standard: If I put if-else logic in a stored procedure to execute one of two branches of code, depending on the value of a parameter, does the engine cache the latest version? ...
Erik Darling's user avatar
  • 40.8k
32 votes
Accepted

Why is a scan faster than seek for this predicate?

Why is there a large difference in CPU time for these two queries? The scan plan evaluates the following pushed non-sargable (residual) predicate for each row: [two_col_key_test].[ID1]<>N'1' ...
Paul White's user avatar
  • 84.4k
30 votes

If a CTE is defined in a query and is never used, does it make a sound?

+1 to Erik, but wanted to add two things (which did not work well in a comment): You don't even need to look at execution plans to see that they are ignored when not used. The following should ...
Solomon Rutzky's user avatar
29 votes
Accepted

Why is "select *" faster than "select top 500 *" in SQL Server?

Adding a TOP clause to a query introduces a row goal to the query. The query optimizer will attempt to use the fact that it doesn't need to return all rows to create a more efficient query plan. The ...
Joe Obbish's user avatar
  • 32.2k
29 votes
Accepted

Why filtered index on IS NULL value is not used?

Why this index is not "covering" for this query: No good reason. That is a covering index for that query. Please vote for the feeback item here: https://feedback.azure.com/d365community/...
David Browne - Microsoft's user avatar
28 votes
Accepted

Difference between Seek Predicate and Predicate

Let's throw one million rows into a temp table along with a few columns: CREATE TABLE #174860 ( PK INT NOT NULL, COL1 INT NOT NULL, COL2 INT NOT NULL, PRIMARY KEY (PK) ); INSERT INTO #174860 WITH (...
Joe Obbish's user avatar
  • 32.2k
28 votes
Accepted

Undesirable Nest Loop vs. Hash Join in PostgreSQL 9.6

What's happening here is the Nested Loop is way off on one side. Nested Loops work really well when one side is very small, such as returning one row. In your query, the planner fumbles here and ...
Evan Carroll's user avatar
  • 63.2k
27 votes
Accepted

How to make DISTINCT ON faster in PostgreSQL?

For only 400 stations, this query will be massively faster: SELECT s.station_id, l.submitted_at, l.level_sensor FROM station s CROSS JOIN LATERAL ( SELECT submitted_at, level_sensor FROM ...
Erwin Brandstetter's user avatar
25 votes
Accepted

DEFAULT CONSTRAINT, worth it?

I assume that the database is checking always default values, even if I supply the correct value, hence I'm doing the same job twice. Um, why would you assume that? ;-). Given that Defaults exist to ...
Solomon Rutzky's user avatar
25 votes
Accepted

Colleague says to never use an OR statement in SQL, is this true?

Never listen to anyone saying that you should never do X. Generally, you should not try to outsmart the query optimizer without some really good reasons. It is true that in certain scenarios too many ...
mustaccio's user avatar
  • 26k
24 votes

Performance of a=0 and b=0 and ... z=0 vs a+b+c+d=0

Let's say you have an index on A, B, C and D. Could be filtered too. This is more likely to use the index then addition. Where A=0 and B=0 and C=0 and D=0 In other news, If A is -1 and B is 1, A+B=...
gbn's user avatar
  • 69.8k
24 votes
Accepted

SQL Server 2014: any explanation for inconsistent self join cardinality estimate?

I am having a hard time understanding why SQL Server would come up with an estimate that can be so easily proven to be inconsistent with the statistics. Consistency There is no general guarantee of ...
Paul White's user avatar
  • 84.4k
24 votes
Accepted

Hash keys probe and residual

If the join is on a single column typed as tinyint, smallint, or integer* and if both columns are constrained to be NOT NULL, the hash function is 'perfect' – meaning there is no chance of a hash ...
Paul White's user avatar
  • 84.4k
24 votes
Accepted

Why does a DELETE query run in one format much longer than in another?

This part of the plan is the problem. Issue The correct behaviour if the subquery brings back any NULL is to return 0 rows from the NOT IN. Even if ID is not nullable (and therefore MIN(ID) cannot ...
Martin Smith's user avatar
  • 84.7k
24 votes
Accepted

Improper use of logical operators has led to poor query performance

In order to find out if two conditions are truly equivalent, you could try building the truth table for each of them and see if the two tables are identical. Here is how you could go about building ...
Andriy M's user avatar
  • 23k
23 votes

Which is faster, SUM(CASE) or CTE PIVOT?

Are there examples where PIVOT is slower? This is unlikely in simple cases. As Itzik Ben-Gan notes in his SQL Server Pro article, Pivoting Data when looking at the plan for a PIVOT query (emphasis ...
Paul White's user avatar
  • 84.4k
22 votes
Accepted

Wrapping query in IF EXISTS makes it very slow

As has been explained by Paul White in his blog post: Inside the Optimizer: Row Goals In Depth the EXISTS introduces a row goal, which prefers NESTED LOOPS or MERGE JOIN over HASH MATCH As a final ...
Tom V's user avatar
  • 15.7k
22 votes
Accepted

If a CTE is defined in a query and is never used, does it make a sound?

It doesn't appear that they do, but this really only applies to nested CTEs. Create two temp tables: CREATE TABLE #t1 (id INT); INSERT #t1 ( id ) VALUES ( 1 ); CREATE TABLE #t2 (id INT); INSERT #t2 ...
Erik Darling's user avatar
  • 40.8k
21 votes
Accepted

Why does Concatenation operator estimate fewer rows than its inputs?

To quote Campbell Fraser on this Connect item: These "cardinality inconsistencies" can arise in a number of situations, including when concat is used. They can arise because the estimation of a ...
Paul White's user avatar
  • 84.4k
21 votes
Accepted

Operator used tempdb to spill data during execution with spill level 2

what about level 2? I could not find anything related to level 2. As per this Old MS Doc the number in Tempdb spill signifies how many passes is required over data to sort the data. So Spill 1 means ...
Shanky's user avatar
  • 19k
21 votes
Accepted

Single-row INSERT...SELECT much slower than separate SELECT

SQL Server chooses to scan the heap tables on the inner side of the loops joins using row-level locks. A full scan would normally choose page-level locking, but a combination of the size of the table ...
Paul White's user avatar
  • 84.4k
20 votes
Accepted

Which queries are faster with Postgres than with MySQL InnoDB

To avoid flame war, I'll just glance the way each storage work on querying, not really a benchmark. I'll use this table as reference (the code should be slightly modified to run on both RDBMS): ...
MatheusOl's user avatar
  • 2,519

Only top scored, non community-wiki answers of a minimum length are eligible