Identifying groups of overlapping times ...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Identifying groups of overlapping times ...

Author

Topic

kbrown@pccsinc.org
Starting Member

2 Posts

Posted - 2005-05-11 : 07:05:02

Hey All,

I need help with speeding up a SP. The SP does a series of tests on several tables in my database to find “Exceptions”. This SP takes almost a minute to complete. The bulk of this time is spent performing one test which involves looping through a set of records. I have a need to add this kind of loop to another table within the SP, so the SP would then take even longer to complete. I need to see if anyone can help to eliminate this loop by providing a set-based solution.

The following bit of code sets up a temporary table which will be looped through. The table holds information about services that have been provided during a given time frame. I need to identify all the overlapping time periods for a particular StaffID. This first Insert will insert the relevant information, including the number of overlaps for each record. Items with no overlaps are ignored at this point:

--Insert items from SPSET with number of overlapping staff entries

INSERT #OPERIODS(OGUID, STAFFID, ODATE, TIMEIN, TIMEOUT, FROMTABLE, OLAPS)
SELECT t1.SPGUID, t1.STAFFID, t1.SPDATE, t1.TIMEIN, t1.TIMEOUT, t1.FROMTABLE, COUNT(*) AS Overlaps
  FROM dbo.SPSET t1
  LEFT JOIN dbo.SPSET t2 
            ON t1.STAFFID = t2.STAFFID 
            AND t1.SPDATE = t2.SPDATE 
            AND t1.FROMTABLE = t2.FROMTABLE
            AND t1.SPGUID <> t2.SPGUID
  WHERE (t2.TIMEIN > t1.TIMEIN and t2.TIMEIN < t1.TIMEOUT) 
            OR (t2.TIMEOUT > t1.TIMEIN and t2.TIMEOUT < t1.TIMEOUT)
            OR (t2.TIMEIN <= t1.TIMEIN and t2.TIMEOUT >= t1.TIMEOUT)
            OR (t2.TIMEIN = t1.TIMEIN)
            OR (t2.TIMEOUT = t1.TIMEOUT)
GROUP BY t1.SPGUID, t1.STAFFID, t1.SPDATE, t1.TIMEIN, t1.TIMEOUT, t1.FROMTABLE
ORDER BY t1.STAFFID ASC, t1.OverLAPS DESC

Next, the SP loops through each record of the above temp table and assigns a GUID to any records that have overlaps:


--Loop through #Operiods to assign groupguid's
SELECT @maxrows = COUNT(*)
  FROM #OPERIODS

SELECT @i = 1

While @i <= @maxrows
  BEGIN
          SELECT @GroupGUID = SP.GROUPGUID
              FROM dbo.SPSET SP
              JOIN #OPERIODS OP
                ON OP.OGUID = SP.SPGUID
              WHERE OP.OPSID = @i

            IF @GroupGuid IS NOT NULL
              BEGIN
                SELECT @i = @i + 1
              END
            ELSE
              BEGIN
                SELECT @GroupGUID = NewID()

                SELECT @OP_STAFFID = OP.STAFFID, @OP_ODATE = OP.ODATE, @OP_TIMEIN = OP.TIMEIN, @OP_TIMEOUT = OP.TIMEOUT, @SP_NUMCLIENTS = SP.NUMCLIENTS, @OP_FROMTABLE = OP.FROMTABLE
                  FROM #OPERIODS OP
                  JOIN dbo.SPSET SP
                    ON OP.OGUID = SP.SPGUID
                  WHERE OPSID = @i
                    
               UPDATE dbo.SPSET
               SET GROUPGUID = @GroupGUID
               WHERE SPDATE = @OP_ODATE
               AND STAFFID = @OP_STAFFID
               AND FROMTABLE = @OP_FROMTABLE
               AND 
                 (
                 (TIMEIN > @OP_TIMEIN and TIMEIN < @OP_TIMEOUT) 
                 OR (TIMEOUT > @OP_TIMEIN and TIMEOUT < @OP_TIMEOUT)
                 OR (TIMEIN <= @OP_TIMEIN and TIMEOUT >= @OP_TIMEOUT)
                 OR (TIMEIN = @OP_TIMEIN)
                 OR (TIMEOUT = @OP_TIMEOUT)
                 )
               AND GROUPGUID IS NULL

         SELECT @i = @i + 1
     END
END

That loop is the portion that takes up the most time. I would like to find a set-based solution that will insert the GUID for each group of overlapping timeframes.

This final bit of code updates the original set of records - from which the overlaps were found – with a GUID for any items that do not have an overlapping timeframe:

--UPDATE SPSET with GroupGUID for those  with only one entry in group
UPDATE dbo.SPSET
  SET GROUPGUID = NewID()
  WHERE GROUPGUID IS NULL

This GUID (GroupGUID) is used throughout the rest of the SP to identify several other types of exceptions and perform various other tasks.

If anyone has any suggestions about how to eliminate the looping portion of this code, please let me know ASAP.

Thanks in advance for any help that can be provided!

Best Regards,

Kyle Brown

kbrown@pccsinc.org
Starting Member

2 Posts

Posted - 2005-05-17 : 17:07:32

Just an update:

This question has been resolved ... thank you Google ....

If anyone would like to see the solution to this dilemma, you can view the rather lengthy thread on Tek-Tips here:

http://www.tek-tips.com/viewthread.cfm?qid=1058500&page=1

Kyle.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources