July 8, 2019 at 3:32 pm
I have a stored procedure with two date variables, Start date, End date. If I run this in excel, it takes forever and eventually deadlocks.
ALTER PROCEDURE [dbo].[lost_sales_v2] @SD datetime, @ED datetime
If I take the variables out of the procedure name and embed a set date range in the query, it returns in less than 10 seconds.
ALTER PROCEDURE [dbo].[lost_sales_v2]
AS BEGIN
DECLARE @SD datetime
DECLARE @ED datetime
SET @SD = '6-23-19'
SET @ED = '6-24-19 23:59:59'
I cannot figure out why the addition of asking for a date range would matter in run time. Any suggestions on where to start?
July 8, 2019 at 3:55 pm
No idea. Maybe use XE and see what the difference is, grab the plans.
July 9, 2019 at 11:19 am
You are dealing with two distinct approaches to querying. The first, parameterized queries, involves what is called parameter sniffing. The process gets the values from the parameters and uses those specific values against the statistics of the column(s) and/or index(es) involved in the query for the optimization process. The second is using local variables. These are not sniffed, except during a recompile. Instead of specific values, it uses averages from the statistics. It sounds like you may be dealing with a parameter sniffing issue.
As Steve says, capture the execution plans and compare them. Specifically you'll be looking at the row estimates for each and comparing them. Also, look at the specific compile time values for the parameterized plan and compare those to the statistics for the column or index involved. That will give you enough information to understand why things are occurring as they are.
To fix it, completely depends on what the problem is. Are the statistics accurate or out of date? If out of date, a statistics update may solve the issue. If accurate, you have to look to other solutions from query hints (the OPTIMIZE FOR or RECOMPILE hints specifically) or plan forcing. It's impossible for me to tell you which of these solutions is best based on the information at hand.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2019 at 3:48 pm
Thank you both. I will dig deeper and see if I can narrow the issue by looking at the execution.
July 9, 2019 at 3:56 pm
I cannot figure out why the addition of asking for a date range would matter in run time. Any suggestions on where to start?
I suspect this one:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply