Category: Query Tuning

Writing Conditional JOIN and WHERE Clauses

Published 2024-04-26 by Kevin Feasel

Erik Darling has Blondie stuck in my head:

The OR operator is a perfectly valid one to use in SQL statements. If you use an IN clause, there’s a reasonable chance that the optimizer will convert it to a series of OR statements.
For example, IN(1, 2, 3) could end up being = 1 OR = 2 OR = 3 without you doing a darn thing. Optimizers are funny like that. Funny little bunnies.
The problem generally isn’t when asking for IN or OR for a single column, with a list of literal values, the problem is usually when you:
Use OR across multiple where clause columns
Use OR in a join clause of any variety
Use OR to handle NULL parameters or variables

This is an excellent, detailed article and well worth the read.

Logical Reads and Query Tuning

Published 2024-04-25 by Kevin Feasel

Erik Darling doesn’t focus on logical reads:

To summarize the video a little bit:
High average or total logical reads isn’t a guarantee that a query is slow
Looking for high average CPU and duration queries is a better metric
You may see logical reads go up or down as you make queries faster
For I/O bound workloads, you’re better off looking for queries with a lot of physical reads

I agree with Erik. Disk has gotten so much faster, especially if you’re on all-flash arrays or (even better) direct attached nVME storage. Even relatively old SSDs are still a couple orders of magnitude faster than the spinning rust we typically dealt with 15 years ago.

Sometimes, the faster query is one that requires more logical reads. APPLY-based queries typically fall into that category: I might have 10x as many logical reads but the query takes half the time (or less) to finish using a similar percentage reduction of CPU time.

The Equivalency of Views and Common Table Expressions

Published 2024-04-19 by Kevin Feasel

Erik Darling makes a comparison:

Perhaps one of the most exhausting parts of my job is disabusing developers of the notion that common table expressions hold some weight in gold over any other abstraction layer in SQL Server.
Think of it like this:
Views are like a permanent home
Common table expressions are like a mobile home
You can put equally terrible queries in either one and expect equally terrible results.

Read the whole thing for additional spicy analogies and similes.

I would say that I certainly do not disdain views, so much as I see them as a yellow flag. It’s really easy to go from helpful views to views nested in views nested in views like malevolent Matryoshka dolls. It’s harder to do that with common table expressions before that reptilian part of your brain kicks in and says that “hey, maybe this isn’t the greatest idea I’ve ever had.” Not that this will stop some people, admittedly…

Working with Query Store Hints

Published 2024-04-04 by Kevin Feasel

Etienne Lopes shares some thoughts around a new feature in SQL Server 2022:

Last December I got lucky since after upgrading from SQL Server 2012 to 2022, one of my clients had a process running in a particular database that went from around 10 minutes to around 10 hours!
You might be thinking: “It got 60 times worse, where’s the luck in that?”
Well, it presented me with a great opportunity to try a new feature in SQL Server 2022 called “Query Store Hints” (in a real production environment) and confirm the fabulous results that can be obtained by using it, with no effort at all. I thought it could be worth to share this experience and that’s what I’ll present in this post

Click through for more details on the problem and solution.

Comments closed

Dealing with Parameter Sniffing using Multiple Execution Plans

Published 2024-04-03 by Kevin Feasel

Andy Brownsword deals with statistical skew in the data:

Dynamic SQL has many uses and one of these can help us fix Parameter Sniffing issues. Here we’ll look at how it can be used to generate multiple execution plans for the same query.
Parameter sniffing is a common issue. Even for simple queries we can run into suboptimal plans being produced. There are multiple ways we can use Dynamic SQL to solve this challenge. Here we’ll demonstrate one technique: Comment Injection.

My one note about a good post (other than, you should read it) is that parameter sniffing is not itself a bad thing. 95%+ of the time, it’s a great thing. It’s that last 5% or so that give it a bad name.

Comments closed

Working with INTERSECT and EXCEPT

Published 2024-03-28 by Kevin Feasel

