x

Set based rather than cursor

hi, I have a cursor that is rather slow taking about 7 minutes to execute I know it could probably be done a lot faster using a set based approach

the problem with the query is i am populating a table of averages and those averages are taken over 1 day(yes i know!), 1 week, 1 fortnight and 1 month TIA for any help

Here is the code...

DECLARE @tmpDate AS smalldatetime

DECLARE @Yesterday AS smalldatetime

DECLARE @OneWeek AS smalldatetime

DECLARE @Fortnight AS smalldatetime

DECLARE @Month AS smalldatetime

DECLARE @Offset AS INT

DECLARE @Hour10to11AvOne AS BIGINT
DECLARE @Hour11to12AvOne AS BIGINT
DECLARE @Hour12to13AvOne AS BIGINT
DECLARE @Hour13to14AvOne AS BIGINT
DECLARE @Hour14to15AvOne AS BIGINT
DECLARE @Hour15to16AvOne AS BIGINT
DECLARE @Hour10to11AvWeek AS BIGINT
DECLARE @Hour11to12AvWeek AS BIGINT
DECLARE @Hour12to13AvWeek AS BIGINT
DECLARE @Hour13to14AvWeek AS BIGINT
DECLARE @Hour14to15AvWeek AS BIGINT
DECLARE @Hour15to16AvWeek AS BIGINT
DECLARE @Hour10to11AvFortnight AS BIGINT
DECLARE @Hour11to12AvFortnight AS BIGINT
DECLARE @Hour12to13AvFortnight AS BIGINT
DECLARE @Hour13to14AvFortnight AS BIGINT
DECLARE @Hour14to15AvFortnight AS BIGINT
DECLARE @Hour15to16AvFortnight AS BIGINT
DECLARE @Hour10to11AvMonth AS BIGINT
DECLARE @Hour11to12AvMonth AS BIGINT
DECLARE @Hour12to13AvMonth AS BIGINT
DECLARE @Hour13to14AvMonth AS BIGINT
DECLARE @Hour14to15AvMonth AS BIGINT
DECLARE @Hour15to16AvMonth AS BIGINT

DECLARE @Code AS NVARCHAR(20)

TRUNCATE TABLE [dbo].[COSStudiesAV]

SET ROWCOUNT 0
SELECT distinct(DATEADD(dd, DATEDIFF(dd, 0, DateAndTime), 0)) as 'DateAndTime'
INTO #TempTable 
FROM [dbo].[COSStudies] 

ORDER BY DateAndTime DESC

