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, 2011 at 04:42 PM in Default

avatar image

michaelmfranz
23 1 1 4

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

1 answer: sort voted first

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, 2011 at 05:29 PM

avatar image

malpashaa
404 1 2 5

thank you so much!

your query works perfectly and in only a couple of seconds!

Feb 26, 2011 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, 2011 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.

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:

x68

asked: Feb 26, 2011 at 04:42 PM

Seen: 1152 times

Last Updated: Feb 26, 2011 at 04:44 PM

Copyright 2016 Redgate Software. Privacy Policy