CODEWITHSUNDEEP
sqlperformancet-sqlif-statement
Triynko
Triynko

Reputation: 19194

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 Plans Detailed 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

Upvotes: 14

Views: 584

Answers (2)

richardmgreen
richardmgreen

Reputation: 11

I'd put both queries through Database Tuning Advisor (and take the results with a pich of salt). It may suggest another index to create that would speed things up.

Upvotes: 0

richardmgreen
richardmgreen

Reputation: 11

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).

Upvotes: 1

Related Questions