#tsql2sday #200 — I bet a query it’s bad if I see…

I know we’re on the same page…

Prelude

This month I want to take the opportunity to participate in #tsql2sday on its 200th edition… I swear it’s random, I didn’t wait for this specific number :-).

Each month, a host proposes a topic, and on that date volunteers publish blog posts at the same time talking about it and this month’s host is the one and only Brent Ozar that invites to share what brings us to think a query is bad when we saw it.

If I see it, I know where to start to fix…

Being a data practitioner specialized in data warehouses and business intelligence means your primary job is to move and transform data as fast as you can because C-levels won’t wait too long to have that number printed on their dashboard.

One of the most common engagements is helping troubleshoot performance issues and speed up processing in existing data warehouses.

Most of the time, it all starts with a conversation with the customer explaining how the process logic is driven by stored procedures and telling you that their “big data” can’t go any faster because it’s simply too much data.

The next question is how much data we are talking about, and most of the time it’s definitely not big data. So, let’s see this processing stored procedure… right-click -> Script Procedure as -> CREATE To -> and you see it…

[embed]

When you’re searching for a way to make something go faster and you see a CURSOR, I bet you’ve found the guilty.

A cursor is a way of processing data row by row that is very easy to understand because it’s close to what you learn in school when professors explain arrays, and you can easily visualize its behavior in your mind while it goes from one row to the next.

But in IT, the easiest solution isn’t always the best, and in this case the performance is really bad because you’re not using SQL as it was designed. SQL Server is very good at processing many rows as sets, and if you can leverage parallelism and its internal structures, like indexes, it can do it at blazing-fast speed.

I can’t count how many times I’ve fixed performance issues just by removing CURSORs and I want to share an example from a 2025 engagement.

During a data warehouse rebuild, we were told by the customer that a process could include only two years of aggregated data because that phase was too slow. They shared that they needed a wider time frame and more detailed data, but they had failed multiple times to change the process because processing time exceeded the available window.

The process basically picks up time-range rows (valid-from / valid-to) and “unpivots” them, exploding them into daily events. It’s a quite common scenario that, in this case, had been managed with a CURSOR reading the source table row by row and writing the daily events (one INSERT at a time… yes… a CURSOR inside another CURSOR) into a support table before inserting them into the final fact table.

As soon as we saw this, we invested two hours in understanding the business requirement, then refactored the procedure using a CROSS APPLY approach. We managed to unpivot and explode the data in seconds instead of minutes, going back ten years and to the finest granularity possible, reaching customer’s happiness.

Obviously, the CURSOR code, as I said, is easy to follow because you can see in your mind what is happening step by step, compared to a single SELECT with a CROSS APPLY that is much harder to visualize, but the latter simply outperforms it and unlocks scenarios that previously seemed impossible.

As many experts in this industry say, one of the first things a data practitioner should learn is to “think in sets”. If you do, your data pipelines will run much faster.

Conclusions

CURSOR is one of the first things that catches my eye when I’m asked to troubleshoot a slow SQL process. More often than not, the real issue isn’t the amount of data but how it’s processed. Learning to think in sets is one of the most valuable skills for anyone working with SQL Server and data in general.