Slow running BIDS report query...OR clauses in WHERE branch using IN sub queries

Question

I have a report with 3 sub reports and several queries to optimize in each. The first has several OR clauses in the WHERE branch and the OR's are filtering through IN options which are pulling sub-queries.

I say this mostly from reading this SO post. Specifically LBushkin's second point.

I'm not the greatest at TSQL but I know enough to think this is very inefficient. I think I need to do two things.

1. I know I need to add indexes to the tables involved.

2. I think the query can be greatly enhanced.

So it seems that my first step would be to improve the query. From there I can look at what columns and tables are involved and thus determine the indexes.

At this point I haven't posted table schemas as I'm looking more for options / considerations such as using a cte to replace all the IN sub-queries.

If needed I will definitely post whatever would be helpful such as physical reads etc.

SELECT DISTINCT 
  auth.icm_authorizationid, 
  auth.icm_documentnumber
FROM 
  Filteredicm_servicecost AS servicecost 

INNER JOIN Filteredicm_authorization AS auth ON 
auth.icm_authorizationid = servicecost.icm_authorizationid 

INNER JOIN Filteredicm_service AS service ON 
service.icm_serviceid = servicecost.icm_serviceid 

INNER JOIN Filteredicm_case AS cases ON 
service.icm_caseid = cases.icm_caseid

WHERE
  (cases.icm_caseid IN
    (SELECT icm_caseid FROM Filteredicm_case AS CRMAF_Filteredicm_case)) 

  OR (service.icm_serviceid IN
    (SELECT icm_serviceid FROM Filteredicm_service AS CRMAF_Filteredicm_service)) 

  OR (servicecost.icm_servicecostid IN
    (SELECT icm_servicecostid FROM Filteredicm_servicecost AS CRMAF_Filteredicm_servicecost)) 

  OR (auth.icm_authorizationid IN
    (SELECT icm_authorizationid FROM Filteredicm_authorization AS CRMAF_Filteredicm_authorization)) 

Answer 1

EXISTS is usually much faster than IN as the query engine is able to optimize it better.

Try this:

WHERE EXISTS (SELECT 1 FROM FROM Filteredicm_case WHERE icm_caseid = cases.icm_caseid)
    OR EXISTS (SELECT 1 FROM Filteredicm_service WHERE icm_serviceid = service.icm_serviceid)
    OR EXISTS (SELECT 1 FROM Filteredicm_servicecost WHERE icm_servicecostid = servicecost.icm_servicecostid)
    OR EXISTS (SELECT 1 FROM Filteredicm_authorization WHERE icm_authorizationid = auth.icm_authorizationid)

Furthermore, an index on Filteredicm_case.icm_caseid, an index on Filteredicm_service.icm_serviceid, an index on Filteredicm_servicecost.icm_servicecostid, and an index on Filteredicm_authorization.icm_authorizationid will increase performance of this query. They look like they should be keys already, however, so I suspect that indices already exist.

However, unless I'm misreading, there's no way this WHERE clause will ever evaluate to anything other than true.

The clause you wrote says WHERE cases.icm_caseid IN (SELECT icm_caseid FROM Filteredicm_case AS CRMAF_Filteredicm_case). However, cases is an alias to Filteredicm_case. That's the equivalent of WHERE Filteredicm_case.icm_caseid IN (SELECT icm_caseid FROM Filteredicm_case AS CRMAF_Filteredicm_case). That will be true as long as Filteredicm_case.icm_caseid isn't NULL.

The same error in logic exists for the remaining portions in the WHERE clause:

(service.icm_serviceid IN (SELECT icm_serviceid FROM Filteredicm_service AS CRMAF_Filteredicm_service))

service is an alias for Filteredicm_service. This is always true as long as icm_serviceid is not null

(servicecost.icm_servicecostid IN (SELECT icm_servicecostid FROM Filteredicm_servicecost AS CRMAF_Filteredicm_servicecost)) 

servicecost is an alias for Filteredicm_servicecost. This is always true as long as icm_servicecostid is not null.

(auth.icm_authorizationid IN (SELECT icm_authorizationid FROM Filteredicm_authorization AS CRMAF_Filteredicm_authorization))

auth is an alias for Filteredicm_authorization. This is always true as long as icm_authorizationid is not null.

I don't understand what you're trying to accomplish.