Home Store Procedure/Query running slow based on dates in Parameter?
Reply: 1

Store Procedure/Query running slow based on dates in Parameter?

ShaneW
1#
ShaneW Published in 2018-02-13 15:55:39Z

I have a query (that I also made a stored procedure for). In the query, when I use 2017 dates ('2017-09-30' -> '2017-12-31') The query runs fast (1 sec). When I run this as store procedure with date parameters, the query takes forever to run (greater than 5 mins for the same data, partial results).

In addition, when I use 2018 (only) dates, both the query and the store procedure takes even longer to run for less data. I had the store procedure running for 8 mins with no rows loading despite that fact that data exists.

I am not sure what is going on. Below are some times

Query

Between '20170930' and '20171231' - 1 sec  - 863 rows
Between '20170930' and '20180108' - 0 sec  - 875 rows
Between '20180101' and '20180108' - 4:15 m - 12 rows

Store Procedure

Nothing really fully loads, maybe after 5 - 8 mins I will have partial results

This is not happening with other store procedures in my database. When I run the third date sets on the query, my CPU Usage and Memory are normal.

Any thoughts? Thanks!

Query:

{ Select 
M.AdultQty,
M.ChildQty, 
M.GuestQty, 
U.Usetime, 
cast(U.usetime as date) as Date, 
Right(U.usetime, 7) as 'Time', 
P.Zip as 'Zipcode'
from MUsage M
left join Usage U 
on U.usageid = M.usageid
left join pass P
on M.ScannedID = P.ID
where  p.Level = 24
and cast(U.usetime as date) between '20170930' and '20180108'
and u.code != 12
order by UseTime }

Stored Procedure:

ALTER PROCEDURE [dbo].[SP_MEM]
(
@startdate date, 
@enddate date
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

Select 
M.AdultQty,
M.ChildQty, 
M.GuestQty, 
U.Usetime, 
cast(U.usetime as date) as Date, 
Right(U.usetime, 7) as 'Time', 
P.Zip as 'Zipcode'
from MUsage M
left join Usage U 
on U.usageid = M.usageid
left join pass P
on M.ScannedID = P.ID
where  p.level = 24
and cast(U.usetime as date) between @startdate and @enddate
and u.code != 12
order by UseTime

END

Update:

By adding OPTION (RECOMPILE) the store procedure works for 2017 date ranges but not 2018?

PSK
2#
PSK Reply to 2018-02-14 07:43:11Z

I have a query (that I also made a stored procedure for). In the query, when I use 2017 dates ('2017-09-30' -> '2017-12-31') The query runs fast (1 sec). When I run this as store procedure with date parameters, the query takes forever to run (greater than 5 mins for the same data, partial results).

Definitely looks like a case of parameter sniffing. Please try following options.

  • Create SQL Server Stored Procedures using the WITH RECOMPILE Option
  • Use the SQL Server Hint OPTION (RECOMPILE)
  • Use the SQL Server Hint OPTION (OPTIMIZE FOR)
  • Use Dummy Variables on SQL Server Stored Procedures
  • Disable SQL Server Parameter Sniffing at the Instance Level

By adding OPTION (RECOMPILE) the store procedure works for 2017 date ranges but not 2018?

Suggestions

  • Avoid using != or <>

In almost all cases when we use the != operator (or any other operator in conjunction with the NOT operator, i.e.. NOT IN) index seeks will not be performed and instead a table/index scan is required.

You can try same thing with > and < operators.

  • I recommend putting NON CLUSTERED INDEX ON usetime column if it is not there.

  • Avoid CAST in your WHERE.

As a general rule, doing CASTs or CONVERTs on items in your SELECT clause cost almost nothing. (Note that CASTs or CONVERTs in WHERE or ON clauses can be very expensive since they will keep SQL Server from making effective use of indexes).

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.317088 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO