SQL Server Performance

Tuning a loop statement

Discussion in 'T-SQL Performance Tuning for Developers' started by dom, Dec 19, 2007.

  1. dom New Member

    Hi,
    If someone would be able to help me cut down the execution time on this piece of code I would be most greatful. In the [NEMTIME] table I've got a list of every halfhour from the start of 2004 through the end of 2011. I'm trying to get the sum of the another field in table [master contracts] where the dates in the [NEMTIME] are between the dates in the [master contracts] table.
    It takes about 20mins to return a result. It would help me alot if I could cut this time down.declare
    @theTime datetime -- the current interval to assessdeclare
    @maxTime datetime -- maximum date interval in nemtimeselect
    @theTime = min(nemtime) from nemtimeselect
    @maxTime = max(nemtime) from nemtime -- while the current time is less than the latest datewhile
    @theTime <= @maxTimebegin
    -- cover is the name of the 2nd column in the nemtime tableupdate nemtime
    -- set the total volume to the sum between the start and end datesset cover = (select sum(volume) from [Contract Master] where @theTime >= start_date and @theTime <= end_date)
    where nemtime = @theTime
    -- add 30 minutes to obtain the next interval and repeat
    select @theTime = dateadd(mi, 30, @theTime)end
  2. ranjitjain New Member

    Hi,
    I have understood your query correctly then I feel you dont need this loop at all.
    Copy this sample code along with row data and run in QA to check the desired result:declare
    @t1 table(thetime datetime,cover int)insert
    @t1(thetime) select
    '2007-12-14 00:00:00' union all select
    '2007-12-14 00:30:00' union all select
    '2007-12-14 01:00:00' union all select
    '2007-12-14 01:30:00' union all select
    '2007-12-14 02:00:00' union all select
    '2007-12-14 02:30:00' union all select
    '2007-12-14 03:00:00' union all select
    '2007-12-14 03:30:00' declare
    @t2 table(startdate datetime,enddate datetime,volume int)insert
    @t2select
    '2007-12-14 00:25:00','2007-12-14 00:45:00',5 union all select
    '2007-12-14 00:29:00','2007-12-14 00:39:00',4 union all select
    '2007-12-14 00:59:00','2007-12-14 01:00:00',3 union all select
    '2007-12-14 01:20:00','2007-12-14 01:40:00',7 union all select
    '2007-12-14 01:30:00','2007-12-14 01:57:00',6 union all select
    '2007-12-14 02:30:00','2007-12-14 02:30:00',4 union all select
    '2007-12-14 02:49:00','2007-12-14 03:09:00',12 union all select
    '2007-12-14 02:50:00','2007-12-14 00:10:00',90 select
    thetime,sum(volume) from @t1 t1 join @t2 t2 ONt1
    .thetime between t2.startdate and t2.enddategroup
    by thetime
  3. satya Moderator

    What kind of index you have on thsi table?
    Is Nemtime is part of any index?

Share This Page