ALTER TABLE #TempTable ADD ColumnID INT IDENTITY(1,1)
SET @tmpDate  = (SELECT TOP 1 DateAndTime FROM #TempTable ORDER BY DateAndTime DESC)

IF YEAR(@tmpDate) = YEAR(GETDATE()) AND MONTH(@tmpDate) = MONTH(GETDATE()) AND DAY(@tmpDate) = DAY(GETDATE()) 
    SET @Offset = 1
ELSE
    SET @Offset = 0

SET @Yesterday = (SELECT DateAndTime FROM #TempTable WHERE ColumnID = @Offset +1)
SET @OneWeek = (SELECT DateAndTime FROM #TempTable WHERE ColumnID = @Offset +5)
SET @Fortnight = (SELECT DateAndTime FROM #TempTable WHERE ColumnID = @Offset +10)
SET @Month = (SELECT DateAndTime FROM #TempTable WHERE ColumnID = @Offset +20)  

print    @Yesterday
print    @OneWeek
print    @Fortnight
print    @Month

DECLARE CodeCursor CURSOR FORWARD_ONLY
FOR SELECT DISTINCT(COSStudies.Code) FROM [dbo].[COSStudies] ORDER BY CODE
OPEN CodeCursor

FETCH NEXT FROM CodeCursor
INTO @Code

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Hour10to11AvOne = (SELECT AVG(Hour10to11) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Yesterday AND Code = @Code)
SET @Hour11to12AvOne = (SELECT AVG(Hour11to12) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Yesterday  AND Code = @Code)
SET @Hour12to13AvOne = (SELECT AVG(Hour12to13) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Yesterday  AND Code = @Code)
SET @Hour13to14AvOne = (SELECT AVG(Hour13to14) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Yesterday  AND Code = @Code)
SET @Hour14to15AvOne = (SELECT AVG(Hour14to15) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Yesterday  AND Code = @Code)
SET @Hour15to16AvOne = (SELECT AVG(Hour15to16) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Yesterday  AND Code = @Code)
SET @Hour10to11AvWeek = (SELECT AVG(Hour10to11) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @OneWeek  AND Code = @Code)
SET @Hour11to12AvWeek = (SELECT AVG(Hour11to12) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @OneWeek  AND Code = @Code)
SET @Hour12to13AvWeek = (SELECT AVG(Hour12to13) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @OneWeek  AND Code = @Code)
SET @Hour13to14AvWeek = (SELECT AVG(Hour13to14) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @OneWeek  AND Code = @Code)
SET @Hour14to15AvWeek = (SELECT AVG(Hour14to15) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @OneWeek  AND Code = @Code)
SET @Hour15to16AvWeek = (SELECT AVG(Hour15to16) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @OneWeek  AND Code = @Code)
SET @Hour10to11AvFortnight = (SELECT AVG(Hour10to11) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Fortnight  AND Code = @Code)
SET @Hour11to12AvFortnight = (SELECT AVG(Hour11to12) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Fortnight  AND Code = @Code)
SET @Hour12to13AvFortnight = (SELECT AVG(Hour12to13) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Fortnight  AND Code = @Code)
SET @Hour13to14AvFortnight = (SELECT AVG(Hour13to14) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Fortnight  AND Code = @Code)
SET @Hour14to15AvFortnight = (SELECT AVG(Hour14to15) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Fortnight  AND Code = @Code)
SET @Hour15to16AvFortnight = (SELECT AVG(Hour15to16) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Fortnight  AND Code = @Code)
SET @Hour10to11AvMonth = (SELECT AVG(Hour10to11) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Month  AND Code = @Code)
SET @Hour11to12AvMonth = (SELECT AVG(Hour11to12) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Month  AND Code = @Code)
SET @Hour12to13AvMonth = (SELECT AVG(Hour12to13) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Month  AND Code = @Code)
SET @Hour13to14AvMonth = (SELECT AVG(Hour13to14) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Month  AND Code = @Code)
SET @Hour14to15AvMonth = (SELECT AVG(Hour14to15) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Month  AND Code = @Code)
SET @Hour15to16AvMonth = (SELECT AVG(Hour15to16) FROM COSStudies WHERE DateAndTime <= @Yesterday AND DateAndTime >= @Month  AND Code = @Code)

INSERT INTO [dbo].[COSStudiesAv]
       (
        [Code]
       ,[StartDateOne]
       ,[EndDateWeek]
       ,[EndDateFortnight]
       ,[EndDateMonth]   
       ,[Hour10to11AvOne]
       ,[Hour11to12AvOne]
       ,[Hour12to13AvOne]
       ,[Hour13to14AvOne]
       ,[Hour14to15AvOne]
       ,[Hour15to16AvOne]
       ,[Hour10to11AvWeek]
       ,[Hour11to12AvWeek]
       ,[Hour12to13AvWeek]
       ,[Hour13to14AvWeek]
       ,[Hour14to15AvWeek]
       ,[Hour15to16AvWeek]
       ,[Hour10to11AvFortnight]
       ,[Hour11to12AvFortnight]
       ,[Hour12to13AvFortnight]
       ,[Hour13to14AvFortnight]
       ,[Hour14to15AvFortnight]
       ,[Hour15to16AvFortnight]
       ,[Hour10to11AvMonth]
       ,[Hour11to12AvMonth]
       ,[Hour12to13AvMonth]
       ,[Hour13to14AvMonth]
       ,[Hour14to15AvMonth]
       ,[Hour15to16AvMonth]
    )
 VALUES
       (
       @Code
       ,@Yesterday
       ,@OneWeek
       ,@Fortnight
       ,@Month
       ,@Hour10to11AvOne
       ,@Hour11to12AvOne
       ,@Hour12to13AvOne
       ,@Hour13to14AvOne
       ,@Hour14to15AvOne
       ,@Hour15to16AvOne
       ,@Hour10to11AvWeek
       ,@Hour11to12AvWeek
       ,@Hour12to13AvWeek
       ,@Hour13to14AvWeek
       ,@Hour14to15AvWeek
       ,@Hour15to16AvWeek
       ,@Hour10to11AvFortnight
       ,@Hour11to12AvFortnight
       ,@Hour12to13AvFortnight
       ,@Hour13to14AvFortnight
       ,@Hour14to15AvFortnight
       ,@Hour15to16AvFortnight
       ,@Hour10to11AvMonth
       ,@Hour11to12AvMonth
       ,@Hour12to13AvMonth
       ,@Hour13to14AvMonth
       ,@Hour14to15AvMonth
       ,@Hour15to16AvMonth
       )

FETCH NEXT FROM CodeCursor
INTO @Code

END

CLOSE CodeCursor
DEALLOCATE CodeCursor
DROP TABLE #TempTable
more ▼

asked Feb 26 '11 at 04:42 PM in Default

michaelmfranz gravatar image

michaelmfranz
23 1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

Try something like this:

DECLARE @tmpDate AS smalldatetime
DECLARE @Yesterday AS smalldatetime
DECLARE @OneWeek AS smalldatetime
DECLARE @Fortnight AS smalldatetime
DECLARE @Month AS smalldatetime
DECLARE @Offset AS INT

TRUNCATE TABLE dbo.COSStudiesAV

SET ROWCOUNT 0

SELECT distinct(DATEADD(dd, DATEDIFF(dd, 0, DateAndTime), 0)) as 'DateAndTime'
INTO #TempTable 
FROM dbo.COSStudies
ORDER BY DateAndTime DESC

ALTER TABLE #TempTable ADD ColumnID INT IDENTITY(1,1)
SET @tmpDate  = (SELECT TOP 1 DateAndTime FROM #TempTable ORDER BY DateAndTime DESC)

IF YEAR(@tmpDate) = YEAR(GETDATE()) AND MONTH(@tmpDate) = MONTH(GETDATE()) AND DAY(@tmpDate) = DAY(GETDATE()) 
    SET @Offset = 1
ELSE
    SET @Offset = 0

SET @Yesterday = (SELECT DateAndTime FROM #TempTable WHERE ColumnID = @Offset +1)
SET @OneWeek = (SELECT DateAndTime FROM #TempTable WHERE ColumnID = @Offset +5)
SET @Fortnight = (SELECT DateAndTime FROM #TempTable WHERE ColumnID = @Offset +10)
SET @Month = (SELECT DateAndTime FROM #TempTable WHERE ColumnID = @Offset +20)

print       @Yesterday
print       @OneWeek
print       @Fortnight
print       @Month

WITH CTE AS
(
   SELECT C.code,
          T1.Hour10to11AvOne, T1.Hour11to12AvOne, T1.Hour12to13AvOne,
          T1.Hour13to14AvOne, T1.Hour14to15AvOne, T1.Hour15to16AvOne,
          T2.Hour10to11AvWeek, T2.Hour11to12AvWeek, T2.Hour12to13AvWeek,
          T2.Hour13to14AvWeek, T2.Hour14to15AvWeek, T2.Hour15to16AvWeek,
          T3.Hour10to11AvFortnight, T3.Hour11to12AvFortnight, T3.Hour12to13AvFortnight,
          T3.Hour13to14AvFortnight, T3.Hour14to15AvFortnight, T3.Hour15to16AvFortnight,
          T4.Hour10to11AvMonth, T4.Hour11to12AvMonth, T4.Hour12to13AvMonth,
          T4.Hour13to14AvMonth, T4.Hour14to15AvMonth, T4.Hour15to16AvMonth
     FROM (SELECT DISTINCT(COSStudies.Code) AS code
             FROM dbo.COSStudies) AS C
          OUTER APPLY
          (SELECT AVG(CS.Hour10to11) AS Hour10to11AvOne,
                  AVG(CS.Hour11to12) AS Hour11to12AvOne,
                  AVG(CS.Hour12to13) AS Hour12to13AvOne,
                  AVG(CS.Hour13to14) AS Hour13to14AvOne,
                  AVG(CS.Hour14to15) AS Hour14to15AvOne,
                  AVG(CS.Hour15to16) AS Hour15to16AvOne
             FROM dbo.COSStudies AS CS
            WHERE CS.DateAndTime <= @Yesterday
              AND CS.DateAndTime >= @Yesterday
              AND CS.Code = C.code) AS T1
          OUTER APPLY
          (SELECT AVG(CS.Hour10to11) AS Hour10to11AvWeek,
                  AVG(CS.Hour11to12) AS Hour11to12AvWeek,
                  AVG(CS.Hour12to13) AS Hour12to13AvWeek,
                  AVG(CS.Hour13to14) AS Hour13to14AvWeek,
                  AVG(CS.Hour14to15) AS Hour14to15AvWeek,
                  AVG(CS.Hour15to16) AS Hour15to16AvWeek
             FROM dbo.COSStudies AS CS
            WHERE CS.DateAndTime <= @Yesterday
              AND CS.DateAndTime >= @OneWeek
              AND CS.Code = C.code) AS T2
          OUTER APPLY
          (SELECT AVG(CS.Hour10to11) AS Hour10to11AvFortnight,
                  AVG(CS.Hour11to12) AS Hour11to12AvFortnight,
                  AVG(CS.Hour12to13) AS Hour12to13AvFortnight,
                  AVG(CS.Hour13to14) AS Hour13to14AvFortnight,
                  AVG(CS.Hour14to15) AS Hour14to15AvFortnight,
                  AVG(CS.Hour15to16) AS Hour15to16AvFortnight
             FROM dbo.COSStudies AS CS
            WHERE CS.DateAndTime <= @Yesterday
              AND CS.DateAndTime >= @Fortnight
              AND CS.Code = C.code) AS T3
          OUTER APPLY
          (SELECT AVG(CS.Hour10to11) AS Hour10to11AvMonth,
                  AVG(CS.Hour11to12) AS Hour11to12AvMonth,
                  AVG(CS.Hour12to13) AS Hour12to13AvMonth,
                  AVG(CS.Hour13to14) AS Hour13to14AvMonth,
                  AVG(CS.Hour14to15) AS Hour14to15AvMonth,
                  AVG(CS.Hour15to16) AS Hour15to16AvMonth
             FROM dbo.COSStudies AS CS
            WHERE CS.DateAndTime <= @Yesterday
              AND CS.DateAndTime >= @Month
              AND CS.Code = C.code) AS T4
)
INSERT INTO dbo.COSStudiesAv(Code, StartDateOne, EndDateWeek, EndDateFortnight, EndDateMonth,
                             Hour10to11AvOne, Hour11to12AvOne, Hour12to13AvOne,
                             Hour13to14AvOne, Hour14to15AvOne, Hour15to16AvOne,
                             Hour10to11AvWeek, Hour11to12AvWeek, Hour12to13AvWeek,
                             Hour13to14AvWeek, Hour14to15AvWeek, Hour15to16AvWeek,
                             Hour10to11AvFortnight, Hour11to12AvFortnight, Hour12to13AvFortnight,
                             Hour13to14AvFortnight, Hour14to15AvFortnight, Hour15to16AvFortnight,
                             Hour10to11AvMonth, Hour11to12AvMonth, Hour12to13AvMonth,
                             Hour13to14AvMonth, Hour14to15AvMonth, Hour15to16AvMonth)
   SELECT code, @Yesterday, @OneWeek, @Fortnight, @Month,
          Hour10to11AvOne, Hour11to12AvOne, Hour12to13AvOne,
          Hour13to14AvOne, Hour14to15AvOne, Hour15to16AvOne,
          Hour10to11AvWeek, Hour11to12AvWeek, Hour12to13AvWeek,
          Hour13to14AvWeek, Hour14to15AvWeek, Hour15to16AvWeek,
          Hour10to11AvFortnight, Hour11to12AvFortnight, Hour12to13AvFortnight,
          Hour13to14AvFortnight, Hour14to15AvFortnight, Hour15to16AvFortnight,
          Hour10to11AvMonth, Hour11to12AvMonth, Hour12to13AvMonth,
          Hour13to14AvMonth, Hour14to15AvMonth, Hour15to16AvMonth
     FROM CTE      

DROP TABLE #TempTable

Even though I should alter another parts of your script, or ask for more information to help me grasp the intention of them, I have concentrated on eliminating the cursor only, and I hope that this would work (it is hard to write queries when tables, and sample data is missing).

So try it, and let us know if it is working, and if it is fast enough.
more ▼

answered Feb 26 '11 at 05:29 PM

malpashaa gravatar image

malpashaa
404 3

thank you so much!

your query works perfectly and in only a couple of seconds!
Feb 26 '11 at 06:01 PM michaelmfranz
@michaelmfranz - if @malpashaa's answer has done the job for you, please take a second or two to mark it as correct. (Just click on the tick next to his answer...) Doing this helps you both gain reputation within the AskSSC community, and also helps others searching for similar problems to know that this can help them.
Feb 27 '11 at 05:36 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x57

asked: Feb 26 '11 at 04:42 PM

Seen: 647 times

Last Updated: Feb 26 '11 at 04:44 PM