Erik Darling wounds me:

I have never once seen anyone use these. The most glaring issue with them is that unlike a lot of other directives in SQL, these ones just don’t do a good job of telling you what they do, and their behavior is sort of weird.
Unlike EXISTS and NOT EXISTS, which state their case very plainly, as do UNION and UNION ALL, figuring these out is not the most straightforward thing. Especially since INTERSECT has operator precedence rules that many other directives do not.

I’ve used EXCEPT to check if two datasets are equivalent for testing purposes: A EXCEPT B should be zero rows, and B EXCEPT A should be zero rows. It has built-in handling of any NULL madness. Set intersections have their uses as well.

Comments closed

An Overview of Postgres’s Planner

Published 2024-03-25 by Kevin Feasel

Cary Huang digs into one phase of Postgres query processing:

When you send a query to PostgreSQL, it normally would go through stages of query processing and return you the results at the end. These stages are known as:
Parse
Analyze
Rewrite
Plan
Execute
I wrote another blog to briefly explain the responsibility of each query processing stage. You can find it here. In this blog, we will only focus on the “plan” stage or the “planner” module as this is perhaps the most interesting or complex stage if you will. I will share my understanding of the planner module as I investigate its internal workings to handle a simple sequential scan. This will be based on PostgreSQL 16.

Read on to learn what the planner does and how it works, at a high level.

Comments closed

Sorting by Large Columns in SQL Server

Published 2024-03-25 by Kevin Feasel

Aaron Bertrand has a clever trick:

In the most basic cases, we can implement this functionality in SQL Server by using OFFSET/FETCH. The problem is that anything that uses TOP or OFFSET will potentially have to scan everything in the index up until the page requested, which means that queries become slower and slower for higher page numbers. To achieve anything close to linear performance, you need to have a narrow, covering index for each sort option, or use columnstore as Erik Darling recommends here, or concede that some searches are just going to be slow. Throw in additional filtering, pulling data from other tables, and letting users dictate any sort order they want, and it becomes that much harder to tune for all cases.
I have a lot that I want to say about paging, and I will follow up with more content soon. Several years ago, I wrote about some ways to reduce the pain here, and it is long overdue for a refresh. For today’s post, though, I wanted to talk specifically about pagination when you have to order by large values. By “large” I mean any data type that can’t fit in an index key, like nvarchar(4000) or, really, anything that can’t lead in an index and/or would push the key past 1,700 bytes.

Read on for the scenario and how it all works.

Comments closed

When GROUP BY Order Matters

Published 2024-03-21 by Kevin Feasel

Brent Ozar runs some tests:

Sometimes when you do GROUP BY, the order of the columns does matter. For example, these two SELECT queries produce different results:
Their actual execution plans are wildly different:

Click through to learn when the ordering in a GROUP BY clause matters and when it doesn’t.

Comments closed

The Proper Use of Views and Inline UDFs

Published 2024-03-20 by Kevin Feasel

Erik Darling plays tic-tac-toe:

The problem is really the stuff that people stick into views. They’re sort of like a junk drawer for data. Someone builds a view that returns a correct set of results, which becomes a source of truth. Then someone else comes along and uses that view in another view, because they know it returns the correct results, and so on and so on. Worse, views tend to do a bunch of data massaging, left joining and coalescing and substringing and replacing and case expressioning and converting things to other things. The bottom line is that views are as bad as you make them.
The end result is a trash monster with a query plan that can only be viewed in full from deep space.

Read on to learn the use cases for views and inline UDFs, as well as a few important notes regarding performance of each. Views are like mogwai: they’re fine as long as you never get them wet and never let them eat after midnight. The problem is, far too many companies are apparently the business equivalent of all-you-can-eat buffets at water parks.

Inline user-defined functions are like patenting a device that lets you shoot yourself in both feet with one pull of the trigger. Which, if I understand things correctly, means you’ll need a Form 4 for each inline UDF.

Comments closed