1

I have created a view which gets data from 5 tables, 3 of them containing 200k+ rows,340k+ rows and 2.1mill+ rows.

The view contains group by clause, case statements and CTEs.

When I do a simple select 1000 rows or select * from the view, it will take a long time approximately 15 minutes just to get top 1000 ROWS.

This also happens when I try to import to an excel file, it will take 50 + minutes. Any idea on how to speed it up?

Here is the script of the view

CREATE VIEW [iot].[Occupancy_View]
AS

WITH CTE1
AS
(
SELECT A.[TpID], A.[RoomCode], A.[RoomName], A.[BuildingCode],A.[SchoolCode],A.[SchoolCode1], b.[DeviceID], b.[LocalTime], b.[Occupancy], b.[Temperature],c.[semesterid],c.[academicyear]
FROM iot.Device A
inner JOIN iot.DeviceMessageHistory b ON  A.[DeviceID] = b.[DeviceID] 
LEFT OUTER JOIN iot.SEMESTER c ON b.[LocalTime] BETWEEN c.[semesterstartDATE] and c.[semesterendDATE]
),
CTE2
AS
(
SELECT
    ro.[RoomCode],
    ro.[RoomName],
    ro.[BuildingCode],
    ro.[SchoolCode],
    ro.[SchoolCode1],
    ro.[DeviceID],
    ro.[TpID],
    ro.[semesterid],
    ro.[academicyear],
    CAST(avg(ro.[Temperature]) AS decimal(10, 1)) AS [Temperature],
    CASE
        WHEN Ceiling(AVG(CAST(ro.Occupancy AS DECIMAL))) BETWEEN 0.1 AND 1.0
                  THEN '1'
        ELSE
            '0'
    END   AS [Occupancy],
    DATEADD(ss,-1,
    DATEADD(HOUR, DATEPART(HOUR, ro.[LocalTime]), DATEADD( MINUTE, 30 * CAST((DATEDIFF(MINUTE, '19000101', ro.[LocalTime]) / 30) % 2 AS INT),
    CAST(CAST(ro.[LocalTime] AS DATE) AS DATETIME))) 
    )
    AS [Time],
    (DATEPART(day,ro.[LocalTime])-1)/7 + 1 AS [WEEKNO],
    rbt.[booker] AS Booker,
    coalesce((ctt.[duration]/60),rbt.[duration]) as Duration,
    coalesce(ctt.[Day],rbt.[day]) AS [Day],
    coalesce(ctt.[CLASSSTARTDATE], rbt.[startdatetime]) AS [StartDateTime],
    coalesce(ctt.[CLASSENDDATE], rbt.[enddatetime]) AS [EndDateTime]


    FROM
    CTE1 ro
    LEFT OUTER JOIN
        [iot].[ClassTimeTable] ctt ON ro.RoomCode = ctt.ROOMID AND (ro.LocalTime BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE)
    LEFT OUTER JOIN 
        [iot].[RoomBooking] rbt ON ro.RoomCode = rbt.facilityname and (ro.LocalTime BETWEEN rbt.startdatetime AND rbt.enddatetime)
                    GROUP BY
    ro.[RoomCode],
    ro.[RoomName],
    ro.[BuildingCode],
    ro.[SchoolCode],
    ro.[SchoolCode1],
    ro.[DeviceID],
    ro.[TpID],
    ro.[semesterid],
    ro.[academicyear],
    DATEADD(HOUR, DATEPART(HOUR, ro.[LocalTime]), DATEADD( MINUTE, 30 * CAST((DATEDIFF(MINUTE, '19000101', ro.[LocalTime]) / 30) % 2 AS INT),
    CAST(CAST(ro.[LocalTime] AS DATE) AS DATETIME))),
    (DATEPART(day,ro.[LocalTime])-1)/7 + 1,
    rbt.[booker],
    coalesce((ctt.[duration]/60),rbt.[duration]) ,
    coalesce(ctt.[Day],rbt.[day]),
    coalesce(ctt.[CLASSSTARTDATE], rbt.[startdatetime]) ,
    coalesce(ctt.[CLASSENDDATE], rbt.[enddatetime]) 

),
CTE3 
AS
(
    SELECT * 
    FROM CTE2
    CROSS APPLY
        (
        SELECT 
         CASE
                WHEN  (CTE2.Time BETWEEN CTE2.StartDateTime AND CTE2.EndDateTime) AND
                        (CAST(CTE2.[Occupancy]AS DECIMAL)) > 0.0
                        THEN 'Booked / Occupied'

                WHEN  (CTE2.Time BETWEEN CTE2.StartDateTime AND CTE2.EndDateTime) AND 
                        (CAST(CTE2.[Occupancy]AS DECIMAL)) <= 0.0
                        THEN 'Booked / Unoccupied'

                WHEN  (CTE2.Time NOT BETWEEN CTE2.StartDateTime AND CTE2.EndDateTime) OR ((CTE2.SEMESTERID IS NULL ) or (CTE2.Booker IS NULL)) AND 
                        (CAST(CTE2.[Occupancy]AS DECIMAL)) > 0.0
                        THEN 'Not Booked / Occupied'

                WHEN (CTE2.Time NOT BETWEEN CTE2.StartDateTime AND CTE2.EndDateTime) OR ((CTE2.SEMESTERID IS NULL) or (CTE2.Booker IS NULL)) AND
                        (CAST(CTE2.[Occupancy]AS DECIMAL)) <= 0.0
                        THEN 'Not Booked / Unoccupied'


              --(Add another conditon)
            ELSE 
            'ERROR'
            END AS ClassroomStatus
            ) AS CS


)
SELECT * FROM CTE3;
GO


