Improve CPU consumption of query [closed]

Question

I'm researching why the following query consumes a lot of CPU. Please chime in and suggest any improvements/recommendations that can be made or why this query seems to consume high CPU. Or if there is anything structurally wrong in the query design.

(
        @fromDate DATETIME
        ,@toDate DATETIME
        )

SET DATEFIRST 7;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT TOP (25) [T1].[C13] AS C1
    ,[T1].[C14] AS C2
    ,[T1].[C15] AS C3
    ,[T1].[C16] AS C4
    ,[T1].[C17] AS C5
    ,[T1].[C18] AS C6
    ,[T1].[C19] AS C7
    ,[T1].[C20] AS C8
    ,[T1].[C21] AS C9
    ,[T1].[C22] AS C10
    ,[T2].[Name] AS C11
    ,[T2].[ID] AS C12
FROM (
    SELECT TOP (25) [T3].[NetObjectType] AS C13
        ,[T3].[NetObjectID] AS C14
        ,[T3].[NetObjectValue] AS C15
        ,[T3].[EventID] AS C16
        ,[T3].[Acknowledged] AS C17
        ,DateAdd(second, DateDiff(second, GetDate(), GetUtcDate()), [T3].[EventTime]) AS C18
        ,[T3].[EventType] AS C19
        ,[T3].[Message] AS C20
        ,ISNULL([T4].[BackColor], 0) AS C21
        ,[T5].[NodeID] AS C22
        ,0 AS C23
    FROM dbo.Events AS T3
    LEFT JOIN dbo.NodesData AS T5 ON [T3].[NetworkNode] = [T5].[NodeID]
        AND 0 = 0
    LEFT JOIN dbo.EventTypes AS T4 ON [T3].[EventType] = [T4].[EventType]
        AND 0 = 0
    WHERE [T3].[Acknowledged] = 'false'
        AND [T3].[EventTime] >= @fromDate
        AND [T3].[EventTime] <= @toDate
    ORDER BY [C18] DESC
    ) AS T1
INNER JOIN dbo.Sites AS T2 ON [T2].[SiteID] = [T1].[C23]
ORDER BY [C6] DESC

This is an external vendor query. I have no authority to make changes, only suggestions.

Have to have TOP (25). The results are displayed on a website and they are only interested in the last 25 events and it updates on a schedule.

Here is the estimated execution plan:

Answer 1

Ordering by this might be slowing it down quite a bit:

DateAdd(second, DateDiff(second, GetDate(), GetUtcDate()), [T3].[EventTime]) AS C18

I would change ORDER BY [C18] DESC to order by [T3].[EventTime] desc to see if that changes the cost.

---

Your join for dbo.Sites references [T1].[C23] which is hard-coded 0 as [C23] in your [T3] query. So for each row you are returning from [T3] you are returning every row from dbo.Sites.SiteId = 0 without any other criteria. Is that intended?

---

On another note... your column and table alias choices are frightening.

Answer 2

This is just silly C23 is hard coded

, 0 AS C23

Then you join on it

ON [T2].[SiteID] = [T1].[C23]

You

LEFT JOIN dbo.NodesData AS T5 
  ON [T3].[NetworkNode] = [T5].[NodeID] 

Rather than just use [T3].[NetworkNode]

Odering by [T3].[EventTime]twice and only taking 25
So if [T2].[SiteID] is not unique this will be non-deterministic

SELECT TOP (25) 
       [T1].[C13] AS C1
     , [T1].[C14] AS C2
     , [T1].[C15] AS C3
     , [T1].[C16] AS C4
     , [T1].[C17] AS C5
     , [T1].[C18] AS C6
     , [T1].[C19] AS C7
     , [T1].[C20] AS C8
     , [T1].[C21] AS C9
     , [T1].[C22] AS C10
     , [T2].[Name] AS C11
     , [T2].[ID] AS C12
FROM ( SELECT TOP (25) 
              [T3].[NetObjectType] AS C13
            , [T3].[NetObjectID] AS C14
            , [T3].[NetObjectValue] AS C15
            , [T3].[EventID] AS C16
            , [T3].[Acknowledged] AS C17
            , DateAdd(second, DateDiff(second, GetDate(), GetUtcDate()), [T3].[EventTime]) AS C18
            , [T3].[EventType] AS C19
            , [T3].[Message] AS C20
            , ISNULL([T4].[BackColor], 0) AS C21
            , [T5].[NodeID] AS C22
            , 0 AS C23
       FROM dbo.Events AS T3
       LEFT JOIN dbo.NodesData AS T5 
              ON [T3].[NetworkNode] = [T5].[NodeID]   
       LEFT JOIN dbo.EventTypes AS T4 
              ON [T3].[EventType] = [T4].[EventType]
       WHERE [T3].[Acknowledged] = 'false'
         AND [T3].[EventTime] >= @fromDate
         AND [T3].[EventTime] <= @toDate
       ORDER BY [C18] DESC
    ) AS T1
INNER JOIN dbo.Sites AS T2 
        ON [T2].[SiteID] = [T1].[C23]
ORDER BY [C6] DESC