SP taking 15 minutes, but the same query when executed returns results in 1-2 minutes

This is the footprint of parameter-sniffing. See here for another discussion about it; SQL poor stored procedure execution plan performance – parameter sniffing

There are several possible fixes, including adding WITH RECOMPILE to your stored procedure which works about half the time.

The recommended fix for most situations (though it depends on the structure of your query and sproc) is to NOT use your parameters directly in your queries, but rather store them into local variables and then use those variables in your queries.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)