WHEN (TEMP2.Time NOT BETWEEN TEMP2.StartDateTime AND TEMP2.EndDateTime) OR ((TEMP2.SEMESTERID IS NULL) or (TEMP2. WHEN (TEMP2.Time BETWEEN TEMP2.StartDateTime AND TEMP2.EndDateTime) AND Splitting it up gives the optimizer some breathing room in optimization.Īn example with 2 temporary tables (it would be cleaner to define the tables and columns and using INSERT INTO). With the limited information given, the easiest solution would be using a stored procedure and splitting up the CTE's in temporary tables.ĬTE's are not materialized so your view is in fact one big convoluted query.īigger queries are harder to optimize. WHEN (CTE2.Time NOT BETWEEN CTE2.StartDateTime AND CTE2.EndDateTime) OR ((CTE2.SEMESTERID IS NULL) or (CTE2.Booker IS NULL)) AND WHEN (CTE2.Time BETWEEN CTE2.StartDateTime AND CTE2.EndDateTime) AND I've done some research but so far nothing currently listed addresses or fixes this particular problem. Any thoughts would be greatly appreciated. If I leave Acrobat DC running the PDF file will open in Acrobat DC almost immeadiately. AS DATE) AS DATETIME))),Ĭoalesce((ctt./60),rbt.) ,Ĭoalesce(ctt., rbt.) ,Ĭoalesce(ctt., rbt.) When I double click on a PDF it takes a long time for Acrobat DC to launch. rbt ON ro.RoomCode = rbt.facilityname and (ro.LocalTime BETWEEN rbt.startdatetime AND rbt.enddatetime)ĬAST(CAST(ro. ctt ON ro.RoomCode = ctt.ROOMID AND (ro.LocalTime BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE) WHEN Ceiling(AVG(CAST(ro.Occupancy AS DECIMAL))) BETWEEN 0.1 AND 1.0ĭATEADD(HOUR, DATEPART(HOUR, ro.), DATEADD( MINUTE, 30 * CAST((DATEDIFF(MINUTE, '19000101', ro.) / 30) % 2 AS INT),ĬAST(CAST(ro. Inner JOIN iot.DeviceMessageHistory b ON A.

Here is the script of the view CREATE VIEW. This also happens when I try to import to an excel file, it will take 50 + minutes.

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. The view contains group by clause, case statements and CTEs. Here, GPS takes half an hour or more (time) to get the current location coordinates.I have created a view which gets data from 5 tables, 3 of them containing 200k+ rows,340k+ rows and 2.1mill+ rows.

Super sume pro is taking too long