This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(291.2, 8%, 38%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHH%3C/text%3E%3C/svg%3E)
This query select data from a master table (AlarmHistory AH) and joins calculated data (CTE 1 and 2 recursive) from a detail table (AlarmHistoryLog). This part works very good.
The result set should then join data from a table (Tree_Alarms - a recursive tree table), thats the ctethird which should "build" the path to the node (record) where Tree_Alarms.TechnicalAddress = AlarmHistory.TechnicalAddress
I have the query below which consists of three CTE's and I need to add a condition to the ctethird's root to avoid that it recurses the full tree too many times.
I need to fetch the ctethird data by using the TechnicalAddress in the CTE "root" query.
I just cannot figure out how to access the AlarmHistory/AH.TechnicalAddress inside ctethird - even when I look at ctefirst where root "eventid" must be comming from outside, I cannot replicate that.
I admit - I'm by no means strong in sql and especially not CTE.
I hope someone can see the solution to this.
declare @FromDate datetime = N'11/15/20'
declare @ToDate datetime = N'12/16/20'
BEGIN
with
ctefirst as
(
SELECT AH2.EventID FROM AlarmHistory AH2 WHERE ah2.EventID = EventId
),
ctesecond as
(
SELECT ia.eventID, SUM(DATEDIFF(SECOND,ia.eventTime,r.eventTime)) AS seconds, COUNT(*) as AlarmSessions
FROM alarmHistoryLog ia
INNER JOIN alarmHistoryLog r
ON ia.EventId = r.EventId
AND r.EventTypeId = 2
AND r.EventSeq = (SELECT MIN(eventSeq)
FROM alarmHistoryLog WHERE ia.EventId = EventId and eventSeq > ia.EventSeq AND EventTypeId = 2)
WHERE ia.EventTypeId = 0 and ia.EventId > 0
group by ia.EventId
),
ctethird as
(
select NodeID, ParentID, NodeLevel, NodeName,
cast('' as varchar(max)) as N0,
cast('' as varchar(max)) as N1,
cast('' as varchar(max)) as N2,
cast('' as varchar(max)) as N3,
cast(TechnicalAddress as varchar(max)) as N4
from Tree_Alarms TA2 where TA2.NodeLevel = 4
**-- this is where I need the help
and TA2.TechnicalAddress = AH.TechnicalAddress**
union ALL
select
Tre.NodeID, Tre.ParentID, Tre.NodeLevel, Tre.NodeName,
N0 = CASE
WHEN Tre.NodeLevel = 0 AND TreCTE.N0 = '' THEN cast (Tre.NodeName as varchar(max))
ELSE cast (TreCTE.N0 as varchar(max))
END ,
N1 = CASE
WHEN Tre.NodeLevel = 1 AND TreCTE.N1 = '' THEN cast (Tre.NodeName as varchar(max))
ELSE cast (TreCTE.N1 as varchar(max))
END ,
N2 = CASE
WHEN Tre.NodeLevel = 2 AND TreCTE.N2 = '' THEN cast (Tre.NodeName as varchar(max))
ELSE cast (TreCTE.N2 as varchar(max))
END ,
N3 = CASE
WHEN Tre.NodeLevel = 3 AND TreCTE.N3 = '' THEN cast (Tre.NodeName as varchar(max))
ELSE cast (TreCTE.N3 as varchar(max))
END,
N4
from ctethird as TreCTE
join dbo.Tree_Alarms as Tre
on Tre.NodeID = TreCTE.ParentID
)
select *
from
(
select AH.EventID, ah.alh_t_ModulesID as PluginID, m.mod_FriendlyName as PluginName, --ctethird.N0 as [Site], ctethird.N1 as [Area], ctethird.N2 as [Building], ctethird.N3 as [System],
AH.TechnicalAddress, AH.AlarmAlias, AH.AlarmPath as [OrgAlarmPath], AH.AlarmCounter as AlarmCount, AH.EventDateTime as EventTime, AH.[Priority],
AH.AlarmMessage, AH.EventText, AH.CallListName, AH.AlarmReadDate as EndTime,
[USER] = '['+TC.NodeName+ '] '+ TC.FirstName +' '+ TC.LastName,
[EndCodeId] = CASE
WHEN ESC.EventSubCodeID IS NULL THEN 1
ELSE ESC.EventSubCodeID
END,
[EndCode] = CASE
WHEN ESC.EventSubCodeID IS NULL THEN 'Statistics only alarm'
ELSE ESC.esc_Name
END,
[EndRemark] = CASE
WHEN ESC.EventSubCodeID IS NULL THEN 'Statistics only alarm'
ELSE AH.alh_EventEndedUserRemark
END,
IsForStatistics = CASE
WHEN AH.alh_IsForStatistics IS NULL THEN 1
ELSE AH.alh_IsForStatistics
END,
POD = CASE
WHEN AH.DatabaseID = 0 THEN 'Night'
WHEN AH.DatabaseID = 1 THEN 'Day'
END
from AlarmHistory AH
-- JOIN ctethird on ctethird.N4 = AH.TechnicalAddress and ctethird.NodeLevel = 0
LEFT OUTER JOIN t_EventSubCodes ESC ON AH.alh_t_EventSubCode = ESC.EventSubCodeID
LEFT OUTER JOIN Tree_Contacts TC ON AH.AlarmReadByUserID = TC.NodeID
LEFT OUTER JOIN t_Modules M on M.ModulesID = AH.alh_t_ModulesID
WHERE (((AH.CallListID IS NOT NULL) AND (NOT (AH.alh_t_EventSubCode is NULL or AH.AlarmReadByUserID is NULL))) OR (AH.alh_IsForStatistics = 1)) AND (AH.EventDateTime BETWEEN @FromDate and @ToDate)
) m
INNER JOIN (
Select A.*, B.seconds, B.AlarmSessions From ctefirst A
Inner join ctesecond B On A.EventId = B.EventID
)n
ON n.EventID = m.EventID
ORDER BY m.EventID desc
END
Additional SQL Server features and topics not covered by specific categories
It is not clear what you are asking for help with. Your title says "performance", but in your text it sounds more like you need help to change the query.
The latter is not simple for us to do, since we don't know your tables. The best way to get help with writing a query is post CREATE TABLE statements for your tables together with INSERT statements, enough to illustrate all angles of the problem. It helps if you can reduce the example to only cover the pertinent parts. The recursive part of your you ctethird seems a litle complex, and that complexity is maybe not needed only for the example?
Hello Erland
Yes, you are right, it's quite complex.
The thing is, when the ctethird is "joined" the query time drastically increases, and it comes down to the fact that ctethird recurses the entire tree_alarms tabel from all (10.000) level 4 nodes down to nodelevel 0 - thats 5 records per node for building the "path" belonging to one level 4 node, and when having 10.000 level 4 nodes thats something like 10.000 recursions resulting in 50.000 records (where of which we only need one) and if there are 2000 alarm events in the "main" query that's 2000 * 10.000 recursions of the tree_alarms = kind of crazy performance downgrade :)
So the only thing I need help to is how to add one more where condition to the ctethird (line 31-33)
from Tree_Alarms TA2 where TA2.NodeLevel = 4 and TA2.TechnicalAddress = AH.TechnicalAddress
but AH.fields or m.fields are not accessable in ctethird
how do I get the key up there in the ctethird?
Hello Erland
This is the tables involved and the query I have which performing the best.
The place to add the "where NodeLevel = 0" is not decided yet.
So now we know how the table looks like. That does not mean that we understand them or what they are good for. But don't they have any other indexes besides the primary keys?
Did you try my suggestion of breaking up the query by materialising one or more of the CTEs in to temp tables? If temp tables get big, it makes sense to add indexes on them, at least to identify the keys.
Current best performing query
declare @FromDate datetime = N'11/15/20'
declare @ToDate datetime = N'12/16/20'
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
with
ctefirst as
(
SELECT AH2.EventID FROM AlarmHistory AH2 WHERE ah2.EventID = EventId
),
ctesecond as
(
SELECT ia.eventID, SUM(DATEDIFF(SECOND,ia.eventTime,r.eventTime)) AS seconds, COUNT(*) as AlarmSessions
FROM alarmHistoryLog ia
INNER JOIN alarmHistoryLog r
ON ia.EventId = r.EventId
AND r.EventTypeId = 2
AND r.EventSeq = (SELECT MIN(eventSeq)
FROM alarmHistoryLog WHERE ia.EventId = EventId and eventSeq > ia.EventSeq AND EventTypeId = 2)
WHERE ia.EventTypeId = 0 and ia.EventId > 0
group by ia.EventId
),
ctethird as
(
select NodeID, ParentID, NodeLevel, NodeName,
cast('' as varchar(max)) as N0,
cast('' as varchar(max)) as N1,
cast('' as varchar(max)) as N2,
cast('' as varchar(max)) as N3,
cast(TechnicalAddress as varchar(max)) as N4
from Tree_Alarms TA2 where TA2.TechnicalAddress = TechnicalAddress
union ALL
select
Tre.NodeID, Tre.ParentID, Tre.NodeLevel, Tre.NodeName,
N0 = CASE
WHEN Tre.NodeLevel = 0 AND TreCTE.N0 = '' THEN cast (Tre.NodeName as varchar(max))
ELSE cast (TreCTE.N0 as varchar(max))
END ,
N1 = CASE
WHEN Tre.NodeLevel = 1 AND TreCTE.N1 = '' THEN cast (Tre.NodeName as varchar(max))
ELSE cast (TreCTE.N1 as varchar(max))
END ,
N2 = CASE
WHEN Tre.NodeLevel = 2 AND TreCTE.N2 = '' THEN cast (Tre.NodeName as varchar(max))
ELSE cast (TreCTE.N2 as varchar(max))
END ,
N3 = CASE
WHEN Tre.NodeLevel = 3 AND TreCTE.N3 = '' THEN cast (Tre.NodeName as varchar(max))
ELSE cast (TreCTE.N3 as varchar(max))
END,
N4
from ctethird as TreCTE
join dbo.Tree_Alarms as Tre
on Tre.NodeID = TreCTE.ParentID
)
select *
from
(
select AH.EventID, ah.alh_t_ModulesID as PluginID, m.mod_FriendlyName as PluginName, C.N0 as [Site], C.N1 as [Area], C.N2 as [Building], C.N3 as [System], C.Nodelevel,
AH.TechnicalAddress, AH.AlarmAlias, AH.AlarmPath as [OrgAlarmPath], AH.AlarmCounter as AlarmCount, AH.EventDateTime as EventTime, AH.[Priority],
AH.AlarmMessage, AH.EventText, AH.CallListName, AH.AlarmReadDate as EndTime,
[USER] = '['+TC.NodeName+ '] '+ TC.FirstName +' '+ TC.LastName,
[EndCodeId] = CASE
WHEN ESC.EventSubCodeID IS NULL THEN 1
ELSE ESC.EventSubCodeID
END,
[EndCode] = CASE
WHEN ESC.EventSubCodeID IS NULL THEN 'Statistics only alarm'
ELSE ESC.esc_Name
END,
[EndRemark] = CASE
WHEN ESC.EventSubCodeID IS NULL THEN 'Statistics only alarm'
ELSE AH.alh_EventEndedUserRemark
END,
IsForStatistics = CASE
WHEN AH.alh_IsForStatistics IS NULL THEN 1
ELSE AH.alh_IsForStatistics
END,
POD = CASE
WHEN AH.DatabaseID = 0 THEN 'Night'
WHEN AH.DatabaseID = 1 THEN 'Day'
END
from AlarmHistory AH
JOIN ctethird C on C.N4 = AH.TechnicalAddress
LEFT OUTER JOIN t_EventSubCodes ESC ON AH.alh_t_EventSubCode = ESC.EventSubCodeID
LEFT OUTER JOIN Tree_Contacts TC ON AH.AlarmReadByUserID = TC.NodeID
LEFT OUTER JOIN t_Modules M on M.ModulesID = AH.alh_t_ModulesID
WHERE (((AH.CallListID IS NOT NULL) AND (NOT (AH.alh_t_EventSubCode is NULL or AH.AlarmReadByUserID is NULL))) OR (AH.alh_IsForStatistics = 1)) -- From modified 408 hh 03-02-2020
--WHERE (AH.CallListID IS NOT NULL AND alh_EventEnded = 1) OR (AH.alh_IsForStatistics = 1) -- 4.0.9 01-05-2019 HH to enable statistics for all ended events
-- AND (AH.EventDateTime BETWEEN @FromDate and @ToDate)
--and C.Nodelevel = 0
) m
INNER JOIN (
Select A.*, B.seconds, B.AlarmSessions From ctefirst A
Inner join ctesecond B On A.EventId = B.EventID
) n ON n.EventID = m.EventID --and m.Nodelevel = 0
where m.Nodelevel = 0
--INNER JOIN ctethird ON C
--ORDER BY m.EventID desc
END
/****** Object: Table [dbo].[AlarmsOos] Script Date: 18-12-2020 08:37:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AlarmsOos](
[NodeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[OOSType] [int] NULL,
[OOSExpireDate] [datetime] NULL,
[CreatedDate] [datetime] NULL,
[ChangedDate] [datetime] NULL,
[Comment] [nvarchar](255) NULL,
[UserID] [int] NULL,
[OOSStartDate] [datetime] NULL,
[Enabled] [bit] NULL,
[Options] [int] NULL,
[AutoDelete] [bit] NULL,
[oos_Deleted] [bit] NULL,
CONSTRAINT [PK_AlarmsOos] PRIMARY KEY CLUSTERED
(
[NodeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[t_EventSubCodes] Script Date: 18-12-2020 08:32:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t_EventSubCodes](
[EventSubCodeID] [int] IDENTITY(1,1) NOT NULL,
[esc_Name] [nvarchar](200) NOT NULL,
[esc_Icon] [int] NULL,
[esc_CommentRequired] [bit] NULL,
[esc_Enabled] [bit] NULL,
[esc_Text] [nvarchar](200) NULL,
[esc_SelectOos] [bit] NULL,
CONSTRAINT [PK_t_EventSubCodes] PRIMARY KEY CLUSTERED
(
[EventSubCodeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[AlarmHistory] Script Date: 18-12-2020 08:31:11 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AlarmHistory](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[alh_t_ModulesID] [int] NOT NULL,
[SiteID] [int] NOT NULL,
[DatabaseID] [int] NOT NULL,
[AlarmEventState] [int] NOT NULL,
[WorkflowState] [int] NOT NULL,
[AlarmCounter] [int] NULL,
[TechnicalAddress] [nvarchar](255) NOT NULL,
[AlarmAlias] [nvarchar](255) NULL,
[AlarmPath] [nvarchar](255) NULL,
[EventDateTime] [datetime] NOT NULL,
[Priority] [smallint] NOT NULL,
[AlarmMessage] [nvarchar](255) NOT NULL,
[EventText] [nvarchar](255) NULL,
[QueueDateTime] [datetime] NULL,
[UpdateDateTime] [datetime] NULL,
[CallListID] [int] NULL,
[CallListName] [nvarchar](255) NULL,
[OOSID] [int] NULL,
[AlarmReadDate] [datetime] NULL,
[AlarmReadByUserID] [int] NULL,
[HwId] [nvarchar](20) NULL,
[ZoneId] [nvarchar](6) NULL,
[Latitude] [nvarchar](20) NULL,
[Longitude] [nvarchar](20) NULL,
[ZoneType] [nvarchar](12) NULL,
[HeartbeatAlarm] [int] NULL,
[OOSType] [int] NULL,
[alh_ReservedTime] [datetime] NULL,
[alh_ReservedUserID] [int] NULL,
[alh_publish] [bit] NULL,
[alh_SubscriptionOptions] [int] NULL,
[alh_SourceID] [nvarchar](50) NULL,
[alh_Latitude] [nvarchar](20) NULL,
[alh_Longitude] [nvarchar](20) NULL,
[alh_EventIgnored] [bit] NULL,
[alh_EventEnded] [bit] NULL,
[alh_EventHidden] [bit] NULL,
[alh_EventEndedUserRemark] [nvarchar](4000) NULL,
[alh_t_EventSubCode] [int] NULL,
[alh_Street] [nvarchar](250) NULL,
[alh_City] [nvarchar](50) NULL,
[alh_Zipcode] [nvarchar](50) NULL,
[alh_HouseNo] [nvarchar](50) NULL,
[alh_State] [nvarchar](50) NULL,
[alh_Country] [nvarchar](50) NULL,
[alh_IsForStatistics] [bit] NULL,
[alh_EventEndedExternal] [bit] NULL,
[alh_Acknowledged] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AlarmHistory] WITH CHECK ADD CONSTRAINT [fk_AlarmHistory_t_EventSubCode] FOREIGN KEY([alh_t_EventSubCode])
REFERENCES [dbo].[t_EventSubCodes] ([EventSubCodeID])
GO
ALTER TABLE [dbo].[AlarmHistory] CHECK CONSTRAINT [fk_AlarmHistory_t_EventSubCode]
GO
/****** Object: Table [dbo].[AlarmHistoryLog] Script Date: 18-12-2020 08:31:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AlarmHistoryLog](
[EventId] [int] NOT NULL,
[EventSeq] [int] IDENTITY(1,1) NOT NULL,
[EventTypeId] [int] NOT NULL,
[EventTime] [datetime] NOT NULL,
[EventDescription] [nvarchar](250) NOT NULL,
[Comment] [nvarchar](4000) NULL,
[UserID] [int] NULL,
[ReceiverID] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Tree_Alarms] Script Date: 18-12-2020 08:31:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tree_Alarms](
[NodeID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL,
[NodeLevel] [int] NOT NULL,
[NodeName] [nvarchar](255) NOT NULL,
[TechnicalAddress] [nvarchar](255) NULL,
[Remark] [nvarchar](255) NULL,
[Options] [int] NULL,
[CreatedDate] [datetime] NULL,
[ChangedDate] [datetime] NULL,
[LastPrinted] [datetime] NULL,
[PrintCounter] [int] NULL,
[CallListID] [int] NULL,
[OOSID] [int] NULL,
[HwId] [nvarchar](20) NULL,
[ZoneId] [nvarchar](6) NULL,
[Latitude] [nvarchar](20) NULL,
[Longitude] [nvarchar](20) NULL,
[HeartbeatAlarm] [bit] NULL,
[MsgOptions] [int] NULL,
[ZoneType] [nvarchar](12) NULL,
[Street] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[Zipcode] [nvarchar](50) NULL,
[HouseNo] [nvarchar](50) NULL,
[State] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[tal_Deleted] [bit] NULL,
[tal_Publish] [bit] NULL,
[tal_SubscriptionOptions] [int] NULL,
[tal_IsForStatistics] [bit] NULL,
CONSTRAINT [PK_Tree_Alarms] PRIMARY KEY CLUSTERED
(
[NodeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tree_Alarms] WITH CHECK ADD CONSTRAINT [fk_TreeAlarms_AlarmsOos] FOREIGN KEY([OOSID])
REFERENCES [dbo].[AlarmsOos] ([NodeID])
GO
ALTER TABLE [dbo].[Tree_Alarms] CHECK CONSTRAINT [fk_TreeAlarms_AlarmsOos]
GO
/****** Object: Table [dbo].[Tree_Contacts] Script Date: 18-12-2020 08:34:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tree_Contacts](
[NodeID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL,
[NodeName] [nvarchar](50) NOT NULL,
[NodeLevel] [int] NOT NULL,
[Remark] [nvarchar](255) NULL,
[Options] [int] NULL,
[CreatedDate] [datetime] NULL,
[ChangedDate] [datetime] NULL,
[LastPrinted] [datetime] NULL,
[PrintCount] [int] NULL,
[ContactType] [int] NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[CompanyName] [nvarchar](50) NULL,
[Sysadm] [bit] NULL,
[Enabled] [bit] NULL,
[Phone1] [nvarchar](50) NULL,
[Phone2] [nvarchar](50) NULL,
[Phone3] [nvarchar](50) NULL,
[AllowSMSday] [bit] NULL,
[AllowSMSNight] [bit] NULL,
[Email] [nvarchar](75) NULL,
[Password] [nvarchar](4000) NULL,
[Userlevel] [int] NULL,
[Lastactivity] [datetime] NULL,
[LockedDate] [datetime] NULL,
[LogonAttempts] [int] NULL,
[SubstID] [int] NULL,
[SubstReason] [int] NULL,
[SubstStart] [datetime] NULL,
[SubstExpires] [datetime] NULL,
[CalllistBySMS] [bit] NULL,
[CalllistByEmailDay] [bit] NULL,
[CalllistByEmailNight] [bit] NULL,
[Phone4] [nvarchar](50) NULL,
[WebTheme] [nvarchar](50) NULL,
CONSTRAINT [pk_Tree_Contacts] PRIMARY KEY CLUSTERED
(
[NodeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[t_Modules] Script Date: 18-12-2020 08:34:49 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t_Modules](
[ModulesID] [int] IDENTITY(1,1) NOT NULL,
[mod_t_SitesID] [int] NOT NULL,
[mod_t_ModulesTypesID] [int] NOT NULL,
[mod_GUID] [char](36) NOT NULL,
[mod_FriendlyName] [varchar](50) NULL,
[mod_Description] [varchar](100) NULL,
[mod_ServiceName] [varchar](255) NULL,
[mod_HostName] [varchar](255) NULL,
[mod_MachineID] [varchar](4000) NULL,
[mod_ReleaseCode] [varchar](4000) NULL,
[mod_version] [varchar](50) NULL,
[mod_HeartBeat] [datetime] NULL,
[mod_HeartbeatTimeout] [int] NOT NULL,
[mod_t_MissingHeartbeatCausesID] [int] NULL,
[mod_HeartbeatAlarm] [bit] NULL,
[mod_LicenseOK] [bit] NULL,
CONSTRAINT [PK__t_Module__E6C746AD0B1E4F76] PRIMARY KEY CLUSTERED
(
[ModulesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Since I don't know the tables, and I don't feel inclined to dig into this beast of a query, I will let it suffice with a standard "trick": materialise one or more of the CTEs to temp tables. Sometimes this is a sure performance killer, because the optimizer may be able to shortcut the CTE and never compute it as such. But it can also be a winner, because it can help the optimizer to find a better query plan for the rest of the query.
It is not impossible that the situation can be alleviated by adding suitable indexes, but in that case we would need to know the CREATE TABLE and CREATE INDEX statements, as well as the actual query plans for the various variations you have tried.
I found that this actually improves performance a lot now runs in 31 seconds (268.000 rows)
-> from Tree_Alarms TA2 where TA2.TechnicalAddress = TechnicalAddress
That means I removed "where nodelevel = 4" from that where statement.
However I still only need the result row where nodelevel = 0 (where the columns are populated with path names)
But when I add
) n ON n.EventID = m.EventID **and m.Nodelevel = 0** = time 2.30 (53.000 rows)
OR just
where m.Nodelevel = 0 = time 2.30 (53.000 rows)
Performance degrades a lot but at least not as severe as much as to begin with, well beyond 10mins if not timing out
Wonder what could cause that extra 2min when using NodeLevel = 0 on the result set.
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
with
ctefirst as
(
SELECT AH2.EventID FROM AlarmHistory AH2 WHERE ah2.EventID = EventId
),
ctesecond as
(
SELECT ia.eventID, SUM(DATEDIFF(SECOND,ia.eventTime,r.eventTime)) AS seconds, COUNT(*) as AlarmSessions
FROM alarmHistoryLog ia
INNER JOIN alarmHistoryLog r
ON ia.EventId = r.EventId
AND r.EventTypeId = 2
AND r.EventSeq = (SELECT MIN(eventSeq)
FROM alarmHistoryLog WHERE ia.EventId = EventId and eventSeq > ia.EventSeq AND EventTypeId = 2)
WHERE ia.EventTypeId = 0 and ia.EventId > 0
group by ia.EventId
),
ctethird as
(
select NodeID, ParentID, NodeLevel, NodeName,
cast('' as varchar(max)) as N0,
cast('' as varchar(max)) as N1,
cast('' as varchar(max)) as N2,
cast('' as varchar(max)) as N3,
cast(TechnicalAddress as varchar(max)) as N4
from Tree_Alarms TA2 where TA2.TechnicalAddress = TechnicalAddress
union ALL
select
Tre.NodeID, Tre.ParentID, Tre.NodeLevel, Tre.NodeName,
N0 = CASE
WHEN Tre.NodeLevel = 0 AND TreCTE.N0 = '' THEN cast (Tre.NodeName as varchar(max))
ELSE cast (TreCTE.N0 as varchar(max))
END ,
N1 = CASE
WHEN Tre.NodeLevel = 1 AND TreCTE.N1 = '' THEN cast (Tre.NodeName as varchar(max))
ELSE cast (TreCTE.N1 as varchar(max))
END ,
N2 = CASE
WHEN Tre.NodeLevel = 2 AND TreCTE.N2 = '' THEN cast (Tre.NodeName as varchar(max))
ELSE cast (TreCTE.N2 as varchar(max))
END ,
N3 = CASE
WHEN Tre.NodeLevel = 3 AND TreCTE.N3 = '' THEN cast (Tre.NodeName as varchar(max))
ELSE cast (TreCTE.N3 as varchar(max))
END,
N4
from ctethird as TreCTE
join dbo.Tree_Alarms as Tre
on Tre.NodeID = TreCTE.ParentID
)
select *
from
(
select AH.EventID, ah.alh_t_ModulesID as PluginID, m.mod_FriendlyName as PluginName, C.N0 as [Site], C.N1 as [Area], C.N2 as [Building], C.N3 as [System], C.Nodelevel,
AH.TechnicalAddress, AH.AlarmAlias, AH.AlarmPath as [OrgAlarmPath], AH.AlarmCounter as AlarmCount, AH.EventDateTime as EventTime, AH.[Priority],
AH.AlarmMessage, AH.EventText, AH.CallListName, AH.AlarmReadDate as EndTime,
[USER] = '['+TC.NodeName+ '] '+ TC.FirstName +' '+ TC.LastName,
[EndCodeId] = CASE
WHEN ESC.EventSubCodeID IS NULL THEN 1
ELSE ESC.EventSubCodeID
END,
[EndCode] = CASE
WHEN ESC.EventSubCodeID IS NULL THEN 'Statistics only alarm'
ELSE ESC.esc_Name
END,
[EndRemark] = CASE
WHEN ESC.EventSubCodeID IS NULL THEN 'Statistics only alarm'
ELSE AH.alh_EventEndedUserRemark
END,
IsForStatistics = CASE
WHEN AH.alh_IsForStatistics IS NULL THEN 1
ELSE AH.alh_IsForStatistics
END,
POD = CASE
WHEN AH.DatabaseID = 0 THEN 'Night'
WHEN AH.DatabaseID = 1 THEN 'Day'
END
from AlarmHistory AH
JOIN ctethird C on C.N4 = AH.TechnicalAddress
LEFT OUTER JOIN t_EventSubCodes ESC ON AH.alh_t_EventSubCode = ESC.EventSubCodeID
LEFT OUTER JOIN Tree_Contacts TC ON AH.AlarmReadByUserID = TC.NodeID
LEFT OUTER JOIN t_Modules M on M.ModulesID = AH.alh_t_ModulesID
WHERE (((AH.CallListID IS NOT NULL) AND (NOT (AH.alh_t_EventSubCode is NULL or AH.AlarmReadByUserID is NULL))) OR (AH.alh_IsForStatistics = 1))
) m
INNER JOIN (
Select A.*, B.seconds, B.AlarmSessions From ctefirst A
Inner join ctesecond B On A.EventId = B.EventID
) n ON n.EventID = m.EventID and m.Nodelevel = 0
--where m.Nodelevel = 0
ORDER BY m.EventID desc
END