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

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

user11702
1#
user11702 Published in September 20, 2018, 4:43 pm

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?

share|improve this question
  • 3
    Google Parameter sniffing. Use OPTION (Recompile) in select and see the magic – Pரதீப் Feb 13 at 15:59
  • You can clear the plan cache and re-test. --> sqlskills.com/blogs/glenn/… – Ross Bush Feb 13 at 16:00
  • Parameter sniffing is the most likely, but this is also a good read on a similar issue - Benjamin Nevarez - Statistics on Ascending Keys – GarethD Feb 13 at 16:09
  • 3
    As an aside, it is best to avoid the sp_ prefix for your stored procedures – GarethD Feb 13 at 16:10
  • 1
    Don't be afraid to use whitespace when writing your query. Everything being aligned to the left margin makes your statements very difficult to read. – Larnu Feb 13 at 16:12

1 Answer 1

active oldest votes
up vote 0 down vote
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO