November 19, 2014 at 10:00 am
I am running a view which brings bits of information from several tables together. I would like to see if there is some way to improve the performance of the view to reduce lag time in execution. The main table which the data comes from is called UnitLog. There are almost 2M records. The purpose of this view is to find the most recent record, without selecting postdated records, for each distinct UnitNumber in the table. The UnitNumber field relates to each other joined table to pull the relevant information from that table for the purpose of this query. I am learning my way around SQL Server, and have probably made this more complicated than it needs to be. Thanks in advance for any help!
Code:
SELECT TOP (100) PERCENT dbo.UnitLog.UnitNumber, dbo.UnitLog.fldDateTime, CASE WHEN derivedtbl_2.SectionNumber IS NULL
THEN CASE WHEN Departments.SectionNumber IS NULL
THEN '9999' ELSE Departments.SectionNumber END ELSE derivedtbl_2.SectionNumber END AS SectionNumber, dbo.AlphaUnits.UnitStatus AS AlphaStatus,
CASE WHEN dbo.ActiveSafetyTimers.TimerExpr IS NULL THEN NULL ELSE TimerExpr END AS TimerExpr, dbo.UnitLog.RCN, dbo.UnitLog.UnitStatus,
CASE WHEN CCNo = '' THEN NULL ELSE CCNo END AS CCNo, CASE WHEN Unit2 IS NULL THEN NULL ELSE '*' END AS IsJoined,
dbo.UnitLog.CommCenter AS CurrentCommCenter, CASE WHEN derivedtbl_2.CommCenter IS NULL
THEN departments.CommCenter ELSE derivedtbl_2.CommCenter END AS PrimaryCommCenter
FROM (SELECT [#temp_UnitClientStatus1].UnitNumber, [#temp_UnitClientStatus2].RCN
FROM dbo.UnitLog AS [#temp_UnitClientStatus2] RIGHT OUTER JOIN
(SELECT TOP (100) PERCENT UnitNumber, MAX(fldDateTime) AS MaxOfDateTime
FROM dbo.UnitLog AS UnitLog_1
WHERE (UnitStatus <> 'LOG') AND (fldDateTime <= DATEADD(ss, 15, GETDATE()))
GROUP BY UnitNumber
ORDER BY UnitNumber) AS [#temp_UnitClientStatus1] ON [#temp_UnitClientStatus2].UnitNumber = [#temp_UnitClientStatus1].UnitNumber AND
[#temp_UnitClientStatus2].fldDateTime = [#temp_UnitClientStatus1].MaxOfDateTime
GROUP BY [#temp_UnitClientStatus1].UnitNumber, [#temp_UnitClientStatus2].RCN) AS derivedtbl_1 INNER JOIN
dbo.UnitLog ON derivedtbl_1.RCN = dbo.UnitLog.RCN LEFT OUTER JOIN
dbo.Departments ON derivedtbl_1.UnitNumber = dbo.Departments.CallSign LEFT OUTER JOIN
(SELECT TOP (100) PERCENT [#temp_qryPersonnel].DepartmentID, [#temp_qryPersonnel].UnitID, Departments_1.SectionNumber,
Departments_1.CommCenter
FROM dbo.DepartmentPersonnel AS [#temp_qryPersonnel] LEFT OUTER JOIN
dbo.Departments AS Departments_1 ON [#temp_qryPersonnel].DepartmentID = Departments_1.RCN
WHERE ([#temp_qryPersonnel].Active = 1) AND ([#temp_qryPersonnel].UnitID IS NOT NULL)
ORDER BY [#temp_qryPersonnel].UnitID) AS derivedtbl_2 ON derivedtbl_1.UnitNumber = derivedtbl_2.UnitID LEFT OUTER JOIN
dbo.AlphaUnits ON derivedtbl_1.UnitNumber = dbo.AlphaUnits.UnitNumber LEFT OUTER JOIN
dbo.JoinedUnits ON derivedtbl_1.UnitNumber = dbo.JoinedUnits.Unit1 LEFT OUTER JOIN
dbo.ActiveSafetyTimers ON derivedtbl_1.UnitNumber = dbo.ActiveSafetyTimers.UnitNumber
WHERE (LEFT(dbo.UnitLog.UnitStatus, 5) <> '10-42')
ORDER BY dbo.UnitLog.UnitNumber
November 19, 2014 at 10:16 am
Hi and welcome to the forums. Let's start with some formatting so we can read this. If you use the IFCode shortcuts on the left side when posting you can wrap code in the code tags to maintain formatting.
SELECT TOP (100) PERCENT dbo.UnitLog.UnitNumber
,dbo.UnitLog.fldDateTime
,CASE
WHEN derivedtbl_2.SectionNumber IS NULL
THEN CASE
WHEN Departments.SectionNumber IS NULL
THEN '9999'
ELSE Departments.SectionNumber
END
ELSE derivedtbl_2.SectionNumber
END AS SectionNumber
,dbo.AlphaUnits.UnitStatus AS AlphaStatus
,CASE
WHEN dbo.ActiveSafetyTimers.TimerExpr IS NULL
THEN NULL
ELSE TimerExpr
END AS TimerExpr
,dbo.UnitLog.RCN
,dbo.UnitLog.UnitStatus
,CASE
WHEN CCNo = ''
THEN NULL
ELSE CCNo
END AS CCNo
,CASE
WHEN Unit2 IS NULL
THEN NULL
ELSE '*'
END AS IsJoined
,dbo.UnitLog.CommCenter AS CurrentCommCenter
,CASE
WHEN derivedtbl_2.CommCenter IS NULL
THEN departments.CommCenter
ELSE derivedtbl_2.CommCenter
END AS PrimaryCommCenter
FROM (
SELECT [#temp_UnitClientStatus1].UnitNumber
,[#temp_UnitClientStatus2].RCN
FROM dbo.UnitLog AS [#temp_UnitClientStatus2]
RIGHT JOIN (
SELECT TOP (100) PERCENT UnitNumber
,MAX(fldDateTime) AS MaxOfDateTime
FROM dbo.UnitLog AS UnitLog_1
WHERE (UnitStatus <> 'LOG')
AND (fldDateTime <= DATEADD(ss, 15, GETDATE()))
GROUP BY UnitNumber
ORDER BY UnitNumber
) AS [#temp_UnitClientStatus1] ON [#temp_UnitClientStatus2].UnitNumber = [#temp_UnitClientStatus1].UnitNumber
AND [#temp_UnitClientStatus2].fldDateTime = [#temp_UnitClientStatus1].MaxOfDateTime
GROUP BY [#temp_UnitClientStatus1].UnitNumber
,[#temp_UnitClientStatus2].RCN
) AS derivedtbl_1
INNER JOIN dbo.UnitLog ON derivedtbl_1.RCN = dbo.UnitLog.RCN
LEFT JOIN dbo.Departments ON derivedtbl_1.UnitNumber = dbo.Departments.CallSign
LEFT JOIN (
SELECT TOP (100) PERCENT [#temp_qryPersonnel].DepartmentID
,[#temp_qryPersonnel].UnitID
,Departments_1.SectionNumber
,Departments_1.CommCenter
FROM dbo.DepartmentPersonnel AS [#temp_qryPersonnel]
LEFT JOIN dbo.Departments AS Departments_1 ON [#temp_qryPersonnel].DepartmentID = Departments_1.RCN
WHERE ([#temp_qryPersonnel].Active = 1)
AND ([#temp_qryPersonnel].UnitID IS NOT NULL)
ORDER BY [#temp_qryPersonnel].UnitID
) AS derivedtbl_2 ON derivedtbl_1.UnitNumber = derivedtbl_2.UnitID
LEFT JOIN dbo.AlphaUnits ON derivedtbl_1.UnitNumber = dbo.AlphaUnits.UnitNumber
LEFT JOIN dbo.JoinedUnits ON derivedtbl_1.UnitNumber = dbo.JoinedUnits.Unit1
LEFT JOIN dbo.ActiveSafetyTimers ON derivedtbl_1.UnitNumber = dbo.ActiveSafetyTimers.UnitNumber
WHERE (LEFT(dbo.UnitLog.UnitStatus, 5) <> '10-42')
ORDER BY dbo.UnitLog.UnitNumber
There are a number of low hanging fruits in here for performance. You have a lot of nonSARGable predicates.
Things like UnitStatus <> 'LOG' and even worse LEFT(dbo.UnitLog.UnitStatus, 5) <> '10-42' means that no indexing is going to help here. The engine is going to have to look at every single row and calculate the result.
Also, there is no need to use top 100 percent and there is certainly no need to use an order by a subquery (unless the top is less than 100 percent).
It seems you are hitting the same tables over and over here too which will cause things to slow down. I would HIGHLY recommend you not use spaces, reserved words or special characters for object names. And putting # at the beginning of a base table will drive you nuts because it looks like a temp table but it isn't. And even worse is using that kind of notation as an alias.
We can help but you would need to post table definitions and an explanation of what this query is trying to do. Take a look at the link in my signature about posting questions for more details.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2014 at 12:21 pm
Thanks for the reply, and I apologize, I did not understand the use of the IfCode Shortcuts before. I will try to provide the information as best as I can. Below are the statements to Drop/Create the tables.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UnitLog]') AND type in (N'U'))
DROP TABLE [dbo].[UnitLog]
GO
CREATE TABLE [dbo].[UnitLog](
[RCN] [int] IDENTITY(1,1) NOT NULL,
[UnitNumber] [varchar](8) NULL,
[UnitRank] [varchar](6) NULL,
[UnitName] [varchar](30) NULL,
[UnitStatus] [varchar](6) NULL,
[fldDateTime] [datetime2](7) NULL,
[Details] [varchar](255) NULL,
[CCNo] [varchar](16) NULL,
[PCO] [varchar](25) NULL,
[CommCenter] [char](2) NULL,
[flgAudit] [bit] NOT NULL,
[Mileage] [decimal](7, 1) NULL,
[CreatedDateTime] [datetime2](7) NULL,
[CreatedBy] [varchar](25) NULL,
[LastModifiedDateTime] [datetime2](7) NULL,
[LastModifiedBy] [varchar](25) NULL,
CONSTRAINT [PK__UnitLog__CAFF900C74AE54BC] PRIMARY KEY CLUSTERED
(
[RCN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Departments]') AND type in (N'U'))
DROP TABLE [dbo].[Departments]
GO
CREATE TABLE [dbo].[Departments](
[RCN] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [varchar](50) NOT NULL,
[DetachmentName] [varchar](50) NULL,
[StreetNumber] [varchar](10) NULL,
[PreDirectional] [varchar](3) NULL,
[StreetName] [varchar](30) NULL,
[StreetSuffix] [varchar](4) NULL,
[PostDirectional] [varchar](3) NULL,
[SecondaryUnitIndicator] [varchar](4) NULL,
[SecondaryNumber] [varchar](10) NULL,
[POBox] [varchar](10) NULL,
[City] [varchar](30) NULL,
[County] [char](5) NULL,
[CityState] [varchar](2) NULL,
[Zip] [varchar](5) NULL,
[Zip4] [varchar](4) NULL,
[MailTo] [bit] NULL,
[ORI] [varchar](9) NULL,
[TroopNumber] [char](1) NULL,
[TroopCommand] [bit] NULL,
[DistrictNumber] [char](1) NULL,
[DistrictCommand] [bit] NULL,
[SectionNumber] [char](4) NULL,
[CADCode] [varchar](6) NULL,
[Notes] [varchar](255) NULL,
[Active] [bit] NULL,
[LastUpdated] [datetime2](7) NULL,
[LastUpdatedBy] [varchar](25) NULL,
[CommCenter] [char](2) NULL,
[DetachmentIP] [tinyint] NULL,
[IsPCC] [bit] NOT NULL,
[CallSign] [varchar](8) NULL,
[LogOff] [bit] NOT NULL,
[OnNetCallingNumber] [smallint] NULL,
CONSTRAINT [PK__Departme__CAFF900C47A6A41B] PRIMARY KEY CLUSTERED
(
[RCN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DepartmentPersonnel]') AND type in (N'U'))
DROP TABLE [dbo].[DepartmentPersonnel]
GO
CREATE TABLE [dbo].[DepartmentPersonnel](
[DepartmentID] [int] NULL,
[PersonnelID] [int] NULL,
[PermanentID] [varchar](6) NULL,
[UnitID] [varchar](6) NULL,
[UnitRank] [varchar](6) NULL,
[DODs] [varchar](7) NULL,
[EMailAddress] [varchar](40) NULL,
[NewUnitID] [varchar](6) NULL,
[Notes] [varchar](255) NULL,
[Active] [bit] NULL,
[RadioIDPortable] [int] NULL,
[RadioIDMobile] [int] NULL,
[LastUpdated] [datetime2](7) NULL,
[LastUpdatedBy] [varchar](255) NULL,
[OldUnitID] [varchar](6) NULL,
[RCN] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_DepartmentPersonnel] PRIMARY KEY CLUSTERED
(
[RCN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DepartmentPersonnel] ADD CONSTRAINT [DF__Departmen__Activ__59C55456] DEFAULT ((1)) FOR [Active]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ActiveSafetyTimers]') AND type in (N'U'))
DROP TABLE [dbo].[ActiveSafetyTimers]
GO
CREATE TABLE [dbo].[ActiveSafetyTimers](
[UnitNumber] [varchar](8) NOT NULL,
[TimerExpr] [datetime2](7) NULL,
[TimerValue] [tinyint] NULL,
[CurrentCommCenter] [char](2) NULL,
CONSTRAINT [PK__ActiveSa__D3CC338A39CD8610] PRIMARY KEY CLUSTERED
(
[UnitNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AlphaUnits]') AND type in (N'U'))
DROP TABLE [dbo].[AlphaUnits]
GO
CREATE TABLE [dbo].[AlphaUnits](
[UnitNumber] [varchar](8) NOT NULL,
[UnitStatus] [char](1) NOT NULL,
CONSTRAINT [PK__AlphaUni__44F5EC95286302EC] PRIMARY KEY CLUSTERED
(
[UnitNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JoinedUnits]') AND type in (N'U'))
DROP TABLE [dbo].[JoinedUnits]
GO
CREATE TABLE [dbo].[JoinedUnits](
[Unit1] [varchar](8) NOT NULL,
[Unit2] [varchar](8) NOT NULL,
CONSTRAINT [PK_JoinedUnits] PRIMARY KEY CLUSTERED
(
[Unit1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Table explanations:
RCN (Record Control Number) fields are unique identifiers on a per table basis.
UnitLog - Stores individual activity records for all personnel. Primary information fields for this view are RCN, UnitNumber, UnitStatus, and fldDateTime.
Departments - Stores information about different departments, such as location. Primary information fields for this view are Section Number, and CommCenter.
DepartmentPersonnel - Join table to join records between a Personnel table and the Departments table. Primary information fields for this view are: DepartmentID (references Departments.RCN) and UnitID (referenced by UnitLog.UnitNumber).
ActiveSafetyTimers - Stores expiration date/time of timers. Primary information field for this view is: UnitNumber (references UnitLog.UnitNumber). All fields are returned.
AlphaUnits - Stores information in reference to alpha status. UnitNumber references UnitLog.UnitNumber. UnitStatus = '*'.
JoinedUnits - Stores information about units tagged together. Unit1 references UnitLog.UnitNumber. If a UnitNumber is present in JoinedUnits, the view returns an asterisk '*'.
Desired View result:
The intent of the view is to pull the requested information from the above tables to output data in the following columns:
UnitNumber (varchar(8), null) ----- From UnitLog
fldDateTime (datetime2(7), null) ----- From UnitLog
SectionNumber (varchar(4), null) ----- From Departments
AlphaStatus (char(1), null) ----- From AlphaUnits.UnitStatus
TimerExpr (datetime2(7), null) ----- From ActiveSafetyTimers
RCN (int, not null) ----- From UnitLog
UnitStatus (varchar(6), null) ----- From UnitLog
CCNo (varchar(16), null) ----- From UnitLog
IsJoined (varchar(1), null) ----- Returns Asterisk '*' if UnitNumber is present in JoinedUnits.Unit1
CurrentCommCenter (char(2), null) ----- From UnitLog
PrimaryCommCenter (char(2), null) ----- From Departments.CommCenter
View action:
From UnitLog pull the most recent activity for each UnitNumber, excluding: future activity or any activity with a status of 'LOG'. If more than one activity is present for a UnitNumber at the same datetime, the highest RCN shall prevail. Using UnitNumber, join each additional table, and return requested information. If the UnitStatus of the most recent activity is '10-42' or '10-42A', the record shall not be returned and the UnitNumber omitted from the final result (not return a previous activity record). Any single unit number should only appear once on the final list, but there may be hundreds of units returned.
I hope I have included enough information to get started, but let me know what else is needed.
November 19, 2014 at 12:49 pm
No need to apologize at all. I let you know they are there to make things easier for you. 🙂 Unlike some other online places we try to be nice around here.
Excellent job with the table structures. If you could post enough sample rows (as insert statements) for each table to cover your scenario that would be awesome.
And then for output it would really helpful to know the actual values (based on your sample data) you want out of the query. Although I think your description may be enough to get us there.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2014 at 1:55 pm
Here are the insert statements:
INSERT INTO dbo.UnitLog (RCN, UnitNumber, UnitStatus, fldDateTime, CCNo, CommCenter) VALUES
('1','373','10-41','2014-11-07 15:59:00.0000000','2014-000057','20'),
('2','373','10-17','2014-11-07 16:59:00.0000000','2014-000057','20'),
('3','373','10-23','2014-11-07 17:09:00.0000000','2014-000057','20'),
('4','373','LOG','2014-11-07 17:19:00.0000000','2014-000057','20'),
('5','374','10-8','2014-11-07 15:59:00.0000000','2014-000059','20'),
('6','374','10-17','2014-11-07 19:49:00.0000000','2014-000060','20'),
('7','374','10-23','2014-11-07 19:59:00.0000000','2014-000060','20'),
('8','374','10-17','2014-11-07 20:04:00.0000000','2014-000061','20'),
('9','374','10-7','2014-11-07 20:19:00.0000000','2014-000061','20'),
('10','371','10-8','2014-11-07 11:29:00.0000000','2014-000055','20'),
('11','371','10-42','2014-11-07 11:59:00.0000000','','20'),
('12','372','10-23','2014-11-07 12:59:00.0000000','2014-000056','20'),
('13','372','LOG','2014-11-07 13:59:00.0000000','2014-000056','20'),
('14','372','10-8','2014-11-07 15:59:00.0000000''2014-000056',,'20'),
('15','372','10-7','2014-11-07 15:59:00.0000000','','20')
GO
INSERT INTO dbo.Departments (RCN, DepartmentName, SectionNumber, CommCenter) VALUES
('1','HEADQUARTERS','0520', '20')
GO
INSERT INTO dbo.DepartmentPersonnel (DepartmentID, UnitID) VALUES
('1','371'),
('1','372'),
('1','373'),
('1','374')
GO
INSERT INTO dbo.ActiveSafetyTimers (UnitNumber, TimerExpr, TimerValue, CurrentCommCenter) VALUES
('373', '2014-11-07 17:29:00.0000000','20','20'),
('374', '2014-11-07 20:49:00.0000000','30','20')
GO
INSERT INTO dbo.AlphaUnits (UnitNumber, UnitStatus) VALUES
('372','*')
GO
INSERT INTO dbo.JoinedUnits (Unit1, Unit2) VALUES
('372','375')
GO
The result should be:
UnitNumber | fldDateTime | SectionNumber | AlphaStatus | TimerExpr | RCN | UnitStatus | CCNo | IsJoined | CurrentCommCenter | PrimaryCommCenter
372 | 2014-11-07 15:59:00.0000000 | 0520 | * | | 15 | 10-7 | | * | 20 | 20
373 | 2014-11-07 17:09:00.0000000 | 0520 | | 2014-11-07 17:29:00.0000000 | 3 | 10-23 | 2014-000057 | | 20 | 20
374 | 2014-11-07 20:19:00.0000000 | 0520 | | 2014-11-07 20:49:00.0000000 | 9 | 10-7 | 2014-000061 | | 20 | 20
Notes:
371 is omitted as its most recent activity is 10-42.
372 had two activities at the same time, the highest RCN was selected.
373's most recent activity is LOG, so the previous activity was selected.
November 19, 2014 at 1:56 pm
Probably the big thing is to reduce all the gyrations around UnitLog. Please try the query below. If you tend to restrict UnitLog rows using fldDateTime range, particularly more restrictively rather than just "<= 15 seconds ago", I suggest you strongly consider clustering UnitLog on ( fldDateTime, RCN ) rather than just RCN/identity.
Likewise, but with much lesser overall impact, dbo.DepartmentPersonnel should very likely be clustered by ( DepartmentID, PersonnelID ) rather than identity/RCN.
SELECT TOP (100) PERCENT
ul.UnitNumber
,ul.fldDateTime
,CASE
WHEN derivedtbl_2.SectionNumber IS NULL
THEN CASE
WHEN Departments.SectionNumber IS NULL
THEN '9999'
ELSE Departments.SectionNumber
END
ELSE derivedtbl_2.SectionNumber
END AS SectionNumber
,dbo.AlphaUnits.UnitStatus AS AlphaStatus
,CASE
WHEN dbo.ActiveSafetyTimers.TimerExpr IS NULL
THEN NULL
ELSE TimerExpr
END AS TimerExpr
,ul.RCN
,ul.UnitStatus
,CASE
WHEN CCNo = ''
THEN NULL
ELSE CCNo
END AS CCNo
,CASE
WHEN Unit2 IS NULL
THEN NULL
ELSE '*'
END AS IsJoined
,ul.CommCenter AS CurrentCommCenter
,CASE
WHEN derivedtbl_2.CommCenter IS NULL
THEN departments.CommCenter
ELSE derivedtbl_2.CommCenter
END AS PrimaryCommCenter
FROM (
SELECT
ul2.*,
ROW_NUMBER() OVER(PARTITION BY ul2.UnitNumber ORDER BY fldDateTime DESC) AS row_num
FROM dbo.UnitLog ul2
WHERE
(ul2.UnitStatus <> 'LOG') AND
(ul2.fldDateTime <= DATEADD(SECOND, 15, GETDATE()))
) AS ul
LEFT JOIN dbo.Departments ON ul.UnitNumber = dbo.Departments.CallSign
LEFT JOIN (
SELECT TOP (100) PERCENT [#temp_qryPersonnel].DepartmentID
,[#temp_qryPersonnel].UnitID
,Departments_1.SectionNumber
,Departments_1.CommCenter
FROM dbo.DepartmentPersonnel AS [#temp_qryPersonnel]
LEFT JOIN dbo.Departments AS Departments_1 ON [#temp_qryPersonnel].DepartmentID = Departments_1.RCN
WHERE ([#temp_qryPersonnel].Active = 1)
AND ([#temp_qryPersonnel].UnitID IS NOT NULL)
--ORDER BY [#temp_qryPersonnel].UnitID
) AS derivedtbl_2 ON ul.UnitNumber = derivedtbl_2.UnitID
LEFT JOIN dbo.AlphaUnits ON ul.UnitNumber = dbo.AlphaUnits.UnitNumber
LEFT JOIN dbo.JoinedUnits ON ul.UnitNumber = dbo.JoinedUnits.Unit1
LEFT JOIN dbo.ActiveSafetyTimers ON ul.UnitNumber = dbo.ActiveSafetyTimers.UnitNumber
WHERE
ul.row_num = 1 AND
ul.UnitStatus NOT LIKE '10-42%'
ORDER BY ul.UnitNumber
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 19, 2014 at 2:03 pm
Didn't see the test data when initially coding. I had to add RCN to the ORDER BY in case there are duplicate times ... although it seems odd/counterintuitive that you want the highest time but, if times are tied, the lowest RCN?!
FROM (
SELECT
ul2.*,
ROW_NUMBER() OVER(PARTITION BY ul2.UnitNumber ORDER BY fldDateTime DESC, RCN ) AS row_num
FROM dbo.UnitLog ul2
WHERE
(ul2.UnitStatus <> 'LOG') AND
(ul2.fldDateTime <= DATEADD(SECOND, 15, GETDATE()))
) AS ul
Edit: Corrected typo.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 19, 2014 at 2:18 pm
ScottPletcher (11/19/2014)
Didn't see the test data when initially coding. I had to add RCN to the ORDER BY in case there are duplicate times ... although it seems odd/counterintuitive that you want the highest time but, if times are tied, the lowest RCN?!
It should select the highest RCN, indicating the most recent record added. RCN is an Identity column which counts up.
November 19, 2014 at 2:22 pm
swhetsell (11/19/2014)
ScottPletcher (11/19/2014)
Didn't see the test data when initially coding. I had to add RCN to the ORDER BY in case there are duplicate times ... although it seems odd/counterintuitive that you want the highest time but, if times are tied, the lowest RCN?!It should select the highest RCN, indicating the most recent record added. RCN is an Identity column which counts up.
Your original code used datetime to determine the "last" row. Indeed, it went to the trouble of a separate subquery to insure that datetime was determinate instead of RCN.
You can change the ORDER BY to just "RCN DESC" if you prefer to go by identity value, but datetime could be more accurate, depending on how the value of the datetime is set/determined.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 19, 2014 at 8:11 pm
Your original code used datetime to determine the "last" row. Indeed, it went to the trouble of a separate subquery to insure that datetime was determinate instead of RCN.
You can change the ORDER BY to just "RCN DESC" if you prefer to go by identity value, but datetime could be more accurate, depending on how the value of the datetime is set/determined.
To clarify, the first query selects the most recent record based on fldDateTime. If there are multiple records with the same fldDateTime value, it looks to the RCN field and returns the newest entry which would have the higher RCN. However, new entries (higher RCN) could be for previous times and do not necessarily represent more recent activity (out of order entries).
November 20, 2014 at 8:19 am
ScottPletcher (11/19/2014)
Probably the big thing is to reduce all the gyrations around UnitLog. Please try the query below. If you tend to restrict UnitLog rows using fldDateTime range, particularly more restrictively rather than just "<= 15 seconds ago", I suggest you strongly consider clustering UnitLog on ( fldDateTime, RCN ) rather than just RCN/identity.Likewise, but with much lesser overall impact, dbo.DepartmentPersonnel should very likely be clustered by ( DepartmentID, PersonnelID ) rather than identity/RCN.
SELECT TOP (100) PERCENT
ul.UnitNumber
,ul.fldDateTime
,CASE
WHEN derivedtbl_2.SectionNumber IS NULL
THEN CASE
WHEN Departments.SectionNumber IS NULL
THEN '9999'
ELSE Departments.SectionNumber
END
ELSE derivedtbl_2.SectionNumber
END AS SectionNumber
,dbo.AlphaUnits.UnitStatus AS AlphaStatus
,CASE
WHEN dbo.ActiveSafetyTimers.TimerExpr IS NULL
THEN NULL
ELSE TimerExpr
END AS TimerExpr
,ul.RCN
,ul.UnitStatus
,CASE
WHEN CCNo = ''
THEN NULL
ELSE CCNo
END AS CCNo
,CASE
WHEN Unit2 IS NULL
THEN NULL
ELSE '*'
END AS IsJoined
,ul.CommCenter AS CurrentCommCenter
,CASE
WHEN derivedtbl_2.CommCenter IS NULL
THEN departments.CommCenter
ELSE derivedtbl_2.CommCenter
END AS PrimaryCommCenter
FROM (
SELECT
ul2.*,
ROW_NUMBER() OVER(PARTITION BY ul2.UnitNumber ORDER BY fldDateTime DESC) AS row_num
FROM dbo.UnitLog ul2
WHERE
(ul2.UnitStatus <> 'LOG') AND
(ul2.fldDateTime <= DATEADD(SECOND, 15, GETDATE()))
) AS ul
LEFT JOIN dbo.Departments ON ul.UnitNumber = dbo.Departments.CallSign
LEFT JOIN (
SELECT TOP (100) PERCENT [#temp_qryPersonnel].DepartmentID
,[#temp_qryPersonnel].UnitID
,Departments_1.SectionNumber
,Departments_1.CommCenter
FROM dbo.DepartmentPersonnel AS [#temp_qryPersonnel]
LEFT JOIN dbo.Departments AS Departments_1 ON [#temp_qryPersonnel].DepartmentID = Departments_1.RCN
WHERE ([#temp_qryPersonnel].Active = 1)
AND ([#temp_qryPersonnel].UnitID IS NOT NULL)
--
ORDER BY [#temp_qryPersonnel].UnitID) AS derivedtbl_2 ON ul.UnitNumber = derivedtbl_2.UnitID
LEFT JOIN dbo.AlphaUnits ON ul.UnitNumber = dbo.AlphaUnits.UnitNumber
LEFT JOIN dbo.JoinedUnits ON ul.UnitNumber = dbo.JoinedUnits.Unit1
LEFT JOIN dbo.ActiveSafetyTimers ON ul.UnitNumber = dbo.ActiveSafetyTimers.UnitNumber
WHERE
ul.row_num = 1 AND
ul.UnitStatus NOT LIKE '10-42%'
ORDER BY ul.UnitNumber
I indexed the UnitLog table by deleting the PK clustered index and creating a clustered index on fldDateTime and RCN. Running this query, it returns the expected result set, but does so seven (7) to ten (10) times slower. Something I had not considered previously, and I do not know if it would help or not, but would it be beneficial to limit the number of records it is looking at when it starts? Instead of the whole table, just the last couple days or weeks?
November 20, 2014 at 8:49 am
I modified the query to select just the past two (2) weeks, and it sped up significantly. I welcome any other performance enhancement suggestions, but this may have solved the issue.ultra
SELECT TOP (100) PERCENT
ul.UnitNumber
,ul.fldDateTime
,CASE
WHEN derivedtbl_2.SectionNumber IS NULL
THEN CASE
WHEN Departments.SectionNumber IS NULL
THEN '9999'
ELSE Departments.SectionNumber
END
ELSE derivedtbl_2.SectionNumber
END AS SectionNumber
,dbo.AlphaUnits.UnitStatus AS AlphaStatus
,CASE
WHEN dbo.ActiveSafetyTimers.TimerExpr IS NULL
THEN NULL
ELSE TimerExpr
END AS TimerExpr
,ul.RCN
,ul.UnitStatus
,CASE
WHEN CCNo = ''
THEN NULL
ELSE CCNo
END AS CCNo
,CASE
WHEN Unit2 IS NULL
THEN NULL
ELSE '*'
END AS IsJoined
,ul.CommCenter AS CurrentCommCenter
,CASE
WHEN derivedtbl_2.CommCenter IS NULL
THEN departments.CommCenter
ELSE derivedtbl_2.CommCenter
END AS PrimaryCommCenter
FROM (
SELECT
ul2.*,
ROW_NUMBER() OVER(PARTITION BY ul2.UnitNumber ORDER BY fldDateTime DESC) AS row_num
FROM dbo.UnitLog ul2
WHERE
(ul2.UnitStatus <> 'LOG') AND
(ul2.fldDateTime BETWEEN DATEADD(DAY,DATEDIFF(day,14,GETDATE()),0) AND DATEADD(SECOND, 15, GETDATE()))
) AS ul
LEFT JOIN dbo.Departments ON ul.UnitNumber = dbo.Departments.CallSign
LEFT JOIN (
SELECT TOP (100) PERCENT [#temp_qryPersonnel].DepartmentID
,[#temp_qryPersonnel].UnitID
,Departments_1.SectionNumber
,Departments_1.CommCenter
FROM dbo.DepartmentPersonnel AS [#temp_qryPersonnel]
LEFT JOIN dbo.Departments AS Departments_1 ON [#temp_qryPersonnel].DepartmentID = Departments_1.RCN
WHERE ([#temp_qryPersonnel].Active = 1)
AND ([#temp_qryPersonnel].UnitID IS NOT NULL)
--ORDER BY [#temp_qryPersonnel].UnitID
) AS derivedtbl_2 ON ul.UnitNumber = derivedtbl_2.UnitID
LEFT JOIN dbo.AlphaUnits ON ul.UnitNumber = dbo.AlphaUnits.UnitNumber
LEFT JOIN dbo.JoinedUnits ON ul.UnitNumber = dbo.JoinedUnits.Unit1
LEFT JOIN dbo.ActiveSafetyTimers ON ul.UnitNumber = dbo.ActiveSafetyTimers.UnitNumber
WHERE
ul.row_num = 1 AND
ul.UnitStatus NOT LIKE '10-42%'
November 20, 2014 at 10:35 am
swhetsell (11/20/2014)
ScottPletcher (11/19/2014)
Probably the big thing is to reduce all the gyrations around UnitLog. Please try the query below. If you tend to restrict UnitLog rows using fldDateTime range, particularly more restrictively rather than just "<= 15 seconds ago", I suggest you strongly consider clustering UnitLog on ( fldDateTime, RCN ) rather than just RCN/identity.I indexed the UnitLog table by deleting the PK clustered index and creating a clustered index on fldDateTime and RCN. Running this query, it returns the expected result set, but does so seven (7) to ten (10) times slower. Something I had not considered previously, and I do not know if it would help or not, but would it be beneficial to limit the number of records it is looking at when it starts? Instead of the whole table, just the last couple days or weeks?
I think you overlooked the qualification: "If you tend to restrict UnitLog rows using fldDateTime range, particularly more restrictively rather than just <= 15 seconds ago"
For queries with no date range, such as those above, the best clustering would be ( UnitNumber, fldDateTime ). But, as noted, if you limited it to the last n weeks, and you can do that (almost) all of the time when querying, then the ( fldDateTime, RCN) clustering should do very well. RCN by itself is useless for querying.
I'd need to see the query plan to see what happened with the awful performance for the query you're talking about.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 20, 2014 at 2:07 pm
I'm not sure how else to post it, so here is the plan in XML format.
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1.1" Build="10.0.5512.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="39232.5" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="130.18" StatementText="SELECT TOP (100) PERCENT ul.UnitNumber ,ul.fldDateTime ,CASE WHEN derivedtbl_2.SectionNumber IS NULL THEN CASE WHEN Departments.SectionNumber IS NULL THEN '9999' ELSE Departments.SectionNumber END ELSE derivedtbl_2.SectionNumber END AS SectionNumber ,dbo.AlphaUnits.UnitStatus AS AlphaStatus ,CASE WHEN dbo.ActiveSafetyTimers.TimerExpr IS NULL THEN NULL ELSE TimerExpr END AS TimerExpr ,ul.RCN ,ul.UnitStatus ,CASE WHEN CCNo = '' THEN NULL ELSE CCNo END AS CCNo ,CASE WHEN Unit2 IS NULL THEN NULL ELSE '*' END AS IsJoined ,ul.CommCenter AS CurrentCommCenter ,CASE WHEN derivedtbl_2.CommCenter IS NULL THEN departments.CommCenter ELSE derivedtbl_2.CommCenter END AS PrimaryCommCenter FROM ( SELECT ul2.*, ROW_NUMBER() OVER(PARTITION BY ul2.UnitNumber ORDER BY fldDateTime DESC) AS row_num FROM dbo.UnitLog ul2 WHERE (ul2.UnitStatus <> 'LOG') AND (ul2.fldDateTime <= DATEADD(SECOND, 15, GETDATE())) ) AS ul LEFT JOIN dbo.Departments ON ul.UnitNumber = dbo.Departments.CallSign LEFT JOIN ( SELECT TOP (100) PERCENT [#temp_qryPersonnel].DepartmentID ,[#temp_qryPersonnel].UnitID ,Departments_1.SectionNumber ,Departments_1.CommCenter FROM dbo.DepartmentPersonnel AS [#temp_qryPersonnel] LEFT JOIN dbo.Departments AS Departments_1 ON [#temp_qryPersonnel].DepartmentID = Departments_1.RCN WHERE ([#temp_qryPersonnel].Active = 1) AND ([#temp_qryPersonnel].UnitID IS NOT NULL) --ORDER BY [#temp_qryPersonnel].UnitID ) AS derivedtbl_2 ON ul.UnitNumber = derivedtbl_2.UnitID LEFT JOIN dbo.AlphaUnits ON ul.UnitNumber = dbo.AlphaUnits.UnitNumber LEFT JOIN dbo.JoinedUnits ON ul.UnitNumber = dbo.JoinedUnits.Unit1 LEFT JOIN dbo.ActiveSafetyTimers ON ul.UnitNumber = dbo.ActiveSafetyTimers.UnitNumber WHERE ul.row_num = 1 AND ul.UnitStatus NOT LIKE '10-42%'" StatementType="SELECT" QueryHash="0xD6474B54A498D18C" QueryPlanHash="0x447C1CC7E068385D">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="4" MemoryGrant="167584" CachedPlanSize="160" CompileTime="102" CompileCPU="102" CompileMemory="1744">
<RelOp AvgRowSize="62" EstimateCPU="0.00392325" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39232.5" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="130.18">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Column="UnitStatus" />
<ColumnReference Column="Expr1022" />
<ColumnReference Column="Expr1023" />
<ColumnReference Column="Expr1024" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1026" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1022" />
<ScalarOperator ScalarString="CASE WHEN [Expr1011] IS NULL THEN CASE WHEN [CADRecords].[dbo].[Departments].[SectionNumber] IS NULL THEN '9999' ELSE [CADRecords].[dbo].[Departments].[SectionNumber] END ELSE [Expr1011] END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1011" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="SectionNumber" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="'9999'" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="SectionNumber" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1011" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1023" />
<ScalarOperator ScalarString="CASE WHEN [CADRecords].[dbo].[ActiveSafetyTimers].[TimerExpr] IS NULL THEN NULL ELSE [CADRecords].[dbo].[ActiveSafetyTimers].[TimerExpr] END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[ActiveSafetyTimers]" Column="TimerExpr" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[ActiveSafetyTimers]" Column="TimerExpr" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1026" />
<ScalarOperator ScalarString="CASE WHEN [Expr1012] IS NULL THEN [CADRecords].[dbo].[Departments].[CommCenter] ELSE [Expr1012] END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1012" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CommCenter" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1012" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="68" EstimateCPU="0.0293824" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39232.5" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="130.176">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="SectionNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CommCenter" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1012" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[ActiveSafetyTimers]" Column="TimerExpr" />
<ColumnReference Column="Expr1024" />
<ColumnReference Column="Expr1025" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="43" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Parallelism>
<RelOp AvgRowSize="68" EstimateCPU="0.194781" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39232.5" LogicalOp="Right Outer Join" NodeId="2" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="130.147">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="SectionNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CommCenter" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1012" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[ActiveSafetyTimers]" Column="TimerExpr" />
<ColumnReference Column="Expr1024" />
<ColumnReference Column="Expr1025" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="11" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="12" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[ActiveSafetyTimers]" Column="UnitNumber" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[CADRecords].[dbo].[UnitLog].[UnitNumber] as [ul2].[UnitNumber]=[CADRecords].[dbo].[ActiveSafetyTimers].[UnitNumber]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[ActiveSafetyTimers]" Column="UnitNumber" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="22" EstimateCPU="0.0285498" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="16" LogicalOp="Distribute Streams" NodeId="3" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0318494">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[ActiveSafetyTimers]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[ActiveSafetyTimers]" Column="TimerExpr" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRows="16" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="16" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="16" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="16" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Parallelism PartitioningType="Broadcast">
<RelOp AvgRowSize="22" EstimateCPU="0.0001746" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="16" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032996" TableCardinality="16">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[ActiveSafetyTimers]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[ActiveSafetyTimers]" Column="TimerExpr" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="1" ActualRows="16" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[ActiveSafetyTimers]" Column="UnitNumber" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[ActiveSafetyTimers]" Column="TimerExpr" />
</DefinedValue>
</DefinedValues>
<Object Database="[CADRecords]" Schema="[dbo]" Table="[ActiveSafetyTimers]" Index="[PK__ActiveSa__D3CC338A39CD8610]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</Parallelism>
</RelOp>
<RelOp AvgRowSize="60" EstimateCPU="0.00196162" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39232.5" LogicalOp="Compute Scalar" NodeId="5" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="129.92">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="SectionNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CommCenter" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1012" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Column="UnitStatus" />
<ColumnReference Column="Expr1024" />
<ColumnReference Column="Expr1025" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1025" />
<ScalarOperator ScalarString="CASE WHEN [CADRecords].[dbo].[JoinedUnits].[Unit2] IS NULL THEN NULL ELSE '*' END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[JoinedUnits]" Column="Unit2" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="'*'" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="64" EstimateCPU="0.0477918" EstimateIO="0.0004695" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39232.5" LogicalOp="Left Outer Join" NodeId="6" Parallel="true" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="129.918">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="SectionNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CommCenter" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1012" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[JoinedUnits]" Column="Unit2" />
<ColumnReference Column="Expr1024" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="11" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="12" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Merge ManyToMany="true">
<InnerSideJoinColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[JoinedUnits]" Column="Unit1" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[CADRecords].[dbo].[UnitLog].[UnitNumber] as [ul2].[UnitNumber]=[CADRecords].[dbo].[JoinedUnits].[Unit1]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[JoinedUnits]" Column="Unit1" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="58" EstimateCPU="0.0496031" EstimateIO="0.0001565" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39232.5" LogicalOp="Left Outer Join" NodeId="7" Parallel="true" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="129.838">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="SectionNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CommCenter" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1012" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Column="UnitStatus" />
<ColumnReference Column="Expr1024" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="11" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="12" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Merge ManyToMany="true">
<InnerSideJoinColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Column="UnitNumber" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[CADRecords].[dbo].[UnitLog].[UnitNumber] as [ul2].[UnitNumber]=[CADRecords].[dbo].[AlphaUnits].[UnitNumber]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Column="UnitNumber" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="57" EstimateCPU="0.053908" EstimateIO="0.0379382" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39232.5" LogicalOp="Left Outer Join" NodeId="8" Parallel="true" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="129.757">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="SectionNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CommCenter" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1012" />
<ColumnReference Column="Expr1024" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="11" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="12" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Merge ManyToMany="true">
<InnerSideJoinColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="UnitID" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[CADRecords].[dbo].[UnitLog].[UnitNumber] as [ul2].[UnitNumber]=[CADRecords].[dbo].[DepartmentPersonnel].[UnitID] as [#temp_qryPersonnel].[UnitID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="UnitID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="50" EstimateCPU="0.0557269" EstimateIO="0.000313" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39232.5" LogicalOp="Right Outer Join" NodeId="9" Parallel="true" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="129.534">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="SectionNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CommCenter" />
<ColumnReference Column="Expr1024" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="11" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="12" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Merge ManyToMany="true">
<InnerSideJoinColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CallSign" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[CADRecords].[dbo].[UnitLog].[UnitNumber] as [ul2].[UnitNumber]=[CADRecords].[dbo].[Departments].[CallSign]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CallSign" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="21" EstimateCPU="0.000788558" EstimateIO="0.00563063" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="134" LogicalOp="Sort" NodeId="10" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0411271">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="SectionNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CommCenter" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CallSign" />
</OutputList>
<MemoryFractions Input="0.000204719" Output="0.000204719" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRebinds="1" ActualRewinds="0" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRebinds="1" ActualRewinds="0" ActualRows="131" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CallSign" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="21" EstimateCPU="0.0290563" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="134" LogicalOp="Distribute Streams" NodeId="11" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0347079">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="SectionNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CommCenter" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CallSign" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="131" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CallSign" />
</PartitionColumns>
<RelOp AvgRowSize="21" EstimateCPU="0.0003044" EstimateIO="0.00534722" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="134" LogicalOp="Clustered Index Scan" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00565162" TableCardinality="134">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="SectionNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CommCenter" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CallSign" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="1" ActualRows="134" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="SectionNumber" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CommCenter" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Column="CallSign" />
</DefinedValue>
</DefinedValues>
<Object Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Index="[PK__Departme__CAFF900C47A6A41B]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</Parallelism>
</RelOp>
</Sort>
</RelOp>
<RelOp AvgRowSize="44" EstimateCPU="0.365649" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39232.5" LogicalOp="Repartition Streams" NodeId="13" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="129.437">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
<ColumnReference Column="Expr1024" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="11" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="12" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</PartitionColumns>
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="44" EstimateCPU="1.03172" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39232.5" LogicalOp="Filter" NodeId="14" Parallel="true" PhysicalOp="Filter" EstimatedTotalSubtreeCost="129.071">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
<ColumnReference Column="Expr1024" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="3" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="7" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="15" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="11" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Filter StartupExpression="false">
<RelOp AvgRowSize="52" EstimateCPU="0.074762" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1495240" LogicalOp="Compute Scalar" NodeId="15" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="128.04">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
<ColumnReference Column="Expr1002" />
<ColumnReference Column="Expr1024" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1024" />
<ScalarOperator ScalarString="CASE WHEN [CADRecords].[dbo].[UnitLog].[CCNo] as [ul2].[CCNo]='' THEN NULL ELSE [CADRecords].[dbo].[UnitLog].[CCNo] as [ul2].[CCNo] END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CCNo" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="''" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CCNo" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="52" EstimateCPU="11.3931" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1495240" LogicalOp="Distribute Streams" NodeId="16" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="127.965">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CCNo" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
<ColumnReference Column="Expr1002" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="3" ActualRows="373810" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="373810" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="373810" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="373810" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Parallelism PartitioningType="RoundRobin">
<RelOp AvgRowSize="52" EstimateCPU="0.119619" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1495240" LogicalOp="Compute Scalar" NodeId="17" Parallel="false" PhysicalOp="Sequence Project" EstimatedTotalSubtreeCost="116.572">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CCNo" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
<ColumnReference Column="Expr1002" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="1" ActualRows="1495240" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<SequenceProject>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1002" />
<ScalarOperator ScalarString="row_number">
<Sequence FunctionName="row_number" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="52" EstimateCPU="0.0299048" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1495240" LogicalOp="Segment" NodeId="18" Parallel="false" PhysicalOp="Segment" EstimatedTotalSubtreeCost="116.452">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CCNo" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
<ColumnReference Column="Segment1034" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="1" ActualRows="1495240" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Segment>
<GroupBy>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</GroupBy>
<SegmentColumn>
<ColumnReference Column="Segment1034" />
</SegmentColumn>
<RelOp AvgRowSize="44" EstimateCPU="12.4132" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1495240" LogicalOp="Gather Streams" NodeId="19" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="116.422">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CCNo" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="1" ActualRows="1495240" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Parallelism>
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</OrderByColumn>
<OrderByColumn Ascending="false">
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="44" EstimateCPU="23.6191" EstimateIO="73.2151" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1495240" LogicalOp="Sort" NodeId="20" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="104.009">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CCNo" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
</OutputList>
<MemoryFractions Input="0.998567" Output="0.998567" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="3" ActualRebinds="1" ActualRewinds="0" ActualRows="393266" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRebinds="1" ActualRewinds="0" ActualRows="350378" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRebinds="1" ActualRewinds="0" ActualRows="373879" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRebinds="1" ActualRewinds="0" ActualRows="377717" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</OrderByColumn>
<OrderByColumn Ascending="false">
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="44" EstimateCPU="0.835735" EstimateIO="5.67053" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1495240" LogicalOp="Index Scan" NodeId="21" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="6.50627" TableCardinality="1519380">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CCNo" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="3" ActualRows="393266" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="350378" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="373879" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="377717" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="RCN" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitNumber" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CCNo" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="CommCenter" />
</DefinedValue>
</DefinedValues>
<Object Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Index="[SuggestedByUnitClient2]" Alias="[ul2]" IndexKind="NonClustered" />
<Predicate>
<ScalarOperator ScalarString="[CADRecords].[dbo].[UnitLog].[fldDateTime] as [ul2].[fldDateTime]<=CONVERT_IMPLICIT(datetime2(7),dateadd(second,(15),getdate()),0) AND [CADRecords].[dbo].[UnitLog].[UnitStatus] as [ul2].[UnitStatus]<>'LOG'">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="LE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="fldDateTime" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1027">
<ScalarOperator>
<Convert DataType="datetime2" Style="0" Implicit="true">
<ScalarOperator>
<Intrinsic FunctionName="dateadd">
<ScalarOperator>
<Const ConstValue="(8)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(15)" />
</ScalarOperator>
<ScalarOperator>
<Intrinsic FunctionName="getdate" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'LOG'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Sort>
</RelOp>
</Parallelism>
</RelOp>
</Segment>
</RelOp>
</SequenceProject>
</RelOp>
</Parallelism>
</RelOp>
</ComputeScalar>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="NOT [CADRecords].[dbo].[UnitLog].[UnitStatus] as [ul2].[UnitStatus] like '10-42%' AND [Expr1002]=(1)">
<Logical Operation="AND">
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Intrinsic FunctionName="like">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[UnitLog]" Alias="[ul2]" Column="UnitStatus" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'10-42%'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1002" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</Parallelism>
</RelOp>
</Merge>
</RelOp>
<RelOp AvgRowSize="20" EstimateCPU="0.00514215" EstimateIO="0.00563063" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="691.966" LogicalOp="Sort" NodeId="30" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.130618">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="UnitID" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1012" />
</OutputList>
<MemoryFractions Input="0.000614156" Output="0.000614156" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRebinds="1" ActualRewinds="0" ActualRows="175" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRebinds="1" ActualRewinds="0" ActualRows="162" ActualEndOfScans="0" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRebinds="1" ActualRewinds="0" ActualRows="166" ActualEndOfScans="0" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRebinds="1" ActualRewinds="0" ActualRows="168" ActualEndOfScans="0" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="UnitID" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="20" EstimateCPU="3.45983E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="691.966" LogicalOp="Compute Scalar" NodeId="31" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.119845">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="UnitID" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1012" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1011" />
<ScalarOperator ScalarString="[CADRecords].[dbo].[Departments].[SectionNumber] as [Departments_1].[SectionNumber]">
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="SectionNumber" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1012" />
<ScalarOperator ScalarString="[CADRecords].[dbo].[Departments].[CommCenter] as [Departments_1].[CommCenter]">
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="CommCenter" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="20" EstimateCPU="0.0289631" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="691.966" LogicalOp="Repartition Streams" NodeId="32" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.11981">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="UnitID" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="SectionNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="CommCenter" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRows="175" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="177" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="169" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="171" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="UnitID" />
</PartitionColumns>
<RelOp AvgRowSize="20" EstimateCPU="0.00370186" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="691.966" LogicalOp="Right Outer Join" NodeId="33" Parallel="true" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.0908472">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="UnitID" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="SectionNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="CommCenter" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRows="151" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="159" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="205" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="177" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Merge ManyToMany="false">
<InnerSideJoinColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="DepartmentID" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="RCN" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[CADRecords].[dbo].[DepartmentPersonnel].[DepartmentID] as [#temp_qryPersonnel].[DepartmentID]=[CADRecords].[dbo].[Departments].[RCN] as [Departments_1].[RCN]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="DepartmentID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="RCN" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="17" EstimateCPU="0.029006" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="134" LogicalOp="Distribute Streams" NodeId="34" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0346576">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="SectionNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="CommCenter" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRows="32" ActualEndOfScans="0" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="35" ActualEndOfScans="0" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="29" ActualEndOfScans="0" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="31" ActualEndOfScans="0" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="RCN" />
</PartitionColumns>
<RelOp AvgRowSize="17" EstimateCPU="0.0003044" EstimateIO="0.00534722" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="134" LogicalOp="Clustered Index Scan" NodeId="35" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00565162" TableCardinality="134">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="RCN" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="SectionNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="CommCenter" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="1" ActualRows="134" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="RCN" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="SectionNumber" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Alias="[Departments_1]" Column="CommCenter" />
</DefinedValue>
</DefinedValues>
<Object Database="[CADRecords]" Schema="[dbo]" Table="[Departments]" Index="[PK__Departme__CAFF900C47A6A41B]" Alias="[Departments_1]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</Parallelism>
</RelOp>
<RelOp AvgRowSize="18" EstimateCPU="0.00514215" EstimateIO="0.00563063" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="691.966" LogicalOp="Sort" NodeId="36" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0524847">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="DepartmentID" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="UnitID" />
</OutputList>
<MemoryFractions Input="0.000614156" Output="0.000614156" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRebinds="1" ActualRewinds="0" ActualRows="151" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRebinds="1" ActualRewinds="0" ActualRows="159" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRebinds="1" ActualRewinds="0" ActualRows="205" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRebinds="1" ActualRewinds="0" ActualRows="177" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="DepartmentID" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="18" EstimateCPU="0.0296633" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="691.966" LogicalOp="Repartition Streams" NodeId="37" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0417119">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="DepartmentID" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="UnitID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRows="151" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="159" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="205" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="177" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="DepartmentID" />
</PartitionColumns>
<RelOp AvgRowSize="19" EstimateCPU="0.0004657" EstimateIO="0.0112731" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="691.966" LogicalOp="Clustered Index Scan" NodeId="38" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0117388" TableCardinality="704">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="DepartmentID" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="UnitID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="3" ActualRows="179" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="182" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="169" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="162" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="DepartmentID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="UnitID" />
</DefinedValue>
</DefinedValues>
<Object Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Index="[PK_DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[CADRecords].[dbo].[DepartmentPersonnel].[Active] as [#temp_qryPersonnel].[Active]=(1) AND [CADRecords].[dbo].[DepartmentPersonnel].[UnitID] as [#temp_qryPersonnel].[UnitID] IS NOT NULL">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="Active" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[DepartmentPersonnel]" Alias="[#temp_qryPersonnel]" Column="UnitID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Parallelism>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
</Parallelism>
</RelOp>
</ComputeScalar>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
<RelOp AvgRowSize="15" EstimateCPU="0.0285065" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Distribute Streams" NodeId="46" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0317907">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Column="UnitStatus" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Column="UnitNumber" />
</PartitionColumns>
<RelOp AvgRowSize="15" EstimateCPU="0.0001592" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Clustered Index Scan" NodeId="47" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="2">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Column="UnitNumber" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Column="UnitStatus" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="1" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Column="UnitNumber" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Column="UnitStatus" />
</DefinedValue>
</DefinedValues>
<Object Database="[CADRecords]" Schema="[dbo]" Table="[AlphaUnits]" Index="[PK__AlphaUni__44F5EC95286302EC]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</Parallelism>
</RelOp>
</Merge>
</RelOp>
<RelOp AvgRowSize="20" EstimateCPU="0.0285108" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3" LogicalOp="Distribute Streams" NodeId="49" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0317961">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[JoinedUnits]" Column="Unit1" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[JoinedUnits]" Column="Unit2" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="4" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[JoinedUnits]" Column="Unit1" />
</PartitionColumns>
<RelOp AvgRowSize="20" EstimateCPU="0.0001603" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3" LogicalOp="Clustered Index Scan" NodeId="50" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3">
<OutputList>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[JoinedUnits]" Column="Unit1" />
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[JoinedUnits]" Column="Unit2" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="1" ActualRows="3" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[JoinedUnits]" Column="Unit1" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CADRecords]" Schema="[dbo]" Table="[JoinedUnits]" Column="Unit2" />
</DefinedValue>
</DefinedValues>
<Object Database="[CADRecords]" Schema="[dbo]" Table="[JoinedUnits]" Index="[PK_JoinedUnits]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</Parallelism>
</RelOp>
</Merge>
</RelOp>
</ComputeScalar>
</RelOp>
</Hash>
</RelOp>
</Parallelism>
</RelOp>
</ComputeScalar>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply