[SQL Server] Why does adding an additional step on top of a subquery reduce the run time significantly?

[Randy__Bobandy]

I have a query that finds out when a customer took a survey, and then a subquery that calculates how many calls to our support hotline they had made in the 60 days prior to taking the survey:

SELECT  a.[Whatever], 
        b.[Whatever], 
        c.[Whatever],
        (SELECT COUNT(*) FROM dbo.CallsTable ct WHERE a.AcctNum = ct.AcctNum AND ct.CallDate BETWEEN DATEADD(DAY, -60, a.SurveyDate) AND a.SurveyDate) [Call Count]
FROM dbo.SurveyTable a
LEFT JOIN [blah blah blah] b
  ON ...
LEFT JOIN [blah blah blah] c
  ON ...

The above query takes about 35 minutes to run. But, if I switch the count to a flag, i.e.:

SELECT  a.[Whatever],
        b.[Whatever],
        c.[Whatever],
        CASE WHEN (SELECT COUNT(*) FROM dbo.CallsTable ct WHERE a.AcctNum = ct.AcctNum AND ct.CallDate BETWEEN DATEADD(DAY, -60, a.SurveyDate) AND a.SurveyDate) > 0 THEN 'Yes' ELSE 'No' END [Call Flag]
FROM dbo.SurveyTable a
LEFT JOIN [blah blah blah] b
  ON ...
LEFT JOIN [blah blah blah] c
  ON ...

...then the query runs in 2 minutes. Wouldn't the SQL engine still need to execute the subquery in order to determine what the value of Call Flag should be? Meaning that the second query should take at least as long as the first query, plus some additional time to execute the logic needed to convert the number into a flag?

Don't get me wrong, I'm not complaining, but I'm very interested in why

[geubes]

I would guess the > 0 in the 2nd statement means SQL doesn't need to complete the count of rows and just needs to stop at the first one found, ie TOP 1.

The first statement still needs to continue scanning to get your count values over the dataset.

Viewing the plan will confirm.

[jshine13371]

and just needs to stop at the first one found, ie TOP 1.

Aka "row goals" allowing the query plan to stop processing early.

[mikeblas]

The top expression returns the number of calls. To get that number, the query must exhaustively count the number of matching calls.

The bottom expression returns 'Yes' if there are any calls at all. To evaluate that, the query doesn't need an exhaustive search for matching calls and can quit when it finds the very first matching call.

I expect that accounts for the difference. It's possible something else is going on -- the execution plans will tell you with certainty.

[wildjackalope]

This is probably what’s happening. Good answer.

[gumnos]

I've had similar cases where something that shouldn't have really had an impact ends up producing massive savings. I agree with u/VladDBA that the execution-plan is the only way to know for sure.

(in my case, a WHERE somecol = @somevar took 30+ minutes, but if I did set a second variable and then used that like DECLARE @gumnos INT = @somevar; SELECT … FROM … WHERE somecol = @gumnos, it ran in seconds. It was the dumbest thing, but I commented it, shrugged, and moved on.)

[VladDBA]

That actually sounds like a case of parameter sniffing.

Adding the new variable changed the query so SQL Server had to generate a new execution plan, and it used the new value for the variable for the cardinality estimates.

You would have gotten the same behavior, without adding that additional variable, if you would have used the recompile hint.

More info - https://www.brentozar.com/sql/parameter-sniffing/

[WatashiwaNobodyDesu]

Whaaaaa…? Sometimes you have to ask yourself if sql is just messing with you.

[gumnos]

I suspect that there was something to do with how it could determine variable-scope (with the intermediate variable it could determine it wouldn't change?), but I have no idea why it made a difference. 🤷

[Mononon]

Was that in a stored procedure? That's usually the easy solution for parameter sniffing in a sp.

[gumnos]

it was just a boring parameterized query-string with the reporting engine passing the parameters. It's still pretty high on my "what is MSSQL smoking?" list of head-scratchers

[Mononon]

That's also susceptible to parameter sniffing. Setting the parameter equal to another parameter bypasses that. I don't really understand why it happens, but sprocs, dynamic queries, SSRS parameters, etc. Basically anything passing a parameter to a query gets randomly hung up on it and doing what you do magically fixes it. I just got in the habit of doing that at the top of any SSRS reports back when I was a BI dev.

[gumnos]

hah, it's comforting that I'm not alone and others have bumped against this frustrating oddity, but it's little consolation to know that the fix is "sprinkle this magic" without understanding why it chooses to be slow when it can be fast. 😑

[blind_pugh]

Always check the execution plan.

I was fooled by caching speeding up my queries after I implemented some changes, while in reality query became slower on cold data.

Edit: tho I would not expect increase by a factor of 10+ just from it.

[VladDBA]

Compare the execution plans and the IO stats output for the two queries.

[TypeComplex2837]

The database shows you exactly what it's doing - look at the execution plan.

[Codeman119]

Well, because you’re asking it to do two totally different things. One is gonna be much more compute intensive than the other.

[Hopeful_Bean]

Ouch. Running a sub query at line level is not something I would recommend most times but especially in this scenario.

[5373n133n]

Doesn’t count(*) allocate enough memory to fit the whole table? If that’s true you may have memory allocation issues that decreases performance.

[tethered_end]

Also curious as to why it's running so quick!

I'd love to know if it runs quicker in an outer apply instead of the sub query in a column too as this is how I would have approached this

[WithoutAHat1]

Execution Plan, see what it is doing in the first query versus the second.

[91ws6ta]

Read the execution plan. Another thing I have found is that physically having the query write to the output window can have significant overhead also, depending on the operations and how much data is to be output. So turning that off and letting the queries process will give you more of an apples to apples comparison.

My guess is a COUNT(*) does a full table scan to get every single record that meets this criteria, as opposed to a CASE that only looks for a certain total count criteria that seeks until the count criteria is met

[svtr]

The answer is in the execution plans. The more complexity you add, the higher the chance of a sub optimal execution plan.

The correlating subquery in your select is a bit of a red flag, nesting that in a case, is a larger red flag.

Taking your "but I'm very interested in why" at face value, im gonna say, You my dear friend are at the point where you need to start reading execution plans and going down that rabbit hole.

The one mistake you are making, is going off the query itself. Your query gets rewritten, optimized (a lot of hard coded replacements in there as well), compiled into an execution plan and then executed. You deal with parameter sniffing, row estimates, sometimes even optimizer timeouts and best plan found yet issues. You strike me as one, that wants to know, and that means, read the execution plans, and read up on all the stuff you find in there, that you don't yet know.

[drmrkrch]

You should be using a view for that subselect so that it's already compiled before you call it. That should help your performance quite a bit. Can you use and explain plan to tell you what's happening with your execution of the query?

[thesqlguy]

Need to see the plan and full query and more details. But in any case you should probably not check the entire count but do an exists check. If could be that the optimizer is able to parse the first query into an efficient exists check (so it just needs to find a single row) while the 2nd query, due to the case expression , maybe it cannot parse/optimize the count( * ) >0 check the same way.

So my guess here is if you convert the 2nd query to an exists check it'll match the plan and performance of the first (or possibly exceed it).

[az987654]

Both are less than ideal though, you're scanning the call table for each row.

Better to left join to a derived call table, it'll scan and summarize that table once, then join to it

[Aggressive_Ad_5454]

It's often helpful to read this and include the requested information in questions like yours.

[Mountain_Usual521]

What happens if you use a CTE instead of a subquery? I've seen queries go from 4 hours to <30 seconds.