SQL stored procedure is much slower than SQL statement execution

The problem of parameter sniffing

Cause: (1) A parameter sniffer may have occurred, and the input parameter assigned to the stored procedure for the first time will generate an execution plan based on the input parameters for the stored procedure, so if the first input parameter is not representative (for example, most of the query input parameters are a value, but the first time the stored procedure is entered with a B value), is likely to be slower than ad hoc queries, although ad hoc queries need to recompile the execution plan but choose a more efficient plan.
Try using the same parameters as the Ad hoc query to execute the stored procedure, and then compare the execution plan for both.
(2) Usually the stored procedure has its own set settings, such as set ANSI_NULLS on, and ad hoc queries are usually not included, and these set settings also affect the execution plan.
Try adding the same set settings as the stored procedure in the Ad hoc query, and then compare the execution plan.

Solution:

1) Use the DECLARE declared variable instead of the argument: using Set @[email protected], the SQL statements that appear @thedate are all replaced with @variable.

(2) Hide the affected SQL statements, such as:

A) Put the affected SQL statements into one of the sub-stored procedures, for example, we call a word stored procedure after the @thedate setting becomes today to pass the @thedate as a parameter.

b) Use sp_executesql to execute the affected SQL. The execution plan is not executed unless the sp_executesql statement is executed.

c) Use dynamic SQL ("EXEC (@sql)") to execute the affected SQL.

SQL stored procedure is much slower than SQL statement execution