January 5, 2021 at 9:52 pm
First off, I didn't write this query. It's created dynamically in code I inherited. It works 99.9999% of the time so I'd prefer not to touch it. However. The following query just hangs:
Select * From
(Select Top 1 Track, RequestID, ClientName, MatterName, CASE WHEN PATINDEX('% - Yellow', PhaseName) > 0 THEN REPLACE(PhaseName, ' - Yellow', '')
ELSE PhaseName END AS PhaseName, MPApprovalStatusName, DateOpened, RequestorName, Notes, IsNewClient, AffiliatedClients
From (Select Top 15 * From vwRequests As T1 Where ClientName Like '%harch%' Order by DateOpened DESC) As T2 Order by DateOpened ASC)
As T3 Order by DateOpened DESC
If I replace "harch" in the LIKE statement with "chameleon" (both should return results), results are returned with no problem. If I change the LIKE '%harch%' to = '<complete client name>', it works. If I leave LIKE, but remove the CASE statement and just output the PhaseName, it works. I experimented and did this:
Select Top 15 *,PATINDEX('% - Yellow', PhaseName)
From vwRequests As T1 Where ClientName Like '%harch%' Order by DateOpened DESCIt works fine. However, when I do this:
Select Top 15 *,PATINDEX('% - Yellow', PhaseName),REPLACE(PhaseName, ' - Yellow', '')
From vwRequests As T1 Where ClientName Like '%harch%' Order by DateOpened DESCit hangs. What's weird about this is that PhaseName in this case is "Completed." Why would the REPLACE statement cause a problem? And why would LIKE be problematic in this case but not others? Thanks in advance.
January 5, 2021 at 10:18 pm
A couple of things - first, using '%harch%' requires a full table/index scan because of the leading wildcard. Second, you need to review the code in the view to see how if PhaseName and or ClientName are calculated in the view itself.
Using TOP would normally allow for a quicker selection - however, it cannot select the TOP 15 until it has searched every row in the result from vwRequests. And since you are then getting the TOP 1 ordered by dateopened (in each portion - which is going to require 3 sorts - I think) - it is going to be problematic.
The query could be reduced to just this:
Select Top 1 Track, RequestID, ClientName, MatterName, CASE WHEN PATINDEX('% - Yellow', PhaseName) > 0 THEN REPLACE(PhaseName, ' - Yellow', '')
ELSE PhaseName END AS PhaseName, MPApprovalStatusName, DateOpened, RequestorName, Notes, IsNewClient, AffiliatedClients
From vwRequests As T1
Where ClientName Like '%harch%'
Order by DateOpened DESCBut you still need to evaluate the view and see what it is doing.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 5, 2021 at 10:30 pm
The nested subqueries seem not to be necessary imo. What is the point of selecting the top 15 rows only to select the top 1 from that subset using the same ORDER BY column? Then it selects all rows from the 1 row. Nesting and nesting... it can result in unpredictable execution plans. The query is dependent on locating rows using ClientName and DateOpened columns. The 'Like' operator removes the effectiveness of indexes (besides kludgy workarounds or full text search). Is DateOpened indexed?
/* original */
Select * From
(Select Top 1 Track, RequestID, ClientName, MatterName,
CASE WHEN PATINDEX('% - Yellow', PhaseName) > 0
THEN REPLACE(PhaseName, ' - Yellow', '')
ELSE PhaseName END AS PhaseName,
MPApprovalStatusName, DateOpened, RequestorName,
Notes, IsNewClient, AffiliatedClients
From (Select Top 15 *
From vwRequests As T1
Where ClientName Like '%harch%'
Order by DateOpened DESC) As T2
Order by DateOpened ASC) As T3
Order by DateOpened DESC
/* minimal? */
select top 1 Track, RequestID, ClientName, MatterName,
case when patindex('% - Yellow', PhaseName) > 0
then replace(PhaseName, ' - Yellow', '')
else PhaseName end as PhaseName,
MPApprovalStatusName, DateOpened, RequestorName,
Notes, IsNewClient, AffiliatedClients
from vwRequests as T1
where ClientName like '%harch%'
order by DateOpened desc;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 6, 2021 at 12:01 am
Can you post the query for the view being referenced? At least I'm assuming that "vwRequests" is a view. I can't tell what indexes might help this query without seeing the view. If the view uses multiple tables and doesn't alias all columns used in the view, I'll also need the DDL for all the tables in the view.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 6, 2021 at 2:57 pm
The execution plan would help a lot.
Also, two different ORDER BY statements going in different directions? Yikes. Maybe... maybe, two indexes ordered in each direction could help performance. That's a SWAG though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2021 at 3:55 pm
Good observations. The view, like this query, is a mess. It joins 7 tables, including one in a database on another server with a different collation, and another view. However, the PhaseName is coming directly from the table I would expect. That table has no indexes on it other than a primary key constraint. What I'm trying to figure out is why this particular record (harch) causes a problem but other records don't. I'm going to compare execution plans. Thanks for your thoughtful replies.
January 6, 2021 at 5:45 pm
Good observations. The view, like this query, is a mess. It joins 7 tables, including one in a database on another server with a different collation, and another view. However, the PhaseName is coming directly from the table I would expect.
Still need to see the actual view code and, if at all possible, table DDL for the 7 tables. It's still possible that an index would help and let you not have to rewrite the query (at least for now), which you said you didn't want to do.
You "expect" a certain table to have a certain column, but we know nothing about your tables nor data (how could we?), so we need to see the DDL to have any idea what columns go with which tables.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 6, 2021 at 6:20 pm
You need to start by looking at the view definition of vwRequests
January 6, 2021 at 6:28 pm
OK, I'm posting the DDLs for all the tables used in both views. I'm also posting both execution plans, the one that takes 3 secs (chameleon) and the one that takes 50 mins (harch). I'll post the defs of the 2 views in my next reply. I really appreciate everyone's effort on this.
January 6, 2021 at 6:32 pm
Here are the defs of the 2 views. One thing I discovered is that ConflictCheckStatus, referenced in vwRequests, is a view. I add it here.
January 7, 2021 at 3:38 pm
I don't see any view defs. Instead of attaching them, you can post them using the "insert/edit code sample" button when you go to post a Reply.
If I'm overlooked them and they have been posted, please point that out to me. Sorry for missing it in that case.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 7, 2021 at 4:29 pm
No, I didn't get an error message when I posted the files, but apparently they didn't upload. So here goes. First the DDL for vwRequests view:
USE [CMI]
GO
/****** Object: View [dbo].[vwRequests] Script Date: 01/06/2021 1:05:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*Modified 11/17/2008 MSP
Added AffiliatedClients field
Modified 5/4/2011 MSP
Added COLLATE SQL_Latin1_General_CP1_CI_AS to linked server queries
Modification MSP 5/23/2011 Changed Elite server from NYSQL005 to ELITE (linked server name)
Modification MSP 3/30/2016 Added Notes field to SELECT statement
--------------------------------------------------------------*/
CREATE VIEW [dbo].[vwRequests]
AS
SELECT r.RequestID, r.DateOpened, r.CurrentPhaseID, r.RequestorID, u.FirstName + ' ' + u.LastName AS RequestorName, ccs.StatusID AS CCStatus,
CASE WHEN r.IsNewClient = 1 THEN COALESCE (nc.clname1, 'Pending User Input') ELSE COALESCE (ec.clname1, 'Existing Client') COLLATE SQL_Latin1_General_CP1_CI_AS
END AS ClientName, CASE WHEN r.IsNewClient = 1 THEN COALESCE (nc.clnum, 'TBD by Accounting') ELSE COALESCE (ec.clnum,
'Existing Client') COLLATE SQL_Latin1_General_CP1_CI_AS END AS ClientNumber, COALESCE (m.mmatter, 'TBD by Accounting ') AS MatterNumber,
COALESCE (m.mname, 'Pending User Input') AS MatterName, p.PhaseName, ccs.StatusName AS CCStatusName, aps.StatusID AS MPApprovalStatus,
aps.StatusName AS MPApprovalStatusName, r.IsNewClient, ISNULL(nc.AffiliatedClients, '') AS AffiliatedClients, r.Notes, r.Track
FROM dbo.ConflictCheckRequest AS r LEFT OUTER JOIN
dbo.Matter AS m ON r.RequestID = m.RequestID LEFT OUTER JOIN
dbo.RequestPhase AS p ON r.CurrentPhaseID = p.PhaseID LEFT OUTER JOIN
dbo.ConflictCheckStatus AS ccs ON r.RequestID = ccs.RequestID LEFT OUTER JOIN
dbo.vwMPApprovalStatus AS aps ON r.RequestID = aps.RequestID LEFT OUTER JOIN
dbo.[User] AS u ON r.RequestorID = u.EmployeeID LEFT OUTER JOIN
ELITE.son_db.dbo.client AS ec ON r.clnum = ec.clnum COLLATE SQL_Latin1_General_CP1_CI_AS LEFT OUTER JOIN
dbo.Client AS nc ON r.cltid = nc.cltid
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "r"
Begin Extent =
Top = 6
Left = 38
Bottom = 135
Right = 209
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "m"
Begin Extent =
Top = 6
Left = 247
Bottom = 135
Right = 436
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "p"
Begin Extent =
Top = 138
Left = 38
Bottom = 233
Right = 208
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "ccs"
Begin Extent =
Top = 138
Left = 246
Bottom = 250
Right = 416
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "aps"
Begin Extent =
Top = 234
Left = 38
Bottom = 346
Right = 208
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "u"
Begin Extent =
Top = 252
Left = 246
Bottom = 381
Right = 416
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "ec"
Begin Extent =
Top = 348
Left = 38
Bottom = 477
Right = 208
End
DisplayFlags = 280
TopColumn = 0
E' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vwRequests'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'nd
Begin Table = "nc"
Begin Extent =
Top = 384
Left = 246
Bottom = 513
Right = 422
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 9
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vwRequests'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vwRequests'
GO
The DDL for Client table:
USE [CMI]
GO
/****** Object: Table [dbo].[Client] Script Date: 01/06/2021 1:09:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Client](
[cltid] [int] IDENTITY(1,1) NOT NULL,
[clnum] [varchar](14) NULL,
[corgaty] [varchar](8) NULL,
[clopendt] [datetime] NULL,
[clname1] [varchar](48) NULL,
[clname2] [varchar](48) NULL,
[claddr1] [varchar](48) NULL,
[claddr2] [varchar](48) NULL,
[claddr3] [varchar](48) NULL,
[claddr4] [varchar](48) NULL,
[claddr5] [varchar](48) NULL,
[claddr6] [varchar](48) NULL,
[clcity] [varchar](24) NULL,
[clstate] [varchar](20) NULL,
[clcountry] [varchar](30) NULL,
[clzip] [varchar](10) NULL,
[clcontact] [varchar](60) NULL,
[cltemail] [varchar](50) NULL,
[clphone] [varchar](14) NULL,
[cltphone2] [varchar](14) NULL,
[cltwebsite] [varchar](50) NULL,
[clfax] [varchar](14) NULL,
[AddToRL] [dbo].[boolean] NULL,
[RestrictedListing] [nvarchar](450) NULL,
[AffiliatedClients] [nvarchar](1000) NULL,
[cbustype] [varchar](4) NULL,
[nowebsite] [bit] NULL,
[cltype] [int] NULL,
CONSTRAINT [PK_ClientContact] PRIMARY KEY CLUSTERED
(
[cltid] 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
The DDL for ConflictCheckRequest table:
USE [CMI]
GO
/****** Object: Table [dbo].[ConflictCheckRequest] Script Date: 01/06/2021 1:07:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ConflictCheckRequest](
[RequestID] [int] IDENTITY(1,1) NOT NULL,
[CurrentPhaseID] [int] NOT NULL,
[DateOpened] [datetime] NOT NULL,
[IsNewClient] [bit] NULL,
[cltid] [int] NULL,
[clnum] [char](14) NULL,
[RequestorID] [varchar](5) NULL,
[Notes] [varchar](200) NULL,
[Track] [varchar](10) NULL,
[TrackReason] [varchar](200) NULL,
CONSTRAINT [PK_ConflictCheckRequest] PRIMARY KEY CLUSTERED
(
[RequestID] 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
The DDL for User table:
USE [CMI]
GO
/****** Object: Table [dbo].[User] Script Date: 01/06/2021 1:09:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
[EmployeeID] [nvarchar](6) NOT NULL,
[FirstName] [varchar](25) NOT NULL,
[LastName] [varchar](60) NOT NULL,
[Title] [varchar](50) NULL,
[RoleID] [int] NOT NULL,
[Email] [nvarchar](50) NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[EmployeeID] 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
The DDL for ConflictCheckStatus view:
USE [CMI]
GO
/****** Object: View [dbo].[ConflictCheckStatus] Script Date: 01/06/2021 1:31:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----------------------------------------------------------------
CREATE VIEW [dbo].[ConflictCheckStatus]
AS
SELECT DISTINCT rp.RequestID, ts.StatusName, ts.StatusID
FROM dbo.RequestProgress rp LEFT OUTER JOIN
dbo.TaskStatus ts ON rp.TaskStatusID = ts.StatusID
WHERE (rp.TaskID = 5)
GO
The DDL for client table on the ELITE server:
USE [son_db]
GO
/****** Object: Table [dbo].[client] Script Date: 01/06/2021 1:12:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[client](
[clname1] [varchar](60) NULL,
[clname2] [varchar](60) NULL,
[claddr1] [varchar](60) NULL,
[claddr2] [varchar](60) NULL,
[claddr3] [varchar](60) NULL,
[claddr4] [varchar](60) NULL,
[claddr5] [varchar](60) NULL,
[claddr6] [varchar](60) NULL,
[clphone] [varchar](14) NULL,
[clsort] [varchar](20) NULL,
[clnum] [varchar](14) NULL,
[corgaty] [varchar](8) NULL,
[corgatst] [smallint] NULL,
[ctmplt] [varchar](2) NULL,
[cftmplt] [varchar](2) NULL,
[cfollow1] [varchar](2) NULL,
[cfollow2] [varchar](2) NULL,
[cfollow3] [varchar](2) NULL,
[cfollow4] [varchar](2) NULL,
[clind] [varchar](8) NULL,
[clcontact] [varchar](60) NULL,
[clstatus] [varchar](1) NULL,
[clrefer] [varchar](60) NULL,
[clstop] [varchar](1) NULL,
[clfax] [varchar](14) NULL,
[clbusi] [varchar](20) NULL,
[clcredit] [money] NULL,
[clopendt] [datetime] NULL,
[cbustype] [varchar](4) NULL,
[crelated] [varchar](14) NULL,
[cphprefix] [varchar](10) NULL,
[clcity] [varchar](50) NULL,
[clstate] [varchar](50) NULL,
[clzip] [varchar](10) NULL,
[clcountry] [varchar](50) NULL,
[cmoddate] [datetime] NULL,
[cmodtime] [varchar](8) NULL,
[clarrange] [varchar](4) NULL,
[clstatdate] [datetime] NULL,
[clsortcode] [varchar](6) NULL,
[clmoduser] [varchar](8) NULL,
[cloc] [varchar](4) NULL,
[unit] [varchar](8) NULL,
[iaddress] [int] NULL,
[iphone] [int] NULL,
[iphonef] [int] NULL,
[ientity] [int] NULL,
[cactgroup] [varchar](7) NULL,
[cleusd] [varchar](1) NULL,
[cldomestic] [varchar](1) NULL,
[cltxcountry] [varchar](8) NULL
) ON [PRIMARY]
GO
The DDL for RequestPhase table:
USE [CMI]
GO
/****** Object: Table [dbo].[RequestPhase] Script Date: 01/06/2021 1:08:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RequestPhase](
[PhaseID] [int] IDENTITY(1,1) NOT NULL,
[PhaseName] [varchar](25) NOT NULL,
CONSTRAINT [PK_RequestPhase] PRIMARY KEY CLUSTERED
(
[PhaseName] 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
The DDL for Matter table:
USE [CMI]
GO
/****** Object: Table [dbo].[Matter] Script Date: 01/06/2021 1:08:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Matter](
[RequestID] [int] NOT NULL,
[mmatter] [varchar](15) NULL,
[mopendt] [datetime] NULL,
[mname] [varchar](500) NULL,
[mname2] [varchar](60) NULL,
[morgaty] [varchar](8) NULL,
[mbillaty] [varchar](8) NULL,
[msupaty] [varchar](8) NULL,
[mprac] [varchar](4) NULL,
[mpracticeareaid] [varchar](4) NULL,
[mcompletiondate] [datetime] NULL,
[mdescription] [varchar](450) NULL,
[mcreatorcomment] [varchar](450) NULL,
[cmacqid] [int] NULL,
[ReferralName] [varchar](48) NULL,
[RecmdCltName] [varchar](48) NULL,
[IsTargetClient] [dbo].[boolean] NULL,
[AddToConfMemo] [dbo].[boolean] NULL,
[ConfMemoListing] [varchar](450) NULL,
[misnewareaoflaw] [dbo].[boolean] NULL,
[HasRcvRetLtrFrClt] [int] NULL,
[IsConfidential] [dbo].[boolean] NULL,
[cmfiletypeid] [int] NULL,
[cmfilelocid] [int] NULL,
[RetFile] [varchar](100) NULL,
[XRef] [varchar](100) NULL,
[PBCategory] [varchar](40) NULL,
[Insert2] [varchar](100) NULL,
[Insert3] [varchar](100) NULL,
[Insert4] [varchar](100) NULL,
[Insert5] [varchar](100) NULL,
[AcctgComment] [nvarchar](1000) NULL,
[Reason] [nvarchar](500) NULL,
[Insert1] [varchar](100) NULL,
[mname3] [varchar](60) NULL,
[clntModel] [varchar](14) NULL,
[mtrModel] [varchar](15) NULL,
CONSTRAINT [PK_Matter] PRIMARY KEY CLUSTERED
(
[RequestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Matter] ADD CONSTRAINT [DF_Matter_IsConfidential] DEFAULT ((0)) FOR [IsConfidential]
GO
The DDL for vwMPApprovalStatus view:
USE [CMI]GO/****** Object: View [dbo].[vwMPApprovalStatus] Script Date: 01/06/2021 1:06:14 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO----------------------------------------------------------------CREATE VIEW [dbo].[vwMPApprovalStatus]ASSELECT DISTINCT rp.RequestID, ts.StatusName, ts.StatusIDFROM dbo.RequestProgress rp LEFT OUTER JOINdbo.TaskStatus ts ON rp.TaskStatusID = ts.StatusIDWHERE (rp.TaskID IN (7,13))GO
The DDL for RequestProgress table:
USE [CMI]
GO
/****** Object: Table [dbo].[RequestProgress] Script Date: 01/06/2021 1:11:14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RequestProgress](
[RequestID] [int] NOT NULL,
[TaskID] [int] NOT NULL,
[TaskStatusID] [int] NOT NULL,
[LastUpdateDate] [datetime] NOT NULL,
CONSTRAINT [PK_RequestProgress] PRIMARY KEY CLUSTERED
(
[RequestID] ASC,
[TaskID] ASC,
[TaskStatusID] 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
The DDL for TaskStatus table:
USE [CMI]
GO
/****** Object: Table [dbo].[TaskStatus] Script Date: 01/06/2021 1:11:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TaskStatus](
[StatusID] [int] IDENTITY(1,1) NOT NULL,
[TaskID] [int] NOT NULL,
[StatusName] [varchar](100) NOT NULL,
CONSTRAINT [PK_TaskStatus_1] PRIMARY KEY CLUSTERED
(
[TaskID] ASC,
[StatusName] 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
January 7, 2021 at 4:29 pm
I can upload the execution plans as XML. Is that OK?
January 7, 2021 at 4:39 pm
Yeah, the XML is the easiest way for all involved.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2021 at 4:42 pm
OK, here's the "harch" execution plan:
<?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.5" Build="11.0.7493.4" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="42.1955" StatementText="Select * From (Select Top 1 Track, RequestID, ClientName, MatterName, CASE WHEN PATINDEX('% - Yellow', PhaseName) > 0 THEN REPLACE(PhaseName, ' - Yellow', '') ELSE PhaseName END ASPhaseName, MPApprovalStatusName, DateOpened, RequestorName, Notes, IsNewClient, AffiliatedClients From (Select Top 15 * From vwRequests As T1 Where ClientName LIKE '%harch%' Order by DateOpened DESC) As T2 Order by DateOpened ASC) As T3 Order by DateOpened DESC" StatementType="SELECT" QueryHash="0x6C016FAF6402EC89" QueryPlanHash="0xDD46E9E66CCC530C" RetrievedFromCache="false">
<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="1" MemoryGrant="15552" CachedPlanSize="216" CompileTime="164" CompileCPU="163" CompileMemory="2712">
<Warnings>
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(5),[r].[RequestorID],0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(14),[ELITE].[son_db].[dbo].[client].[clnum],0)" />
<PlanAffectingConvert ConvertIssue="Seek Plan" Expression="[r].[clnum]=CONVERT(varchar(14),[ELITE].[son_db].[dbo].[client].[clnum],0)" />
</Warnings>
<MemoryGrantInfo SerialRequiredMemory="1176" SerialDesiredMemory="15552" RequiredMemory="1176" DesiredMemory="15552" RequestedMemory="15552" GrantWaitTime="0" GrantedMemory="15552" MaxUsedMemory="5368" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104857" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" />
<TraceFlags IsCompileTime="true">
<TraceFlag Value="1906" Scope="Global" />
<TraceFlag Value="2861" Scope="Global" />
</TraceFlags>
<TraceFlags IsCompileTime="false">
<TraceFlag Value="1906" Scope="Global" />
<TraceFlag Value="2861" Scope="Global" />
</TraceFlags>
<QueryTimeStats CpuTime="3010977" ElapsedTime="3016352" />
<RelOp AvgRowSize="5480" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="42.1955">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1022" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1026" />
<ColumnReference Column="Expr1027" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1022" />
<ScalarOperator ScalarString="CASE WHEN [CMI].[dbo].[ConflictCheckRequest].[IsNewClient] as [r].[IsNewClient]=(1) THEN CASE WHEN [CMI].[dbo].[Client].[clname1] as [nc].[clname1] IS NOT NULL THEN [CMI].[dbo].[Client].[clname1] as [nc].[clname1] ELSE 'Pending User Input' END ELSE CONVERT(varchar(60),CASE WHEN [ELITE].[son_db].[dbo].[client].[clname1] as [ec].[clname1] IS NOT NULL THEN [ELITE].[son_db].[dbo].[client].[clname1] as [ec].[clname1] ELSE 'Existing Client' END,0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="'Pending User Input'" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Convert DataType="varchar" Length="60" Style="0" Implicit="false">
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="'Existing Client'" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="5501" EstimateCPU="0.000196922" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="TopN Sort" NodeId="1" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="42.1955">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1026" />
<ColumnReference Column="Expr1027" />
</OutputList>
<MemoryFractions Input="0.000556483" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TopSort Distinct="false" Rows="1">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="5501" EstimateCPU="1.5E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15" LogicalOp="Top" NodeId="2" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="42.1841">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1026" />
<ColumnReference Column="Expr1027" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(15)">
<Const ConstValue="(15)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="5501" EstimateCPU="0.144423" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15" LogicalOp="Filter" NodeId="3" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="42.1841">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1026" />
<ColumnReference Column="Expr1027" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Filter StartupExpression="false">
<RelOp AvgRowSize="5501" EstimateCPU="0.0058235" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="180.956" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="42.1836">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1026" />
<ColumnReference Column="Expr1027" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1026" />
<ScalarOperator ScalarString="isnull([CMI].[dbo].[Client].[AffiliatedClients] as [nc].[AffiliatedClients],N'')">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="AffiliatedClients" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="5501" EstimateCPU="0.243422" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="180.956" LogicalOp="Left Outer Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="42.1836">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="AffiliatedClients" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58236" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false" WithOrderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Column="Expr1056" />
</OuterReferences>
<RelOp AvgRowSize="4482" EstimateCPU="6920.74" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="180.956" LogicalOp="Left Outer Join" NodeId="7" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="41.65">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58236" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="[CMI].[dbo].[ConflictCheckRequest].[clnum] as [r].[clnum]=CONVERT(varchar(14),[ELITE].[son_db].[dbo].[client].[clnum] as [ec].[clnum],0)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Convert DataType="varchar" Length="14" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clnum" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="4464" EstimateCPU="0.0058235" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="180.956" LogicalOp="Compute Scalar" NodeId="8" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="7.76427">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1021" />
<ScalarOperator ScalarString="[CMI].[dbo].[User].[FirstName] as .[FirstName]+' '+[CMI].[dbo].[User].[LastName] as .[LastName]">
<Arithmetic Operation="ADD">
<ScalarOperator>
<Arithmetic Operation="ADD">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="FirstName" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="' '" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="LastName" />
</Identifier>
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="4465" EstimateCPU="0.0608556" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="180.956" LogicalOp="Left Outer Join" NodeId="9" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="7.72037">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="FirstName" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="LastName" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58236" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false" WithOrderedPrefetch="true">
<OuterReferences>
<ColumnReference Column="Expr1030" />
<ColumnReference Column="Expr1055" />
</OuterReferences>
<RelOp AvgRowSize="4426" EstimateCPU="0.0608556" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="180.956" LogicalOp="Left Outer Join" NodeId="11" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="7.56271">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
<ColumnReference Column="Expr1030" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58236" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
</OuterReferences>
<RelOp AvgRowSize="4411" EstimateCPU="0.0608556" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="180.956" LogicalOp="Left Outer Join" NodeId="12" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="4.83553">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
<ColumnReference Column="Expr1030" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58235" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
</OuterReferences>
<RelOp AvgRowSize="4411" EstimateCPU="0.00145588" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="180.956" LogicalOp="Compute Scalar" NodeId="13" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="2.1083">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
<ColumnReference Column="Expr1030" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1027" />
<ScalarOperator ScalarString="CASE WHEN patindex('% - Yellow',[CMI].[dbo].[RequestPhase].[PhaseName] as [p].[PhaseName])>(0) THEN replace([CMI].[dbo].[RequestPhase].[PhaseName] as [p].[PhaseName],' - Yellow','') ELSE [CMI].[dbo].[RequestPhase].[PhaseName] as [p].[PhaseName] END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Intrinsic FunctionName="patindex">
<ScalarOperator>
<Const ConstValue="'% - Yellow'" />
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseName" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Intrinsic FunctionName="replace">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseName" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="' - Yellow'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseName" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="427" EstimateCPU="0.608556" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="180.956" LogicalOp="Left Outer Join" NodeId="14" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="2.10829">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseName" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1030" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58235" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="[CMI].[dbo].[ConflictCheckRequest].[CurrentPhaseID] as [r].[CurrentPhaseID]=[CMI].[dbo].[RequestPhase].[PhaseID] as [p].[PhaseID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="CurrentPhaseID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="413" EstimateCPU="0.00145588" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="180.956" LogicalOp="Compute Scalar" NodeId="15" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="2.08647">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="CurrentPhaseID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1030" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1025" />
<ScalarOperator ScalarString="CASE WHEN [CMI].[dbo].[Matter].[mname] as [m].[mname] IS NOT NULL THEN [CMI].[dbo].[Matter].[mname] as [m].[mname] ELSE 'Pending User Input' END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Matter]" Alias="[m]" Column="mname" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Matter]" Alias="[m]" Column="mname" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="'Pending User Input'" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="413" EstimateCPU="0.0608556" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="180.956" LogicalOp="Left Outer Join" NodeId="16" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="2.08646">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="CurrentPhaseID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Matter]" Alias="[m]" Column="mname" />
<ColumnReference Column="Expr1030" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58235" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false" WithOrderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Column="Expr1054" />
</OuterReferences>
<RelOp AvgRowSize="161" EstimateCPU="0.00145588" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="180.956" LogicalOp="Compute Scalar" NodeId="18" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.51168">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="CurrentPhaseID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Column="Expr1030" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1030" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(5),[CMI].[dbo].[ConflictCheckRequest].[RequestorID] as [r].[RequestorID],0)">
<Convert DataType="nvarchar" Length="10" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestorID" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="161" EstimateCPU="1.05783" EstimateIO="0.00281532" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="180.956" LogicalOp="Sort" NodeId="19" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1.51168">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="CurrentPhaseID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestorID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
</OutputList>
<MemoryFractions Input="1" Output="0.994992" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="58235" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="false">
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="161" EstimateCPU="0.0160539" EstimateIO="0.434977" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58235" LogicalOp="Clustered Index Scan" NodeId="20" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.451031" TableCardinality="58235">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="CurrentPhaseID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestorID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58235" ActualRowsRead="58235" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="CurrentPhaseID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestorID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Index="[PK_ConflictCheckRequest]" Alias="[r]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</Sort>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="261" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="180.953" EstimateRewinds="1.10365E-07" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="24" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.574592" TableCardinality="58235">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Matter]" Alias="[m]" Column="mname" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58235" ActualRowsRead="58235" ActualEndOfScans="0" ActualExecutions="58235" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Matter]" Alias="[m]" Column="mname" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[Matter]" Index="[PK_Matter]" Alias="[m]" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Matter]" Alias="[m]" Column="RequestID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[CMI].[dbo].[ConflictCheckRequest].[RequestID] as [r].[RequestID]">
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="31" EstimateCPU="8.95E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="180.953" EstimatedExecutionMode="Row" EstimateRows="10" LogicalOp="Clustered Index Scan" NodeId="28" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0194883" TableCardinality="10">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="582350" ActualRowsRead="582350" ActualEndOfScans="58235" ActualExecutions="58235" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseName" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Index="[PK_RequestPhase]" Alias="[p]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="0.000100028" EstimateIO="0.0112613" EstimateRebinds="180.953" EstimateRewinds="0.00310734" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Distinct Sort" NodeId="33" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="2.72708">
<OutputList />
<MemoryFractions Input="0.00222593" Output="0.00222593" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="58235" ActualRewinds="0" ActualRows="51097" ActualEndOfScans="58235" ActualExecutions="58235" />
</RunTimeInformation>
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="28" EstimateCPU="0.0002717" EstimateIO="0" EstimateRebinds="180.953" EstimateRewinds="0.00310734" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="34" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.659832">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="51097" ActualEndOfScans="58235" ActualExecutions="58235" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="[CMI].[dbo].[RequestProgress].[TaskStatusID] as [rp].[TaskStatusID]=[CMI].[dbo].[TaskStatus].[StatusID] as [ts].[StatusID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskStatusID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="180.953" EstimateRewinds="0.00310734" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="35" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.576349" TableCardinality="693094">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskStatusID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="51097" ActualRowsRead="51097" ActualEndOfScans="58235" ActualExecutions="58235" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskStatusID" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Index="[PK_RequestProgress]" Alias="[rp]" TableReferenceId="1" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[CMI].[dbo].[ConflictCheckRequest].[RequestID] as [r].[RequestID]">
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="(5)">
<Const ConstValue="(5)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="28" EstimateCPU="0.00015" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="180.956" EstimatedExecutionMode="Row" EstimateRows="65" LogicalOp="Clustered Index Scan" NodeId="36" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0304969" TableCardinality="65">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3321305" ActualRowsRead="3321305" ActualEndOfScans="51097" ActualExecutions="51097" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Index="[PK_TaskStatus_1]" Alias="[ts]" TableReferenceId="1" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Sort>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="24" EstimateCPU="0.000100028" EstimateIO="0.0112613" EstimateRebinds="180.953" EstimateRewinds="0.00310734" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Distinct Sort" NodeId="38" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="2.72703">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</OutputList>
<MemoryFractions Input="0.00222593" Output="0.00222593" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="58235" ActualRewinds="0" ActualRows="57281" ActualEndOfScans="58235" ActualExecutions="58235" />
</RunTimeInformation>
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="28" EstimateCPU="0.0002717" EstimateIO="0" EstimateRebinds="180.953" EstimateRewinds="0.00310734" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="39" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.659776">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="57281" ActualEndOfScans="58235" ActualExecutions="58235" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="[CMI].[dbo].[RequestProgress].[TaskStatusID] as [rp].[TaskStatusID]=[CMI].[dbo].[TaskStatus].[StatusID] as [ts].[StatusID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskStatusID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="180.953" EstimateRewinds="0.00310734" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="40" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.576293" TableCardinality="693094">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskStatusID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="57281" ActualRowsRead="57281" ActualEndOfScans="58235" ActualExecutions="58235" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskStatusID" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Index="[PK_RequestProgress]" Alias="[rp]" TableReferenceId="2" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[CMI].[dbo].[ConflictCheckRequest].[RequestID] as [r].[RequestID]">
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="(7)">
<Const ConstValue="(7)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[CMI].[dbo].[ConflictCheckRequest].[RequestID] as [r].[RequestID]">
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="(13)">
<Const ConstValue="(13)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="28" EstimateCPU="0.00015" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="180.956" EstimatedExecutionMode="Row" EstimateRows="65" LogicalOp="Clustered Index Scan" NodeId="41" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0304969" TableCardinality="65">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="3723265" ActualRowsRead="3723265" ActualEndOfScans="57281" ActualExecutions="57281" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Index="[PK_TaskStatus_1]" Alias="[ts]" TableReferenceId="2" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Sort>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="55" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="180.745" EstimateRewinds="0.211299" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="43" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.157473" TableCardinality="1932">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="FirstName" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="LastName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58236" ActualRowsRead="58236" ActualEndOfScans="0" ActualExecutions="58236" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="FirstName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="LastName" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[User]" Index="[PK_User]" Alias="" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="EmployeeID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Expr1030]">
<Identifier>
<ColumnReference Column="Expr1030" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="50" EstimateCPU="0.00521778" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="180.953" EstimatedExecutionMode="Row" EstimateRows="28431" LogicalOp="Lazy Spool" NodeId="47" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="10.4464">
<OutputList>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clnum" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="58235" ActualRows="1655707716" ActualRowsRead="1655679285" ActualEndOfScans="58236" ActualExecutions="58236" />
</RunTimeInformation>
<Spool>
<RelOp AvgRowSize="50" EstimateCPU="9.487" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="28431" LogicalOp="Compute Scalar" NodeId="48" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="9.487">
<OutputList>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clnum" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ScalarOperator ScalarString="[ELITE].[son_db].[dbo].[client].[clname1] as [ec].[clname1]">
<Identifier>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clnum" />
<ScalarOperator ScalarString="[ELITE].[son_db].[dbo].[client].[clnum] as [ec].[clnum]">
<Identifier>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clnum" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="50" EstimateCPU="9.487" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="28431" LogicalOp="Remote Query" NodeId="49" Parallel="false" PhysicalOp="Remote Query" EstimatedTotalSubtreeCost="9.487">
<OutputList>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clnum" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="28431" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RemoteQuery RemoteSource="ELITE" RemoteQuery="SELECT "Tbl1017"."clname1" "Col1034","Tbl1017"."clnum" "Col1036" FROM "son_db"."dbo"."client" "Tbl1017"" />
</RelOp>
</ComputeScalar>
</RelOp>
</Spool>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="1032" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="180.953" EstimateRewinds="0.000117204" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="56" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.532898" TableCardinality="13993">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="AffiliatedClients" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="13499" ActualRowsRead="13499" ActualEndOfScans="44737" ActualExecutions="58236" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="AffiliatedClients" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[Client]" Index="[PK_ClientContact]" Alias="[nc]" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="cltid" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[CMI].[dbo].[ConflictCheckRequest].[cltid] as [r].[cltid]">
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [CMI].[dbo].[ConflictCheckRequest].[IsNewClient] as [r].[IsNewClient]=(1) THEN CASE WHEN [CMI].[dbo].[Client].[clname1] as [nc].[clname1] IS NOT NULL THEN [CMI].[dbo].[Client].[clname1] as [nc].[clname1] ELSE 'Pending User Input' END ELSE CONVERT(varchar(60),CASE WHEN [ELITE].[son_db].[dbo].[client].[clname1] as [ec].[clname1] IS NOT NULL THEN [ELITE].[son_db].[dbo].[client].[clname1] as [ec].[clname1] ELSE 'Existing Client' END,0) END like '%harch%'">
<Intrinsic FunctionName="like">
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="'Pending User Input'" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Convert DataType="varchar" Length="60" Style="0" Implicit="false">
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="'Existing Client'" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'%harch%'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</Top>
</RelOp>
</TopSort>
</RelOp>
</ComputeScalar>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
and the chameleon plan for comparison:
<?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.5" Build="11.0.7493.4" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="42.4678" StatementText="Select * From (Select Top 1 Track, RequestID, ClientName, MatterName, CASE WHEN PATINDEX('% - Yellow', PhaseName) > 0 THEN REPLACE(PhaseName, ' - Yellow', '') ELSE PhaseName END ASPhaseName, MPApprovalStatusName, DateOpened, RequestorName, Notes, IsNewClient, AffiliatedClients From (Select Top 15 * From vwRequests As T1 Where ClientName Like '%chameleon%' Order by DateOpened DESC) As T2 Order by DateOpened ASC) As T3 Order by DateOpened DESC" StatementType="SELECT" QueryHash="0x6C016FAF6402EC89" QueryPlanHash="0x58DDD1D8442E7EDC" RetrievedFromCache="false">
<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="1" MemoryGrant="205072" CachedPlanSize="424" CompileTime="179" CompileCPU="175" CompileMemory="2704">
<Warnings>
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(5),[r].[RequestorID],0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(14),[ELITE].[son_db].[dbo].[client].[clnum],0)" />
<PlanAffectingConvert ConvertIssue="Seek Plan" Expression="[r].[clnum]=CONVERT(varchar(14),[ELITE].[son_db].[dbo].[client].[clnum],0)" />
</Warnings>
<MemoryGrantInfo SerialRequiredMemory="6280" SerialDesiredMemory="205072" RequiredMemory="6280" DesiredMemory="205072" RequestedMemory="205072" GrantWaitTime="0" GrantedMemory="205072" MaxUsedMemory="15216" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104857" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" />
<TraceFlags IsCompileTime="true">
<TraceFlag Value="1906" Scope="Global" />
<TraceFlag Value="2861" Scope="Global" />
</TraceFlags>
<TraceFlags IsCompileTime="false">
<TraceFlag Value="1906" Scope="Global" />
<TraceFlag Value="2861" Scope="Global" />
</TraceFlags>
<QueryTimeStats CpuTime="2140" ElapsedTime="2146" />
<RelOp AvgRowSize="5480" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="42.4678">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1022" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1026" />
<ColumnReference Column="Expr1027" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1022" />
<ScalarOperator ScalarString="CASE WHEN [CMI].[dbo].[ConflictCheckRequest].[IsNewClient] as [r].[IsNewClient]=(1) THEN CASE WHEN [CMI].[dbo].[Client].[clname1] as [nc].[clname1] IS NOT NULL THEN [CMI].[dbo].[Client].[clname1] as [nc].[clname1] ELSE 'Pending User Input' END ELSE CONVERT(varchar(60),CASE WHEN [ELITE].[son_db].[dbo].[client].[clname1] as [ec].[clname1] IS NOT NULL THEN [ELITE].[son_db].[dbo].[client].[clname1] as [ec].[clname1] ELSE 'Existing Client' END,0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="'Pending User Input'" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Convert DataType="varchar" Length="60" Style="0" Implicit="false">
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="'Existing Client'" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="5501" EstimateCPU="0.000196922" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="TopN Sort" NodeId="1" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="42.4678">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1026" />
<ColumnReference Column="Expr1027" />
</OutputList>
<MemoryFractions Input="0.5" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TopSort Distinct="false" Rows="1">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="5501" EstimateCPU="0.047753" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15" LogicalOp="TopN Sort" NodeId="2" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="42.4563">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1026" />
<ColumnReference Column="Expr1027" />
</OutputList>
<MemoryFractions Input="4.09316E-05" Output="0.5" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TopSort Distinct="false" Rows="15">
<OrderBy>
<OrderByColumn Ascending="false">
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="5501" EstimateCPU="0.144423" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2681.82" LogicalOp="Filter" NodeId="3" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="42.3973">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1026" />
<ColumnReference Column="Expr1027" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Filter StartupExpression="false">
<RelOp AvgRowSize="5501" EstimateCPU="0.0058235" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58235" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="42.2529">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1026" />
<ColumnReference Column="Expr1027" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1026" />
<ScalarOperator ScalarString="isnull([CMI].[dbo].[Client].[AffiliatedClients] as [nc].[AffiliatedClients],N'')">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="AffiliatedClients" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="5501" EstimateCPU="10.3154" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58235" LogicalOp="Right Outer Join" NodeId="5" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="42.247">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="AffiliatedClients" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
</OutputList>
<MemoryFractions Input="0.438247" Output="0.445745" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58236" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="cltid" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[CMI].[dbo].[ConflictCheckRequest].[cltid] as [r].[cltid]=[CMI].[dbo].[Client].[cltid] as [nc].[cltid]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="cltid" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="1036" EstimateCPU="0.0155493" EstimateIO="0.574236" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="13993" LogicalOp="Clustered Index Scan" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.589785" TableCardinality="13993">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="AffiliatedClients" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="13993" ActualRowsRead="13993" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="cltid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="AffiliatedClients" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[Client]" Index="[PK_ClientContact]" Alias="[nc]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
<RelOp AvgRowSize="4482" EstimateCPU="1.5453" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58235" LogicalOp="Right Outer Join" NodeId="7" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="31.3419">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
</OutputList>
<MemoryFractions Input="0.151555" Output="0.154148" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58236" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Column="Expr1043" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[CMI].[dbo].[ConflictCheckRequest].[clnum] as [r].[clnum]=[Expr1043]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1043" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="59" EstimateCPU="0.0028431" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="28431" LogicalOp="Compute Scalar" NodeId="8" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="9.48984">
<OutputList>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Column="Expr1043" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1043" />
<ScalarOperator ScalarString="CONVERT(varchar(14),[ELITE].[son_db].[dbo].[client].[clnum] as [ec].[clnum],0)">
<Convert DataType="varchar" Length="14" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clnum" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="50" EstimateCPU="9.487" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="28431" LogicalOp="Compute Scalar" NodeId="9" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="9.487">
<OutputList>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clnum" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ScalarOperator ScalarString="[ELITE].[son_db].[dbo].[client].[clname1] as [ec].[clname1]">
<Identifier>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clnum" />
<ScalarOperator ScalarString="[ELITE].[son_db].[dbo].[client].[clnum] as [ec].[clnum]">
<Identifier>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clnum" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="50" EstimateCPU="9.487" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="28431" LogicalOp="Remote Query" NodeId="10" Parallel="false" PhysicalOp="Remote Query" EstimatedTotalSubtreeCost="9.487">
<OutputList>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clnum" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="28431" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RemoteQuery RemoteSource="ELITE" RemoteQuery="SELECT "Tbl1017"."clname1" "Col1034","Tbl1017"."clnum" "Col1036" FROM "son_db"."dbo"."client" "Tbl1017"" />
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="4464" EstimateCPU="0.0058235" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58235" LogicalOp="Compute Scalar" NodeId="19" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="20.3067">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1021" />
<ScalarOperator ScalarString="[CMI].[dbo].[User].[FirstName] as .[FirstName]+' '+[CMI].[dbo].[User].[LastName] as .[LastName]">
<Arithmetic Operation="ADD">
<ScalarOperator>
<Arithmetic Operation="ADD">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="FirstName" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="' '" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="LastName" />
</Identifier>
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="4465" EstimateCPU="0.551259" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58235" LogicalOp="Right Outer Join" NodeId="20" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="20.3009">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="FirstName" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="LastName" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
</OutputList>
<MemoryFractions Input="0.0310274" Output="0.0315583" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58236" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="EmployeeID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Column="Expr1030" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Expr1030]=[CMI].[dbo].[User].[EmployeeID] as .[EmployeeID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1030" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="EmployeeID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="66" EstimateCPU="0.0022822" EstimateIO="0.0334954" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1932" LogicalOp="Clustered Index Scan" NodeId="21" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0357776" TableCardinality="1932">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="EmployeeID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="FirstName" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="LastName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1932" ActualRowsRead="1932" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="EmployeeID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="FirstName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[User]" Alias="" Column="LastName" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[User]" Index="[PK_User]" Alias="" IndexKind="Clustered" />
</IndexScan>
</RelOp>
<RelOp AvgRowSize="4426" EstimateCPU="2.28844" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58235" LogicalOp="Right Outer Join" NodeId="22" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="19.7139">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
<ColumnReference Column="Expr1030" />
</OutputList>
<MemoryFractions Input="0.200813" Output="0.204249" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58236" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
</HashKeysProbe>
<RelOp AvgRowSize="28" EstimateCPU="3.67218" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="57316.7" LogicalOp="Aggregate" NodeId="23" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="7.21873">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</OutputList>
<MemoryFractions Input="0.368321" Output="0.178357" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="57281" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</HashKeysBuild>
<BuildResidual>
<ScalarOperator ScalarString="[CMI].[dbo].[RequestProgress].[RequestID] as [rp].[RequestID] = [CMI].[dbo].[RequestProgress].[RequestID] as [rp].[RequestID] AND [CMI].[dbo].[TaskStatus].[StatusName] as [ts].[StatusName] = [CMI].[dbo].[TaskStatus].[StatusName] as [ts].[StatusName] AND [CMI].[dbo].[TaskStatus].[StatusID] as [ts].[StatusID] = [CMI].[dbo].[TaskStatus].[StatusID] as [ts].[StatusID]">
<Logical Operation="AND">
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</BuildResidual>
<RelOp AvgRowSize="32" EstimateCPU="0.326101" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="57316.7" LogicalOp="Right Outer Join" NodeId="24" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="3.54655">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</OutputList>
<MemoryFractions Input="0.37917" Output="0.0108489" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="57281" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskStatusID" />
</HashKeysProbe>
<RelOp AvgRowSize="28" EstimateCPU="0.0002285" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="65" LogicalOp="Clustered Index Scan" NodeId="25" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0033535" TableCardinality="65">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="65" ActualRowsRead="65" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Index="[PK_TaskStatus_1]" Alias="[ts]" TableReferenceId="2" IndexKind="Clustered" />
</IndexScan>
</RelOp>
<RelOp AvgRowSize="19" EstimateCPU="0.76256" EstimateIO="1.84461" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="57316.7" LogicalOp="Clustered Index Scan" NodeId="26" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="2.60717" TableCardinality="693094">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskStatusID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="57281" ActualRowsRead="693094" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskStatusID" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Index="[PK_RequestProgress]" Alias="[rp]" TableReferenceId="2" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[CMI].[dbo].[RequestProgress].[TaskID] as [rp].[TaskID]=(7) OR [CMI].[dbo].[RequestProgress].[TaskID] as [rp].[TaskID]=(13)">
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(7)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(13)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="4411" EstimateCPU="0.827735" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58235" LogicalOp="Right Outer Join" NodeId="29" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="10.2067">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
<ColumnReference Column="Expr1030" />
</OutputList>
<MemoryFractions Input="0.160047" Output="0.162785" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58235" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
</HashKeysProbe>
<RelOp AvgRowSize="11" EstimateCPU="3.28056" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="51174.5" LogicalOp="Aggregate" NodeId="30" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="6.51707">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
</OutputList>
<MemoryFractions Input="0.170847" Output="0.0183106" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="51097" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</HashKeysBuild>
<BuildResidual>
<ScalarOperator ScalarString="[CMI].[dbo].[RequestProgress].[RequestID] as [rp].[RequestID] = [CMI].[dbo].[RequestProgress].[RequestID] as [rp].[RequestID] AND [CMI].[dbo].[TaskStatus].[StatusName] as [ts].[StatusName] = [CMI].[dbo].[TaskStatus].[StatusName] as [ts].[StatusName] AND [CMI].[dbo].[TaskStatus].[StatusID] as [ts].[StatusID] = [CMI].[dbo].[TaskStatus].[StatusID] as [ts].[StatusID]">
<Logical Operation="AND">
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</BuildResidual>
<RelOp AvgRowSize="32" EstimateCPU="0.293295" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="51174.5" LogicalOp="Right Outer Join" NodeId="31" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="3.2365">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</OutputList>
<MemoryFractions Input="0.178357" Output="0.00750978" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="51097" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskStatusID" />
</HashKeysProbe>
<RelOp AvgRowSize="28" EstimateCPU="0.0002285" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="65" LogicalOp="Clustered Index Scan" NodeId="32" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0033535" TableCardinality="65">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="65" ActualRowsRead="65" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Alias="[ts]" Column="StatusName" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[TaskStatus]" Index="[PK_TaskStatus_1]" Alias="[ts]" TableReferenceId="1" IndexKind="Clustered" />
</IndexScan>
</RelOp>
<RelOp AvgRowSize="19" EstimateCPU="0.76256" EstimateIO="1.84461" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="51174.5" LogicalOp="Clustered Index Scan" NodeId="33" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="2.60717" TableCardinality="693094">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskStatusID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="51097" ActualRowsRead="693094" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="RequestID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskStatusID" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Index="[PK_RequestProgress]" Alias="[rp]" TableReferenceId="1" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[CMI].[dbo].[RequestProgress].[TaskID] as [rp].[TaskID]=(5)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestProgress]" Alias="[rp]" Column="TaskID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(5)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="4411" EstimateCPU="0.0058235" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58235" LogicalOp="Compute Scalar" NodeId="36" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="2.8619">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
<ColumnReference Column="Expr1030" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1027" />
<ScalarOperator ScalarString="CASE WHEN patindex('% - Yellow',[CMI].[dbo].[RequestPhase].[PhaseName] as [p].[PhaseName])>(0) THEN replace([CMI].[dbo].[RequestPhase].[PhaseName] as [p].[PhaseName],' - Yellow','') ELSE [CMI].[dbo].[RequestPhase].[PhaseName] as [p].[PhaseName] END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Intrinsic FunctionName="patindex">
<ScalarOperator>
<Const ConstValue="'% - Yellow'" />
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseName" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Intrinsic FunctionName="replace">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseName" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="' - Yellow'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseName" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="427" EstimateCPU="0.328956" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58235" LogicalOp="Right Outer Join" NodeId="37" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="2.85607">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseName" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1030" />
</OutputList>
<MemoryFractions Input="0.0183106" Output="0.00147354" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58235" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="CurrentPhaseID" />
</HashKeysProbe>
<RelOp AvgRowSize="31" EstimateCPU="0.000168" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="10" LogicalOp="Clustered Index Scan" NodeId="38" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.003293" TableCardinality="10">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="10" ActualRowsRead="10" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Alias="[p]" Column="PhaseName" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[RequestPhase]" Index="[PK_RequestPhase]" Alias="[p]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
<RelOp AvgRowSize="413" EstimateCPU="0.0058235" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58235" LogicalOp="Compute Scalar" NodeId="39" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="2.52382">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="CurrentPhaseID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1030" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1025" />
<ScalarOperator ScalarString="CASE WHEN [CMI].[dbo].[Matter].[mname] as [m].[mname] IS NOT NULL THEN [CMI].[dbo].[Matter].[mname] as [m].[mname] ELSE 'Pending User Input' END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Matter]" Alias="[m]" Column="mname" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Matter]" Alias="[m]" Column="mname" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="'Pending User Input'" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="413" EstimateCPU="0.256011" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58235" LogicalOp="Left Outer Join" NodeId="40" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="2.518">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="CurrentPhaseID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Matter]" Alias="[m]" Column="mname" />
<ColumnReference Column="Expr1030" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58235" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Merge ManyToMany="false">
<InnerSideJoinColumns>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Matter]" Alias="[m]" Column="RequestID" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[CMI].[dbo].[ConflictCheckRequest].[RequestID] as [r].[RequestID]=[CMI].[dbo].[Matter].[RequestID] as [m].[RequestID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Matter]" Alias="[m]" Column="RequestID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="161" EstimateCPU="0.0058235" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58235" LogicalOp="Compute Scalar" NodeId="41" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.505016">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="CurrentPhaseID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
<ColumnReference Column="Expr1030" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1030" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(5),[CMI].[dbo].[ConflictCheckRequest].[RequestorID] as [r].[RequestorID],0)">
<Convert DataType="nvarchar" Length="10" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestorID" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="161" EstimateCPU="0.0642155" EstimateIO="0.434977" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58235" LogicalOp="Clustered Index Scan" NodeId="42" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.499192" TableCardinality="58235">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="CurrentPhaseID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestorID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58235" ActualRowsRead="58235" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="CurrentPhaseID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="DateOpened" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="cltid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="clnum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="RequestorID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Notes" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="Track" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Index="[PK_ConflictCheckRequest]" Alias="[r]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="265" EstimateCPU="0.0642155" EstimateIO="1.69275" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="58235" LogicalOp="Clustered Index Scan" NodeId="46" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1.75697" TableCardinality="58235">
<OutputList>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Matter]" Alias="[m]" Column="RequestID" />
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Matter]" Alias="[m]" Column="mname" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="58235" ActualRowsRead="58235" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Matter]" Alias="[m]" Column="RequestID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Matter]" Alias="[m]" Column="mname" />
</DefinedValue>
</DefinedValues>
<Object Database="[CMI]" Schema="[dbo]" Table="[Matter]" Index="[PK_Matter]" Alias="[m]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</Merge>
</RelOp>
</ComputeScalar>
</RelOp>
</Hash>
</RelOp>
</ComputeScalar>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
</ComputeScalar>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
</ComputeScalar>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [CMI].[dbo].[ConflictCheckRequest].[IsNewClient] as [r].[IsNewClient]=(1) THEN CASE WHEN [CMI].[dbo].[Client].[clname1] as [nc].[clname1] IS NOT NULL THEN [CMI].[dbo].[Client].[clname1] as [nc].[clname1] ELSE 'Pending User Input' END ELSE CONVERT(varchar(60),CASE WHEN [ELITE].[son_db].[dbo].[client].[clname1] as [ec].[clname1] IS NOT NULL THEN [ELITE].[son_db].[dbo].[client].[clname1] as [ec].[clname1] ELSE 'Existing Client' END,0) END like '%chameleon%'">
<Intrinsic FunctionName="like">
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[ConflictCheckRequest]" Alias="[r]" Column="IsNewClient" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CMI]" Schema="[dbo]" Table="[Client]" Alias="[nc]" Column="clname1" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="'Pending User Input'" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Convert DataType="varchar" Length="60" Style="0" Implicit="false">
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Server="[ELITE]" Database="[son_db]" Schema="[dbo]" Table="[client]" Alias="[ec]" Column="clname1" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="'Existing Client'" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'%chameleon%'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</TopSort>
</RelOp>
</TopSort>
</RelOp>
</ComputeScalar>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply