Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why would wrapping a TSQL query in an if statement increase its runtime significantly?

I'm having odd performance difference when running the same query two different ways. Literally, the only difference is whether its wrapped in an if statement.

This query is actually part of a larger one, but I've isolated it as the culprit.

Running the query by itself returns almost immediately (returns 0)

select COUNT(*) from Responses r where r.ResponseID not in (
    select ResponseID from data.GamingReport_Computerized
))

Ultimately, I want to avoid running complex calculations based on the result of that query, so I wrap it in an if statement like so, but it takes much longer to run (~10 seconds) and I can't figure out why:

if (0 = (select COUNT(*) from Responses r where r.ResponseID not in (
    select ResponseID from data.GamingReport_Computerized
)))
begin select 'update will be skipped to save time' end
else begin select 'missing rows will be inserted' end

The data set does not change so in both cases the result is zero / 'update will be skipped', and yet running these two versions of the same query always results in the first version completing quickly, and the second version taking about 10-12 seconds to complete.

Update: here is a screenshot of the query execution plans for comparison. Why are they so different? Its very unexpected to me.

Query Execution PlansDetailed Index Scan/Seek performance

Update 2: In response to a suggestion in the comments, I wanted to mention that the following query performs identically to the second version above, with the same execution plan and no increase in performance (i.e. using 'exists' instead of comparing count(*) to zero makes no difference).

if exists(select 1 from Responses r where r.ResponseID not in (
    select ResponseID from data.GamingReport_Computerized
))
begin select 'missing rows will be inserted' end
else begin select 'update will be skipped to save time' end
like image 690
Triynko Avatar asked Mar 17 '14 20:03

Triynko


People also ask

How does in clause affect performance in SQL Server?

For that small list of values, SQL Server will use each value in optimization which gives it better information than if you have data in a temp table. If you have more than 63 values in the IN clause, it will build an internal temp table, so the behaviour will be the same.


1 Answers

I've run into this problem before (quite a few times). Can you check if you're indexes are fragmented (and defrag them if necessary) and statistics are up to date? That could have quite an impact on performance and is quite possibly the issue (as the seek is taking longer than the scan).

like image 93
richardmgreen Avatar answered Oct 24 '22 04:10

richardmgreen