May 10, 2005 at 1:39 pm
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,
May 10, 2005 at 3:14 pm
First a non-sequitur: I hate GUIDs.
You're half way there. Use IDENTITY function (SELECT IDENTITY( INT, 1, 1) AS sid ) to insert into a temp table (using TOP 100 PERCENT with ORDER BY) and order things sequentially so that you can use this field to easily join one row to the next row. Like:
(sid = name of sequential id identity column)
SELECT ...
FROM #t a
JOIN #t b on a.sid = b.sid - 1
AND a.keycols = b.keycols
Using that join method you can filter any overlaps non overlaps and suck those SIDs into another table that you can then join back to your original result set to update related columns/flags...
May 17, 2005 at 3:26 pm
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
John, thank you for your quick reply. I'm sorry I haven't been able to post a reply to your proposed solution. It turns out their were even bigger fish to fry in loop problem. Thanks again for your help.
Kyle.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply