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.