asked on SQL Date Parameter decrease script performance in stored procedure
Good day.
I have narrowed down the issue of my previous post from yesterday. I have created multiple views and created a stored procedure which have 2 paramaters. A date and costcentre field. I have noted that if i fix the date value in the script it will run in 20 seconds. However the moment i add the date parameter and execute the proc it takes forever to run. I have tried casting this field as date and datetime with no effect. In the db the date field is a date data type.
What else can i try to increase the performance to where i fixed the date field to a actual value in the stored procedure
Regards
I have narrowed down the issue of my previous post from yesterday. I have created multiple views and created a stored procedure which have 2 paramaters. A date and costcentre field. I have noted that if i fix the date value in the script it will run in 20 seconds. However the moment i add the date parameter and execute the proc it takes forever to run. I have tried casting this field as date and datetime with no effect. In the db the date field is a date data type.
What else can i try to increase the performance to where i fixed the date field to a actual value in the stored procedure
Regards
SQL
Last Comment
ste5an
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
This is only necessary on the older verisons of SQL Server. Use instead OPTION (RECOMPILE) with the query causing the bad plan reusage..
Forgot to add: Using WITH RECOMPILE on the procedure is more overhead than usin the query oiption.
https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/