Press "Enter" to skip to content

Category: Query Tuning

Aggregations and Distinct Counts in Power BI

Published 2023-02-23 by Kevin Feasel

Phil Seamark doesn’t have time to wait for Power BI to count:

This article aims to show how you can speed up distinct count calculations in Power BI using the built-in user-defined aggregations feature. The user-defined aggregation feature in Power BI is designed to work with direct query models and usually gets used for calculations such as SUM, MIN, MAX etc. However, it can also work well for distinct count calculations using the pattern shown in this article.

It’s an interesting partial aggregation approach which works really well when the distinct count is a small percentage of the total.

Comments closed

FILTER vs CASE in Postgres

Published 2023-02-08 by Kevin Feasel

Lukas Eder hits on an interesting data point:

I’ve found an interesting question on Twitter, recently. Is there any performance impact of using FILTER in SQL (PostgreSQL, specifically), or is it just syntax sugar for a CASE expression in an aggregate function?

Click through for the answer and your daily reminder that SQL variants aren’t pure fourth generation languages—if they were, the optimizer would take all possible constructs of a given desired operation and generate the same execution plan for all of them.

Comments closed

Minimizing Cross-Highlighting on Direct Query

Published 2023-01-31 by Kevin Feasel

Dany Hoter explains why cross filtering might not be the best when you are using Direct Query in Power BI:

Every time a user clicks on a row or a column in a visual, every other visual in the page feels an urge to refresh itself and respond to the click.

Visual that are based on direct query may issue multiple queries for each refresh.

Depending on the number of measures used in the visual, the number of generated queries can be as high as 10 or even more for a single visual.

Read on for more information and what you can do to avoid this problem.

Comments closed

Performance Comparing DISTINCT to GROUP BY

Published 2023-01-20 by Kevin Feasel

Reitse Eskens does a performance comparison:

A few days ago, I heard someone stating that Group By was much quicker than Distinct. Less disk impact, less memory etc.
So, I thought I’d find out if it’s true or not because I found it interesting. I always thought there was no difference. I tested a single small table and found no difference in speed, reads or execution plan. But that’s no real world example. Usually the tables contain a lot of data and are joined to other tables.

Click through for the results of Reitse’s analysis.

Comments closed

SQL Server 2022 Query Store Hints

Published 2022-12-01 by Kevin Feasel

Matthew McGiffen takes a hint:

Another neat little feature in SQL Server 2022 is Query Store Hints. This is the ability to apply a query hint through Query Store rather than having to modify existing code or fiddle around with plan guides.


Query hints are a way to influence optimizer behaviour towards generating desired execution plans for a given query. The word “hint” is a bit of a misnomer as usually they mandate what you wish to happen.

Right. They’re ‘hints’ in the way that my wife ‘hints’ that I should take out the garbage.

Comments closed

SQL Server 2022: Forcing Optimized Plans

Published 2022-11-17 by Kevin Feasel

Matthew McGiffen wants you to put yourr wallet and your best plans in the bag:

I’ve been reading Bob Ward’s book “SQL Server 2022 Revealed” and one of the new features in Intelligent Query Processing (IQP) jumped out at me. Not because it’s going to rock my world but rather it reminded me of an idea I had when I first started playing with Query Store when it came out.

I thought it would be cool if the execution plans cached in Query Store could be used to populate the plan cache after an instance restart, removing the need for all queries to be recompiled. I even raised a suggestion on Microsoft Connect for it to be considered as a new feature.

Click through to see what Microsoft came up with along these lines.

Comments closed

AT TIME ZONE Performance and an Alternative

Published 2022-11-14 by Kevin Feasel

Joe Obbish looks at time zones:

Queries that use the AT TIME ZONE operator may perform worse than desired. For example, Jonathan Kehayias blogged about performance struggles with AT TIME ZONE at sqlskills.com. One key difference between Jonathan’s opinion and my own is that I enjoy writing these kinds of posts.

Read on for the details and check out some work Joe has done around time zone friendly functions as an alternative to AT TIME ZONE.

Comments closed

Lessons Learned from Index Tuning

Published 2022-11-02 by Kevin Feasel

Lee Markum has seven lessons for us:

SQL Server indexing basics are critical to query and server performance. Resources, like CPU and disk, are affected by the indexes that you have, or the ones you’re missing.

In the StackOverflow2013 database we’re going to look at Badges and users. Specifically, I want to start by seeing what badges a user has and when that user received them. Some badges, because of the type of badge it is, can be awarded more than once.

Click through for a demonstration.

Comments closed

DOP Feedback in SQL Server 2022

Published 2022-10-21 by Kevin Feasel

Kate Smith points out a new feature in SQL Server 2022:

In SQL Server 2022, we introduced a new feature called DOP feedback. This feature will look at any parallel query and determine if it might perform better with a lower degree of parallelism than currently being used. For example, perhaps 16 threads will perform better than 20 if there are a lot of waits on other threads. It will test out the new degree of parallelism and, either decide that this was a good change and keep the 16 threads, or it will revert to previous levels of parallelism and go back to 20 threads. If the new degree of parallelism is good, then this optimization is persisted inside the query store and will be applied appropriately to a query for future executions. 

Read on for an overview of how it works and what protections are in place to keep it from going completely bonkers. Well, more completely bonkers than what you already have.

Comments closed

Extended Event Duration Filtering and Looping

Published 2022-10-20 by Kevin Feasel

Erik Darling notes a problem with time-based filters on operations:

There are six waitfor commands that each pause for 1 second. In between them are queries that finish in milliseconds.

If I watch the event output, eventually, I’ll see this:

Okay, so the stored procedure took more than 5 seconds, but… no individual query took more than 5 seconds.

This happens a lot and I’m not sure there’s a good answer for it.

Comments closed