Slow parameterized queries

I was working on a stored procedure when suddenly test runs started to timeout (>30 seconds). I extracted the procedure, replaced the parameters with the same constants, and ran it. Finished instantly. I spent a lot of time focusing on the “stored procedure” aspect of it (trying things such as WITH RECOMPILE and shadow variables), but the problem turned out to revolve around the “parameterized” aspect of it.

We ended up reducing the query to this small query, where each table/view has at most 10,000 rows:

SELECT *
  FROM tbl6ControlEntity CE 
  LEFT JOIN dbo.tbl7Accounts AS PM 
  ON PM.record_gid = CE.program_manager_gid
WHERE 																					
  (@Insp = 0 OR CE.control_entity_gid IN
    (SELECT XIP.control_entity_gid FROM 
    viewWSBIS_GetControlEntities_Inspections XIP WHERE 
    XIP.master_cd_flag = 1 AND XIP.cert_no = 
      (SELECT AC.inspector_cert_no FROM tbl7Accounts AC 
       WHERE AC.record_gid = @InspGid)))
ORDER BY CE.sort_br_no	

When we put in constants for the parameters @Insp and @InspGid, the query ran in less than 1 second. When we declared and set them in the query window, it took indefinite amounts of time. Using a temp table instead of the view helped, but wasn’t a complete solution. We tried replacing the inner-most uncorrelated subquery with a join. No impact.

I tried most of the common suggestions, such as OPTIMIZE FOR @Insp = 1 and OPTIMIZE FOR UNKNOWN. No impact.

Finally, I added OPTION (RECOMPILE) to the query, which fixed the problem. I’m surprised the WITH RECOMPILE on the stored procedure did not, however.

In general, the need for OPTION (RECOMPILE) indicates a need to rebuild statistics, however, in the cases where flags are used to control execution of certain subconditions, I’m not sure the statistics can cope.

See also: OPTION (RECOMPILE) is Always Faster; Why?