CODEWITHSUNDEEP
sqlsql-servert-sqlparameters
asmgx
asmgx

Reputation: 7994

Parameters causing query to slow down

I have this query

DECLARE @Company VARCHAR(20) = 'ABC'
DECLARE @Train INT = 1

SELECT L.*
FROM vemd_episodes_firstnet_currentPatients L 
INNER JOIN vemd_episodes E ON E.Company = L.Company 
                           AND E.cpid = L.cpid  
                           AND E.dDate = L.dDate
WHERE L.Company = @Company
  AND ((census_datetime NOT IN (SELECT MAX(census_datetime) FROM vemd_episodes_firstnet_currentPatients ) AND @Train = 1)
       OR (census_datetime IN (SELECT MAX(census_datetime) FROM vemd_episodes_firstnet_currentPatients ) AND @Train = 0))

It takes forever to finish.

After 1 minute it only retrieve 400 records and is still running.

Total number of records this query should fetch is around 500,000 records.

enter image description here

But when I hard code the parameters in the query

SELECT L.*
FROM vemd_episodes_firstnet_currentPatients L 
INNER JOIN vemd_episodes E ON E.Company = L.Company 
                           AND E.cpid = L.cpid  
                           AND E.dDate = L.dDate
WHERE L.Company = 'ABC'
  AND ((census_datetime NOT IN (SELECT MAX(census_datetime) FROM vemd_episodes_firstnet_currentPatients ) AND 1 = 1)
       OR (census_datetime IN (SELECT MAX(census_datetime) FROM vemd_episodes_firstnet_currentPatients ) AND 1 = 0))

it is extremely fast and retrieves 500k records in 16 seconds.

Why is using parameters in the where clause causing this issue? And how to fix it?

Edit :

I could not keep it running till the end

so I select top 1000 and got execution plan

enter image description here

Upvotes: 0

Views: 190

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Try using window functions:

SELECT L.*
FROM (SELECT L.*,
             MAX(census_datetime) OVER () as max_census_datetime
      FROM vemd_episodes_firstnet_currentPatients L 
     ) L JOIN
     vemd_episodes E
     ON E.Company = L.Company AND
        E.cpid = L.cpid AND
        E.dDate = L.dDate
WHERE L.Company = @Company AND
      ((census_datetime <> max_census_datetime AND @Train = 1) OR
       (census_datetime = max_census_datetime AND @Train = 0)
      );

The optimizer should find it easier to generate an appropriate execution plan with a simpler where clause.

Upvotes: 2

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can try the query with the recompile option OPTION (RECOMPILE) With parameterized queries, SQL sometimes chooses the wrong execution plan and insist to use it. 

DECLARE @Company VARCHAR(20) = 'ABC'
DECLARE @Train INT = 1

SELECT L.*
FROM vemd_episodes_firstnet_currentPatients L 
INNER JOIN vemd_episodes E ON E.Company = L.Company 
                           AND E.cpid = L.cpid  
                           AND E.dDate = L.dDate
WHERE L.Company = @Company
  AND ((census_datetime NOT IN (SELECT MAX(census_datetime) FROM vemd_episodes_firstnet_currentPatients ) AND @Train = 1)
       OR (census_datetime IN (SELECT MAX(census_datetime) FROM vemd_episodes_firstnet_currentPatients ) AND @Train = 0))
OPTION  (RECOMPILE)

Upvotes: 4

Related Questions