Here is the execution plan: https://imgur.com/a/cFPSk3M

5
  • 2
    Please start by including an execution plan. Also, are you running any database maintenance jobs? Are statistics up to date?
    – vonPryz
    Feb 10 '20 at 9:52
  • CTE2 look suspicious... but, as was told earlier, include execution plan...
    – Luuk
    Feb 10 '20 at 10:17
  • @vonPryz ive attached the execution plan Feb 11 '20 at 2:30
  • @Luuk i ve attached the execution plan Feb 11 '20 at 2:30
  • 1
    Please include the plan in text format, as explained in Brent's article: Right click on the execution plan and click View XML. Copy/paste those contents, and then go to PasteThePlan.com. There, you can upload your query plan to share a link with the public. That's because a picture of the plan is missing a lot of information.For example, there are warnings on sort and parallelism, but the picture doesn't tell what the warnings actually say.
    – vonPryz
    Feb 11 '20 at 5:39
1

With the limited information given, the easiest solution would be using a stored procedure and splitting up the CTE's in temporary tables.

CTE's are not materialized so your view is in fact one big convoluted query.

Bigger queries are harder to optimize. Splitting it up gives the optimizer some breathing room in optimization.

An example with 2 temporary tables (it would be cleaner to define the tables and columns and using INSERT INTO).

CREATE PROCEDURE [iot].[Occupancy_View]
AS

SELECT A.[TpID], A.[RoomCode], A.[RoomName], A.[BuildingCode],A.[SchoolCode],A.[SchoolCode1], b.[DeviceID], b.[LocalTime], b.[Occupancy], b.[Temperature],c.[semesterid],c.[academicyear]
INTO #TEMP1
FROM iot.Device A
inner JOIN iot.DeviceMessageHistory b ON  A.[DeviceID] = b.[DeviceID] 
LEFT OUTER JOIN iot.SEMESTER c ON b.[LocalTime] BETWEEN c.[semesterstartDATE] and c.[semesterendDATE]


