Avatar of Cav IT
Cav IT
Flag for South Africa 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
SQL

Avatar of undefined
Last Comment
ste5an

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
ste5an

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Éric Moreau

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Cav IT

ASKER
Thank You Gentleman. Looks like it was parameter sniffing as you said. I managed to fix the issue by adding with RECOMPILE to the stored procedure

https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
ste5an

This is only necessary on the older verisons of SQL Server. Use instead OPTION (RECOMPILE) with the query causing the bad plan reusage..
ste5an

Forgot to add: Using WITH RECOMPILE on the procedure is more overhead than usin the query oiption.
Your help has saved me hundreds of hours of internet surfing.
fblack61