SELECT
    ro.[RoomCode],
    ro.[RoomName],
    ro.[BuildingCode],
    ro.[SchoolCode],
    ro.[SchoolCode1],
    ro.[DeviceID],
    ro.[TpID],
    ro.[semesterid],
    ro.[academicyear],
    CAST(avg(ro.[Temperature]) AS decimal(10, 1)) AS [Temperature],
    CASE
        WHEN Ceiling(AVG(CAST(ro.Occupancy AS DECIMAL))) BETWEEN 0.1 AND 1.0
                  THEN '1'
        ELSE
            '0'
    END   AS [Occupancy],
    DATEADD(ss,-1,
    DATEADD(HOUR, DATEPART(HOUR, ro.[LocalTime]), DATEADD( MINUTE, 30 * CAST((DATEDIFF(MINUTE, '19000101', ro.[LocalTime]) / 30) % 2 AS INT),
    CAST(CAST(ro.[LocalTime] AS DATE) AS DATETIME))) 
    )
    AS [Time],
    (DATEPART(day,ro.[LocalTime])-1)/7 + 1 AS [WEEKNO],
    rbt.[booker] AS Booker,
    coalesce((ctt.[duration]/60),rbt.[duration]) as Duration,
    coalesce(ctt.[Day],rbt.[day]) AS [Day],
    coalesce(ctt.[CLASSSTARTDATE], rbt.[startdatetime]) AS [StartDateTime],
    coalesce(ctt.[CLASSENDDATE], rbt.[enddatetime]) AS [EndDateTime]

    INTO #TEMP2
    FROM
    #TEMP1 ro
    LEFT OUTER JOIN
        [iot].[ClassTimeTable] ctt ON ro.RoomCode = ctt.ROOMID AND (ro.LocalTime BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE)
    LEFT OUTER JOIN 
        [iot].[RoomBooking] rbt ON ro.RoomCode = rbt.facilityname and (ro.LocalTime BETWEEN rbt.startdatetime AND rbt.enddatetime)
                    GROUP BY
    ro.[RoomCode],
    ro.[RoomName],
    ro.[BuildingCode],
    ro.[SchoolCode],
    ro.[SchoolCode1],
    ro.[DeviceID],
    ro.[TpID],
    ro.[semesterid],
    ro.[academicyear],
    DATEADD(HOUR, DATEPART(HOUR, ro.[LocalTime]), DATEADD( MINUTE, 30 * CAST((DATEDIFF(MINUTE, '19000101', ro.[LocalTime]) / 30) % 2 AS INT),
    CAST(CAST(ro.[LocalTime] AS DATE) AS DATETIME))),
    (DATEPART(day,ro.[LocalTime])-1)/7 + 1,
    rbt.[booker],
    coalesce((ctt.[duration]/60),rbt.[duration]) ,
    coalesce(ctt.[Day],rbt.[day]),
    coalesce(ctt.[CLASSSTARTDATE], rbt.[startdatetime]) ,
    coalesce(ctt.[CLASSENDDATE], rbt.[enddatetime]) 


    SELECT * 
    FROM #TEMP2 TEMP2
    CROSS APPLY
        (
        SELECT 
         CASE
                WHEN  (TEMP2.Time BETWEEN TEMP2.StartDateTime AND TEMP2.EndDateTime) AND
                        (CAST(TEMP2.[Occupancy]AS DECIMAL)) > 0.0
                        THEN 'Booked / Occupied'

                WHEN  (TEMP2.Time BETWEEN TEMP2.StartDateTime AND TEMP2.EndDateTime) AND 
                        (CAST(TEMP2.[Occupancy]AS DECIMAL)) <= 0.0
                        THEN 'Booked / Unoccupied'

                WHEN  (TEMP2.Time NOT BETWEEN TEMP2.StartDateTime AND TEMP2.EndDateTime) OR ((TEMP2.SEMESTERID IS NULL ) or (TEMP2.Booker IS NULL)) AND 
                        (CAST(TEMP2.[Occupancy]AS DECIMAL)) > 0.0
                        THEN 'Not Booked / Occupied'

                WHEN (TEMP2.Time NOT BETWEEN TEMP2.StartDateTime AND TEMP2.EndDateTime) OR ((TEMP2.SEMESTERID IS NULL) or (TEMP2.Booker IS NULL)) AND
                        (CAST(TEMP2.[Occupancy]AS DECIMAL)) <= 0.0
                        THEN 'Not Booked / Unoccupied'


              --(Add another conditon)
            ELSE 
            'ERROR'
            END AS ClassroomStatus
            ) AS CS
1
  • Can i use a create view but implement the temp tables instead of cte in it? Feb 11 '20 at 1:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.