backend
Starting Member
8 Posts |
Posted - 2013-05-01 : 20:21:38
|
Need help analyzing the following exec plan.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table '#0E82FF5F'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- ---------- -------- ---------------------------------------------------------------- -------- ------------------1 1 INSERT @VisitTypeTable VALUES (LEFT(@VisitType, @Pointer3 - 1)) 1 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.01000216 NULL NULL INSERT 0 NULL1 1 |--Table Insert(OBJECT:(@VisitTypeTable), SET:([VisitType] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(30),substring([@VisitType],(1),CONVERT_IMPLICIT(bigint,[@Pointer3]-(1),0)),0))) 1 2 1 Table Insert Insert OBJECT:(@VisitTypeTable), SET:([VisitType] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(30),substring([@VisitType],(1),CONVERT_IMPLICIT(bigint,[@Pointer3]-(1),0)),0)) [Expr1004]=CONVERT_IMPLICIT(varchar(30),substring([@VisitType],(1),CONVERT_IMPLICIT(bigint,[@Pointer3]-(1),0)),0) 1 0.01 1E-06 9 0.01000216 NULL NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 44 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table '#0E82FF5F'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- ---------- -------- ---------------------------------------------------------------- -------- ------------------1 1 INSERT @VisitTypeTable VALUES (LEFT(@VisitType, @Pointer3 - 1)) 2 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.01000216 NULL NULL INSERT 0 NULL1 1 |--Table Insert(OBJECT:(@VisitTypeTable), SET:([VisitType] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(30),substring([@VisitType],(1),CONVERT_IMPLICIT(bigint,[@Pointer3]-(1),0)),0))) 2 2 1 Table Insert Insert OBJECT:(@VisitTypeTable), SET:([VisitType] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(30),substring([@VisitType],(1),CONVERT_IMPLICIT(bigint,[@Pointer3]-(1),0)),0)) [Expr1004]=CONVERT_IMPLICIT(varchar(30),substring([@VisitType],(1),CONVERT_IMPLICIT(bigint,[@Pointer3]-(1),0)),0) 1 0.01 1E-06 9 0.01000216 NULL NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 80 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table '#0E82FF5F'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------- -------- ---------------------------------------------------------------- -------- ------------------1 1 INSERT @VisitTypeTable VALUES (@VisitType) 3 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.01000216 NULL NULL INSERT 0 NULL1 1 |--Table Insert(OBJECT:(@VisitTypeTable), SET:([VisitType] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(30),[@VisitType],0))) 3 2 1 Table Insert Insert OBJECT:(@VisitTypeTable), SET:([VisitType] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(30),[@VisitType],0)) [Expr1004]=CONVERT_IMPLICIT(varchar(30),[@VisitType],0) 1 0.01 1E-06 9 0.01000216 NULL NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 46 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table '#0F772398'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------- -------- ---------------------------------------------------------------- -------- ------------------1 1 INSERT @VisitTypeCareLevelTable VALUES (LEFT(@VisitTypeCareLevel, @Pointer4 - 1)) 4 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.01000216 NULL NULL INSERT 0 NULL1 1 |--Table Insert(OBJECT:(@VisitTypeCareLevelTable), SET:([VisitTypeCareLevel] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(100),substring([@VisitTypeCareLevel],(1),CONVERT_IMPLICIT(bigint,[@Pointer4]-(1),0)),0))) 4 2 1 Table Insert Insert OBJECT:(@VisitTypeCareLevelTable), SET:([VisitTypeCareLevel] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(100),substring([@VisitTypeCareLevel],(1),CONVERT_IMPLICIT(bigint,[@Pointer4]-(1),0)),0)) [Expr1004]=CONVERT_IMPLICIT(varchar(100),substring([@VisitTypeCareLevel],(1),CONVERT_IMPLICIT(bigint,[@Pointer4]-(1),0)),0) 1 0.01 1E-06 9 0.01000216 NULL NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 61 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table '#0F772398'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------- -------- ---------------------------------------------------------------- -------- ------------------1 1 INSERT @VisitTypeCareLevelTable VALUES (LEFT(@VisitTypeCareLevel, @Pointer4 - 1)) 5 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.01000216 NULL NULL INSERT 0 NULL1 1 |--Table Insert(OBJECT:(@VisitTypeCareLevelTable), SET:([VisitTypeCareLevel] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(100),substring([@VisitTypeCareLevel],(1),CONVERT_IMPLICIT(bigint,[@Pointer4]-(1),0)),0))) 5 2 1 Table Insert Insert OBJECT:(@VisitTypeCareLevelTable), SET:([VisitTypeCareLevel] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(100),substring([@VisitTypeCareLevel],(1),CONVERT_IMPLICIT(bigint,[@Pointer4]-(1),0)),0)) [Expr1004]=CONVERT_IMPLICIT(varchar(100),substring([@VisitTypeCareLevel],(1),CONVERT_IMPLICIT(bigint,[@Pointer4]-(1),0)),0) 1 0.01 1E-06 9 0.01000216 NULL NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 105 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table '#0F772398'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------- -------- ---------------------------------------------------------------- -------- ------------------1 1 INSERT @VisitTypeCareLevelTable VALUES (LEFT(@VisitTypeCareLevel, @Pointer4 - 1)) 6 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.01000216 NULL NULL INSERT 0 NULL1 1 |--Table Insert(OBJECT:(@VisitTypeCareLevelTable), SET:([VisitTypeCareLevel] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(100),substring([@VisitTypeCareLevel],(1),CONVERT_IMPLICIT(bigint,[@Pointer4]-(1),0)),0))) 6 2 1 Table Insert Insert OBJECT:(@VisitTypeCareLevelTable), SET:([VisitTypeCareLevel] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(100),substring([@VisitTypeCareLevel],(1),CONVERT_IMPLICIT(bigint,[@Pointer4]-(1),0)),0)) [Expr1004]=CONVERT_IMPLICIT(varchar(100),substring([@VisitTypeCareLevel],(1),CONVERT_IMPLICIT(bigint,[@Pointer4]-(1),0)),0) 1 0.01 1E-06 9 0.01000216 NULL NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 61 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table '#0F772398'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------- -------- ---------------------------------------------------------------- -------- ------------------1 1 INSERT @VisitTypeCareLevelTable VALUES (LEFT(@VisitTypeCareLevel, @Pointer4 - 1)) 7 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.01000216 NULL NULL INSERT 0 NULL1 1 |--Table Insert(OBJECT:(@VisitTypeCareLevelTable), SET:([VisitTypeCareLevel] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(100),substring([@VisitTypeCareLevel],(1),CONVERT_IMPLICIT(bigint,[@Pointer4]-(1),0)),0))) 7 2 1 Table Insert Insert OBJECT:(@VisitTypeCareLevelTable), SET:([VisitTypeCareLevel] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(100),substring([@VisitTypeCareLevel],(1),CONVERT_IMPLICIT(bigint,[@Pointer4]-(1),0)),0)) [Expr1004]=CONVERT_IMPLICIT(varchar(100),substring([@VisitTypeCareLevel],(1),CONVERT_IMPLICIT(bigint,[@Pointer4]-(1),0)),0) 1 0.01 1E-06 9 0.01000216 NULL NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 49 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table '#0F772398'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------- -------- ---------------------------------------------------------------- -------- ------------------1 1 INSERT @VisitTypeCareLevelTable VALUES (LEFT(@VisitTypeCareLevel, @Pointer4 - 1)) 8 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.01000216 NULL NULL INSERT 0 NULL1 1 |--Table Insert(OBJECT:(@VisitTypeCareLevelTable), SET:([VisitTypeCareLevel] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(100),substring([@VisitTypeCareLevel],(1),CONVERT_IMPLICIT(bigint,[@Pointer4]-(1),0)),0))) 8 2 1 Table Insert Insert OBJECT:(@VisitTypeCareLevelTable), SET:([VisitTypeCareLevel] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(100),substring([@VisitTypeCareLevel],(1),CONVERT_IMPLICIT(bigint,[@Pointer4]-(1),0)),0)) [Expr1004]=CONVERT_IMPLICIT(varchar(100),substring([@VisitTypeCareLevel],(1),CONVERT_IMPLICIT(bigint,[@Pointer4]-(1),0)),0) 1 0.01 1E-06 9 0.01000216 NULL NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 53 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table '#0F772398'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- ---------- -------- ---------------------------------------------------------------- -------- ------------------1 1 INSERT @VisitTypeCareLevelTable VALUES (@VisitTypeCareLevel) 9 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.01000216 NULL NULL INSERT 0 NULL1 1 |--Table Insert(OBJECT:(@VisitTypeCareLevelTable), SET:([VisitTypeCareLevel] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(100),[@VisitTypeCareLevel],0))) 9 2 1 Table Insert Insert OBJECT:(@VisitTypeCareLevelTable), SET:([VisitTypeCareLevel] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(100),[@VisitTypeCareLevel],0)) [Expr1004]=CONVERT_IMPLICIT(varchar(100),[@VisitTypeCareLevel],0) 1 0.01 1E-06 9 0.01000216 NULL NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table '#0BA692B4'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------- -------- ---------------------------------------------------------------- -------- ------------------1 1 INSERT @SelectedLocation VALUES (@HVCLocation) 10 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.01000216 NULL NULL INSERT 0 NULL1 1 |--Table Insert(OBJECT:(@SelectedLocation), SET:([Unit] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(255),[@HVCLocation],0))) 10 2 1 Table Insert Insert OBJECT:(@SelectedLocation), SET:([Unit] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(varchar(255),[@HVCLocation],0)) [Expr1004]=CONVERT_IMPLICIT(varchar(255),[@HVCLocation],0) 1 0.01 1E-06 9 0.01000216 NULL NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table '#0BA692B4'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ -------------------------------------------------------------------- ----------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------- -------- ---------------------------------------------------------------- -------- ------------------1 1 IF (SELECT count(Unit) from @SelectedLocation) >=1 -- there could be several selections of form '[Facility Name] /<ALL>' 11 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.0032843 NULL NULL GeneralQuery 0 NULL0 0 |--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [Expr1004]>=(1) THEN (1) ELSE (0) END)) 11 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1006]=CASE WHEN [Expr1004]>=(1) THEN (1) ELSE (0) END) [Expr1006]=CASE WHEN [Expr1004]>=(1) THEN (1) ELSE (0) END 1 0 1E-07 11 0.0032843 [Expr1006] NULL PLAN_ROW 0 10 0 |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1011],0))) 11 3 2 Compute Scalar Compute Scalar DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1011],0)) [Expr1004]=CONVERT_IMPLICIT(int,[Expr1011],0) 1 0 1.1E-06 11 0.0032842 [Expr1004] NULL PLAN_ROW 0 11 1 |--Stream Aggregate(DEFINE:([Expr1011]=COUNT([Unit]))) 11 4 3 Stream Aggregate Aggregate NULL [Expr1011]=COUNT([Unit]) 1 0 1.1E-06 11 0.0032842 [Expr1011] NULL PLAN_ROW 0 11 1 |--Table Scan(OBJECT:(@SelectedLocation)) 11 5 4 Table Scan Table Scan OBJECT:(@SelectedLocation) [Unit] 1 0.003125 0.0001581 138 0.0032831 [Unit] NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 51 ms.Table '#0BA692B4'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- -------------------------- -------- ---------------------------------------------------------------- -------- ------------------1 1 IF (SELECT TOP 1 SelectedLocation.unit FROM @SelectedLocation SelectedLocation ) = '<ALL>' -- All Locations in all facilities 12 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.003288637 NULL NULL GeneralQuery 0 NULL0 0 |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN @SelectedLocation.[Unit] as [SelectedLocation].[Unit]='<ALL>' THEN (1) ELSE (0) END)) 12 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1004]=CASE WHEN @SelectedLocation.[Unit] as [SelectedLocation].[Unit]='<ALL>' THEN (1) ELSE (0) END) [Expr1004]=CASE WHEN @SelectedLocation.[Unit] as [SelectedLocation].[Unit]='<ALL>' THEN (1) ELSE (0) END 1 0 1E-07 11 0.003288637 [Expr1004] NULL PLAN_ROW 0 11 1 |--Nested Loops(Left Outer Join) 12 3 2 Nested Loops Left Outer Join NULL NULL 1 0 4.18E-06 138 0.003288537 [SelectedLocation].[Unit] NULL PLAN_ROW 0 11 1 |--Constant Scan 12 4 3 Constant Scan Constant Scan NULL NULL 1 0 1.157E-06 9 1.157E-06 NULL NULL PLAN_ROW 0 11 1 |--Top(TOP EXPRESSION:((1))) 12 5 3 Top Top TOP EXPRESSION:((1)) NULL 1 0 1E-07 138 0.0032832 [SelectedLocation].[Unit] NULL PLAN_ROW 0 11 1 |--Table Scan(OBJECT:(@SelectedLocation AS [SelectedLocation])) 12 6 5 Table Scan Table Scan OBJECT:(@SelectedLocation AS [SelectedLocation]) [SelectedLocation].[Unit] 1 0.003125 0.0001581 138 0.0032831 [SelectedLocation].[Unit] NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 115 ms.Table '#0D8EDB26'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'CV3Location'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------0 1 INSERT INTO @LocationGUID SELECT CV3Location.GUID ,Facility.Name ,CV3Location.Name FROM CV3Location INNER JOIN CV3Location Facility --CI GUID ON CV3L 13 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.01986072 NULL NULL INSERT 0 NULL0 1 |--Table Insert(OBJECT:(@LocationGUID), SET:([LocationGuid] = [TESTSP11].[dbo].[CV3Location].[GUID],[FacilityName] = [Expr1013],[UnitName] = [Expr1014])) 13 2 1 Table Insert Insert OBJECT:(@LocationGUID), SET:([LocationGuid] = [TESTSP11].[dbo].[CV3Location].[GUID],[FacilityName] = [Expr1013],[UnitName] = [Expr1014]) NULL 1 0.01 1E-06 9 0.01986072 NULL NULL PLAN_ROW 0 10 1 |--Top(ROWCOUNT est 0) 13 3 2 Top Top TOP EXPRESSION:((0)) NULL 1 0 1E-07 24 0.00985972 [TESTSP11].[dbo].[CV3Location].[GUID], [Expr1013], [Expr1014] NULL PLAN_ROW 0 10 1 |--Nested Loops(Left Semi Join, WHERE:([TESTSP11].[dbo].[CV3Location].[Name] as [Facility].[Name]=[Expr1015])) 13 4 3 Nested Loops Left Semi Join WHERE:([TESTSP11].[dbo].[CV3Location].[Name] as [Facility].[Name]=[Expr1015]) NULL 1 0 4.18E-06 24 0.00985962 [TESTSP11].[dbo].[CV3Location].[GUID], [Expr1013], [Expr1014] NULL PLAN_ROW 0 10 0 |--Compute Scalar(DEFINE:([Expr1013]=CONVERT_IMPLICIT(varchar(40),[TESTSP11].[dbo].[CV3Location].[Name] as [Facility].[Name],0))) 13 5 4 Compute Scalar Compute Scalar DEFINE:([Expr1013]=CONVERT_IMPLICIT(varchar(40),[TESTSP11].[dbo].[CV3Location].[Name] as [Facility].[Name],0)) [Expr1013]=CONVERT_IMPLICIT(varchar(40),[TESTSP11].[dbo].[CV3Location].[Name] as [Facility].[Name],0) 1 0 1E-07 27 0.00657194 [TESTSP11].[dbo].[CV3Location].[GUID], [Facility].[Name], [Expr1013], [Expr1014] NULL PLAN_ROW 0 10 1 | |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTSP11].[dbo].[CV3Location].[FacilityGUID])) 13 6 5 Nested Loops Inner Join OUTER REFERENCES:([TESTSP11].[dbo].[CV3Location].[FacilityGUID]) NULL 1 0 4.18E-06 24 0.00657184 [TESTSP11].[dbo].[CV3Location].[GUID], [Facility].[Name], [Expr1014] NULL PLAN_ROW 0 10 0 | |--Compute Scalar(DEFINE:([Expr1014]=CONVERT_IMPLICIT(varchar(40),[TESTSP11].[dbo].[CV3Location].[Name],0))) 13 7 6 Compute Scalar Compute Scalar DEFINE:([Expr1014]=CONVERT_IMPLICIT(varchar(40),[TESTSP11].[dbo].[CV3Location].[Name],0)) [Expr1014]=CONVERT_IMPLICIT(varchar(40),[TESTSP11].[dbo].[CV3Location].[Name],0) 1 0 1E-07 31 0.0032832 [TESTSP11].[dbo].[CV3Location].[GUID], [TESTSP11].[dbo].[CV3Location].[FacilityGUID], [Expr1014] NULL PLAN_ROW 0 10 1 | | |--Clustered Index Scan(OBJECT:([TESTSP11].[dbo].[CV3Location].[CV3LocationPK]), WHERE:([TESTSP11].[dbo].[CV3Location].[Active]=(1) AND [TESTSP11].[dbo].[CV3Location].[IsLocationGroup]=(1))) 13 8 7 Clustered Index Scan Clustered Index Scan OBJECT:([TESTSP11].[dbo].[CV3Location].[CV3LocationPK]), WHERE:([TESTSP11].[dbo].[CV3Location].[Active]=(1) AND [TESTSP11].[dbo].[CV3Location].[IsLocationGroup]=(1)) [TESTSP11].[dbo].[CV3Location].[GUID], [TESTSP11].[dbo].[CV3Location].[Name], [TESTSP11].[dbo].[CV3Location].[FacilityGUID] 1 0.003125 0.0001581 31 0.0032831 [TESTSP11].[dbo].[CV3Location].[GUID], [TESTSP11].[dbo].[CV3Location].[Name], [TESTSP11].[dbo].[CV3Location].[FacilityGUID] NULL PLAN_ROW 0 10 0 | |--Clustered Index Seek(OBJECT:([TESTSP11].[dbo].[CV3Location].[CV3LocationPK] AS [Facility]), SEEK:([Facility].[GUID]=[TESTSP11].[dbo].[CV3Location].[FacilityGUID]), WHERE:([TESTSP11].[dbo].[CV3Location].[IsFacility] as [Facili 13 12 6 Clustered Index Seek Clustered Index Seek OBJECT:([TESTSP11].[dbo].[CV3Location].[CV3LocationPK] AS [Facility]), SEEK:([Facility].[GUID]=[TESTSP11].[dbo].[CV3Location].[FacilityGUID]), WHERE:([TESTSP11].[dbo].[CV3Location].[IsFacility] as [Facility].[IsFacility]=(1)) ORDERED FORWARD [Facility].[Name] 1 0.003125 0.0001581 13 0.0032831 [Facility].[Name] NULL PLAN_ROW 0 10 0 |--Compute Scalar(DEFINE:([Expr1015]=substring(@SelectedLocation.[Unit] as [LocTable].[Unit],(1),charindex(' / <ALL>',@SelectedLocation.[Unit] as [LocTable].[Unit])))) 13 16 4 Compute Scalar Compute Scalar DEFINE:([Expr1015]=substring(@SelectedLocation.[Unit] as [LocTable].[Unit],(1),charindex(' / <ALL>',@SelectedLocation.[Unit] as [LocTable].[Unit]))) [Expr1015]=substring(@SelectedLocation.[Unit] as [LocTable].[Unit],(1),charindex(' / <ALL>',@SelectedLocation.[Unit] as [LocTable].[Unit])) 1 0 1E-07 138 0.0032832 [Expr1015] NULL PLAN_ROW 0 10 0 |--Table Scan(OBJECT:(@SelectedLocation AS [LocTable])) 13 17 16 Table Scan Table Scan OBJECT:(@SelectedLocation AS [LocTable]) [LocTable].[Unit] 1 0.003125 0.0001581 138 0.0032831 [LocTable].[Unit] NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 76 ms.Table '#0C9AB6ED'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#0BA692B4'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------- -------------------------- ------------- ------------- ------------- ----------- ---------------- -------------------------- -------- ---------------------------------------------------------------- -------- ------------------0 1 INSERT INTO @UnitLocation SELECT Unit FROM @SelectedLocation SelectedLocation WHERE charindex('<ALL>',SelectedLocation.Unit) = 0 14 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.01328438 NULL NULL INSERT 0 NULL0 1 |--Table Insert(OBJECT:(@UnitLocation), SET:([Unit] = @SelectedLocation.[Unit] as [SelectedLocation].[Unit])) 14 2 1 Table Insert Insert OBJECT:(@UnitLocation), SET:([Unit] = @SelectedLocation.[Unit] as [SelectedLocation].[Unit]) NULL 1 0.01 1E-06 9 0.01328438 NULL NULL PLAN_ROW 0 10 1 |--Top(ROWCOUNT est 0) 14 3 2 Top Top TOP EXPRESSION:((0)) NULL 1 0 1E-07 138 0.00328338 [SelectedLocation].[Unit] NULL PLAN_ROW 0 10 1 |--Table Scan(OBJECT:(@SelectedLocation AS [SelectedLocation]), WHERE:(charindex('<ALL>',@SelectedLocation.[Unit] as [SelectedLocation].[Unit])=(0))) 14 4 3 Table Scan Table Scan OBJECT:(@SelectedLocation AS [SelectedLocation]), WHERE:(charindex('<ALL>',@SelectedLocation.[Unit] as [SelectedLocation].[Unit])=(0)) [SelectedLocation].[Unit] 1 0.0032035 7.96E-05 138 0.0032831 [SelectedLocation].[Unit] NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 64 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table '#ClientDataTable____________________________________________________________________________________________________0000000110D6'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'CV3ClientVisit'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------0 1 INSERT INTo #ClientDataTableSELECT DISTINCTCV3ClientVisit.GUID as ClientVisitGUID,CV3ClientVisit.ClientGUID,CV3ClientVisit.ChartGUID,CV3ClientVisit.ClientDisplayName,CV3ClientVisit.IDCode as MRN,CV3ClientVisit.VisitIDCode as VisitID,CV3Cl 15 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.03302956 NULL NULL INSERT 0 NULL0 1 |--Table Insert(OBJECT:([tempdb].[dbo].[#ClientDataTable]), SET:([tempdb].[dbo].[#ClientDataTable].[ClientVisitGUID] = [TESTSP11].[dbo].[CV3ClientVisit].[GUID],[tempdb].[dbo].[#ClientDataTable].[ClientGUID] = [TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID 15 2 1 Table Insert Insert OBJECT:([tempdb].[dbo].[#ClientDataTable]), SET:([tempdb].[dbo].[#ClientDataTable].[ClientVisitGUID] = [TESTSP11].[dbo].[CV3ClientVisit].[GUID],[tempdb].[dbo].[#ClientDataTable].[ClientGUID] = [TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID],[tempdb].[dbo].[ NULL 1 0.01 1E-06 9 0.03302956 NULL NULL PLAN_ROW 0 10 1 |--Top(ROWCOUNT est 0) 15 3 2 Top Top TOP EXPRESSION:((0)) NULL 1 0 1E-07 193 0.02302856 [TESTSP11].[dbo].[CV3ClientVisit].[GUID], [TESTSP11].[dbo].[CV3ClientVisit].[ChartGUID], [TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID], [TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm], [TESTSP11].[dbo].[CV3ClientVisit].[ClientDisplayName], [TESTSP11].[dbo].[ NULL PLAN_ROW 0 10 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID])) 15 4 3 Nested Loops Inner Join OUTER REFERENCES:([TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID]) NULL 1 0 4.18E-06 193 0.02302846 [TESTSP11].[dbo].[CV3ClientVisit].[GUID], [TESTSP11].[dbo].[CV3ClientVisit].[ChartGUID], [TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID], [TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm], [TESTSP11].[dbo].[CV3ClientVisit].[ClientDisplayName], [TESTSP11].[dbo].[ NULL PLAN_ROW 0 10 1 |--Nested Loops(Left Semi Join, WHERE:([Expr1032]=[VisitTypeCareLevel])) 15 5 4 Nested Loops Left Semi Join WHERE:([Expr1032]=[VisitTypeCareLevel]) NULL 1 0 1.254E-05 172 0.01974118 [TESTSP11].[dbo].[CV3ClientVisit].[GUID], [TESTSP11].[dbo].[CV3ClientVisit].[ChartGUID], [TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID], [TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm], [TESTSP11].[dbo].[CV3ClientVisit].[ClientDisplayName], [TESTSP11].[dbo].[ NULL PLAN_ROW 0 10 1 | |--Nested Loops(Left Semi Join, WHERE:([TESTSP11].[dbo].[CV3ClientVisit].[TypeCode]=[VisitType])) 15 6 5 Nested Loops Left Semi Join WHERE:([TESTSP11].[dbo].[CV3ClientVisit].[TypeCode]=[VisitType]) NULL 1 0 6.27E-06 190 0.01643914 [TESTSP11].[dbo].[CV3ClientVisit].[GUID], [TESTSP11].[dbo].[CV3ClientVisit].[ChartGUID], [TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID], [TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm], [TESTSP11].[dbo].[CV3ClientVisit].[ClientDisplayName], [TESTSP11].[dbo].[ NULL PLAN_ROW 0 10 0 | | |--Compute Scalar(DEFINE:([Expr1031]=CONVERT_IMPLICIT(varchar(60),[TESTSP11].[dbo].[CV3Location].[Name] as [Facility].[Name],0))) 15 7 6 Compute Scalar Compute Scalar DEFINE:([Expr1031]=CONVERT_IMPLICIT(varchar(60),[TESTSP11].[dbo].[CV3Location].[Name] as [Facility].[Name],0)) [Expr1031]=CONVERT_IMPLICIT(varchar(60),[TESTSP11].[dbo].[CV3Location].[Name] as [Facility].[Name],0) 1 0 1E-07 205 0.01314712 [TESTSP11].[dbo].[CV3ClientVisit].[GUID], [TESTSP11].[dbo].[CV3ClientVisit].[ChartGUID], [TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID], [TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm], [TESTSP11].[dbo].[CV3ClientVisit].[TypeCode], [TESTSP11].[dbo].[CV3Client NULL PLAN_ROW 0 10 1 | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTSP11].[dbo].[CV3Location].[FacilityGUID])) 15 8 7 Nested Loops Inner Join OUTER REFERENCES:([TESTSP11].[dbo].[CV3Location].[FacilityGUID]) NULL 1 0 4.18E-06 190 0.01314702 [TESTSP11].[dbo].[CV3ClientVisit].[GUID], [TESTSP11].[dbo].[CV3ClientVisit].[ChartGUID], [TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID], [TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm], [TESTSP11].[dbo].[CV3ClientVisit].[TypeCode], [TESTSP11].[dbo].[CV3Client NULL PLAN_ROW 0 10 1 | | | |--Nested Loops(Left Semi Join, WHERE:([TESTSP11].[dbo].[CV3Location].[GUID]=[LocationGuid])) 15 9 8 Nested Loops Left Semi Join WHERE:([TESTSP11].[dbo].[CV3Location].[GUID]=[LocationGuid]) NULL 1 0 4.18E-06 182 0.00985974 [TESTSP11].[dbo].[CV3ClientVisit].[GUID], [TESTSP11].[dbo].[CV3ClientVisit].[ChartGUID], [TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID], [TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm], [TESTSP11].[dbo].[CV3ClientVisit].[TypeCode], [TESTSP11].[dbo].[CV3Client NULL PLAN_ROW 0 10 0 | | | | |--Compute Scalar(DEFINE:([Expr1030]=CONVERT_IMPLICIT(varchar(60),[TESTSP11].[dbo].[CV3Location].[Name],0))) 15 10 9 Compute Scalar Compute Scalar DEFINE:([Expr1030]=CONVERT_IMPLICIT(varchar(60),[TESTSP11].[dbo].[CV3Location].[Name],0)) [Expr1030]=CONVERT_IMPLICIT(varchar(60),[TESTSP11].[dbo].[CV3Location].[Name],0) 1 0 1E-07 191 0.00657216 [TESTSP11].[dbo].[CV3ClientVisit].[GUID], [TESTSP11].[dbo].[CV3ClientVisit].[ChartGUID], [TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID], [TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm], [TESTSP11].[dbo].[CV3ClientVisit].[TypeCode], [TESTSP11].[dbo].[CV3Client NULL PLAN_ROW 0 10 1 | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTSP11].[dbo].[CV3ClientVisit].[CurrentLocationGUID])) 15 11 10 Nested Loops Inner Join OUTER REFERENCES:([TESTSP11].[dbo].[CV3ClientVisit].[CurrentLocationGUID]) NULL 1 0 4.18E-06 176 0.00657206 [TESTSP11].[dbo].[CV3ClientVisit].[GUID], [TESTSP11].[dbo].[CV3ClientVisit].[ChartGUID], [TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID], [TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm], [TESTSP11].[dbo].[CV3ClientVisit].[TypeCode], [TESTSP11].[dbo].[CV3Client NULL PLAN_ROW 0 10 0 | | | | | |--Compute Scalar(DEFINE:([Expr1027]=CONVERT_IMPLICIT(varchar(20),[TESTSP11].[dbo].[CV3ClientVisit].[IDCode],0), [Expr1028]=CONVERT_IMPLICIT(varchar(20),[TESTSP11].[dbo].[CV3ClientVisit].[VisitIDCode],0), 15 12 11 Compute Scalar Compute Scalar DEFINE:([Expr1027]=CONVERT_IMPLICIT(varchar(20),[TESTSP11].[dbo].[CV3ClientVisit].[IDCode],0), [Expr1028]=CONVERT_IMPLICIT(varchar(20),[TESTSP11].[dbo].[CV3ClientVisit].[VisitIDCode],0), [Expr1029]=CONVERT_IMPLICIT(varchar(100),[TESTSP11].[dbo].[CV3ClientV [Expr1027]=CONVERT_IMPLICIT(varchar(20),[TESTSP11].[dbo].[CV3ClientVisit].[IDCode],0), [Expr1028]=CONVERT_IMPLICIT(varchar(20),[TESTSP11].[dbo].[CV3ClientVisit].[VisitIDCode],0), [Expr1029]=CONVERT_IMPLICIT(varchar(100),[TESTSP11].[dbo].[CV3ClientVisit].[T 1 0 1E-07 153 0.0032832 [TESTSP11].[dbo].[CV3ClientVisit].[GUID], [TESTSP11].[dbo].[CV3ClientVisit].[ChartGUID], [TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID], [TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm], [TESTSP11].[dbo].[CV3ClientVisit].[TypeCode], [TESTSP11].[dbo].[CV3Client NULL PLAN_ROW 0 10 1 | | | | | | |--Clustered Index Scan(OBJECT:([TESTSP11].[dbo].[CV3ClientVisit].[CV3ClientVisitCI]), WHERE:([TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm]>=[@StartDtm] AND [TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm 15 13 12 Clustered Index Scan Clustered Index Scan OBJECT:([TESTSP11].[dbo].[CV3ClientVisit].[CV3ClientVisitCI]), WHERE:([TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm]>=[@StartDtm] AND [TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm]<=[@StopDtm] AND ([TESTSP11].[dbo].[CV3ClientVisit].[VisitStatus]='CLS' OR [TEST [TESTSP11].[dbo].[CV3ClientVisit].[GUID], [TESTSP11].[dbo].[CV3ClientVisit].[ChartGUID], [TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID], [TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm], [TESTSP11].[dbo].[CV3ClientVisit].[IDCode], [TESTSP11].[dbo].[CV3ClientVi 1 0.003125 0.0001581 181 0.0032831 [TESTSP11].[dbo].[CV3ClientVisit].[GUID], [TESTSP11].[dbo].[CV3ClientVisit].[ChartGUID], [TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID], [TESTSP11].[dbo].[CV3ClientVisit].[AdmitDtm], [TESTSP11].[dbo].[CV3ClientVisit].[IDCode], [TESTSP11].[dbo].[CV3ClientVi NULL PLAN_ROW 0 10 0 | | | | | |--Clustered Index Seek(OBJECT:([TESTSP11].[dbo].[CV3Location].[CV3LocationPK]), SEEK:([TESTSP11].[dbo].[CV3Location].[GUID]=[TESTSP11].[dbo].[CV3ClientVisit].[CurrentLocationGUID]) ORDERED FORWARD) 15 23 11 Clustered Index Seek Clustered Index Seek OBJECT:([TESTSP11].[dbo].[CV3Location].[CV3LocationPK]), SEEK:([TESTSP11].[dbo].[CV3Location].[GUID]=[TESTSP11].[dbo].[CV3ClientVisit].[CurrentLocationGUID]) ORDERED FORWARD [TESTSP11].[dbo].[CV3Location].[GUID], [TESTSP11].[dbo].[CV3Location].[Name], [TESTSP11].[dbo].[CV3Location].[FacilityGUID] 1 0.003125 0.0001581 44 0.0032831 [TESTSP11].[dbo].[CV3Location].[GUID], [TESTSP11].[dbo].[CV3Location].[Name], [TESTSP11].[dbo].[CV3Location].[FacilityGUID] NULL PLAN_ROW 0 10 0 | | | | |--Table Scan(OBJECT:(@LocationGUID)) 15 27 9 Table Scan Table Scan OBJECT:(@LocationGUID) [LocationGuid] 1 0.003125 0.0001581 16 0.0032831 [LocationGuid] NULL PLAN_ROW 0 10 0 | | | |--Clustered Index Seek(OBJECT:([TESTSP11].[dbo].[CV3Location].[CV3LocationPK] AS [Facility]), SEEK:([Facility].[GUID]=[TESTSP11].[dbo].[CV3Location].[FacilityGUID]) ORDERED FORWARD) 15 29 8 Clustered Index Seek Clustered Index Seek OBJECT:([TESTSP11].[dbo].[CV3Location].[CV3LocationPK] AS [Facility]), SEEK:([Facility].[GUID]=[TESTSP11].[dbo].[CV3Location].[FacilityGUID]) ORDERED FORWARD [Facility].[Name] 1 0.003125 0.0001581 26 0.0032831 [Facility].[Name] NULL PLAN_ROW 0 10 0 | | |--Table Scan(OBJECT:(@VisitTypeTable)) 15 33 6 Table Scan Table Scan OBJECT:(@VisitTypeTable) [VisitType] 3 0.003125 0.0001603 26 0.0032853 [VisitType] NULL PLAN_ROW 0 10 0 | |--Table Scan(OBJECT:(@VisitTypeCareLevelTable)) 15 35 5 Table Scan Table Scan OBJECT:(@VisitTypeCareLevelTable) [VisitTypeCareLevel] 6 0.003125 0.0001636 69 0.0032886 [VisitTypeCareLevel] NULL PLAN_ROW 0 10 0 |--Clustered Index Seek(OBJECT:([TESTSP11].[dbo].[CV3Client].[CV3ClientPK]), SEEK:([TESTSP11].[dbo].[CV3Client].[GUID]=[TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID]) ORDERED FORWARD) 15 37 4 Clustered Index Seek Clustered Index Seek OBJECT:([TESTSP11].[dbo].[CV3Client].[CV3ClientPK]), SEEK:([TESTSP11].[dbo].[CV3Client].[GUID]=[TESTSP11].[dbo].[CV3ClientVisit].[ClientGUID]) ORDERED FORWARD [TESTSP11].[dbo].[CV3Client].[BirthDayNum], [TESTSP11].[dbo].[CV3Client].[BirthMonthNum], [TESTSP11].[dbo].[CV3Client].[BirthYearNum], [TESTSP11].[dbo].[CV3Client].[GenderCode] 1 0.003125 0.0001581 30 0.0032831 [TESTSP11].[dbo].[CV3Client].[BirthDayNum], [TESTSP11].[dbo].[CV3Client].[BirthMonthNum], [TESTSP11].[dbo].[CV3Client].[BirthYearNum], [TESTSP11].[dbo].[CV3Client].[GenderCode] NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 104 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 203 ms, elapsed time = 1442 ms.Table '#04F99525'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#ClientDataTable____________________________________________________________________________________________________0000000110D6'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------0 1 INSERT INTO @CompliantSELECT DISTINCT 1 AS Compliant ---the patient is compliant with measure ,ClientDataTable.MRN From #ClientDataTable ClientDataTable INNER JOIN CV3ClientDocument --CI =ClientGUID, ChartGUID, PatCareDocGUI 16 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.04108411 NULL NULL INSERT 0 NULL0 1 |--Table Insert(OBJECT:(@Compliant), SET:([Compliant] = [Expr1025],[MRN] = #ClientDataTable.[MRN] as [ClientDataTable].[MRN])) 16 2 1 Table Insert Insert OBJECT:(@Compliant), SET:([Compliant] = [Expr1025],[MRN] = #ClientDataTable.[MRN] as [ClientDataTable].[MRN]) NULL 1 0.01 1E-06 9 0.04108411 NULL NULL PLAN_ROW 0 10 0 |--Compute Scalar(DEFINE:([Expr1025]=(1))) 16 3 2 Compute Scalar Compute Scalar DEFINE:([Expr1025]=(1)) [Expr1025]=(1) 1 0 1E-07 16 0.03108311 [ClientDataTable].[MRN], [Expr1025] NULL PLAN_ROW 0 10 1 |--Top(ROWCOUNT est 0) 16 4 3 Top Top TOP EXPRESSION:((0)) NULL 1 0 1E-07 12 0.03108301 [ClientDataTable].[MRN] NULL PLAN_ROW 0 10 1 |--Sort(DISTINCT ORDER BY:([ClientDataTable].[MRN] ASC)) 16 5 4 Sort Distinct Sort DISTINCT ORDER BY:([ClientDataTable].[MRN] ASC) NULL 1 0.01126126 0.000100012 12 0.03108291 [ClientDataTable].[MRN] NULL PLAN_ROW 0 10 1 |--Nested Loops(Left Semi Join, WHERE:([TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[Name]=[TESTSP11].[dbo].[CV3UserDictionaryValue].[Value])) 16 6 5 Nested Loops Left Semi Join WHERE:([TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[Name]=[TESTSP11].[dbo].[CV3UserDictionaryValue].[Value]) NULL 1 0 4.18E-06 12 0.01972164 [ClientDataTable].[MRN] NULL PLAN_ROW 0 10 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID])) 16 7 6 Nested Loops Inner Join OUTER REFERENCES:([TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID]) NULL 1 0 4.18E-06 32 0.01643406 [ClientDataTable].[MRN], [TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[Name] NULL PLAN_ROW 0 10 1 | |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTSP11].[dbo].[CV3ObservationDocument].[ObservationGUID])) 16 8 7 Nested Loops Inner Join OUTER REFERENCES:([TESTSP11].[dbo].[CV3ObservationDocument].[ObservationGUID]) NULL 1 0 4.18E-06 38 0.01314678 [ClientDataTable].[MRN], [TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID] NULL PLAN_ROW 0 10 1 | | |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTSP11].[dbo].[CV3ClientDocument].[GUID])) 16 9 8 Nested Loops Inner Join OUTER REFERENCES:([TESTSP11].[dbo].[CV3ClientDocument].[GUID]) NULL 1 0 4.18E-06 47 0.00985902 [ClientDataTable].[MRN], [TESTSP11].[dbo].[CV3ObservationDocument].[ObservationGUID], [TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID] NULL PLAN_ROW 0 10 1 | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ClientDataTable].[ClientGUID], [ClientDataTable].[ChartGUID])) 16 10 9 Nested Loops Inner Join OUTER REFERENCES:([ClientDataTable].[ClientGUID], [ClientDataTable].[ChartGUID]) NULL 1 0 4.18E-06 29 0.00657126 [ClientDataTable].[MRN], [TESTSP11].[dbo].[CV3ClientDocument].[GUID] NULL PLAN_ROW 0 10 1 | | | | |--Filter(WHERE:([TESTSP11].[dbo].[AQM_GetAgeAsNum_FN](#ClientDataTable.[BirthDayNum] as [ClientDataTable].[BirthDayNum],#ClientDataTable.[BirthMonthNum] as [ClientDataTable].[BirthMonthNum],#ClientDataTable.[ 16 11 10 Filter Filter WHERE:([TESTSP11].[dbo].[AQM_GetAgeAsNum_FN](#ClientDataTable.[BirthDayNum] as [ClientDataTable].[BirthDayNum],#ClientDataTable.[BirthMonthNum] as [ClientDataTable].[BirthMonthNum],#ClientDataTable.[BirthYearNum] as [ClientDataTable].[BirthYearNum],#Client NULL 1 0 8.8E-07 38 0.00328398 [ClientDataTable].[ClientGUID], [ClientDataTable].[ChartGUID], [ClientDataTable].[MRN] NULL PLAN_ROW 0 10 1 | | | | | |--Table Scan(OBJECT:([tempdb].[dbo].[#ClientDataTable] AS [ClientDataTable])) 16 12 11 Table Scan Table Scan OBJECT:([tempdb].[dbo].[#ClientDataTable] AS [ClientDataTable]) [ClientDataTable].[ClientGUID], [ClientDataTable].[ChartGUID], [ClientDataTable].[MRN], [ClientDataTable].[BirthDayNum], [ClientDataTable].[BirthMonthNum], [ClientDataTable].[BirthYearNum], [ClientDataTable].[AdmitDTM] 1 0.003125 0.0001581 58 0.0032831 [ClientDataTable].[ClientGUID], [ClientDataTable].[ChartGUID], [ClientDataTable].[MRN], [ClientDataTable].[BirthDayNum], [ClientDataTable].[BirthMonthNum], [ClientDataTable].[BirthYearNum], [ClientDataTable].[AdmitDTM] NULL PLAN_ROW 0 10 0 | | | | |--Clustered Index Seek(OBJECT:([TESTSP11].[dbo].[CV3ClientDocument].[DocumentClustIdx]), SEEK:([TESTSP11].[dbo].[CV3ClientDocument].[ClientGUID]=#ClientDataTable.[ClientGUID] as [ClientDataTable].[ClientGUID] 16 14 10 Clustered Index Seek Clustered Index Seek OBJECT:([TESTSP11].[dbo].[CV3ClientDocument].[DocumentClustIdx]), SEEK:([TESTSP11].[dbo].[CV3ClientDocument].[ClientGUID]=#ClientDataTable.[ClientGUID] as [ClientDataTable].[ClientGUID] AND [TESTSP11].[dbo].[CV3ClientDocument].[ChartGUID]=#ClientDataTable. [TESTSP11].[dbo].[CV3ClientDocument].[GUID] 1 0.003125 0.0001581 16 0.0032831 [TESTSP11].[dbo].[CV3ClientDocument].[GUID] NULL PLAN_ROW 0 10 0 | | | |--Clustered Index Seek(OBJECT:([TESTSP11].[dbo].[CV3ObservationDocument].[CV3ObservationDocumentCI]), SEEK:([TESTSP11].[dbo].[CV3ObservationDocument].[OwnerGUID]=[TESTSP11].[dbo].[CV3ClientDocument].[GUID]), WHER 16 15 9 Clustered Index Seek Clustered Index Seek OBJECT:([TESTSP11].[dbo].[CV3ObservationDocument].[CV3ObservationDocumentCI]), SEEK:([TESTSP11].[dbo].[CV3ObservationDocument].[OwnerGUID]=[TESTSP11].[dbo].[CV3ClientDocument].[GUID]), WHERE:([TESTSP11].[dbo].[CV3ObservationDocument].[Active]=(1)) ORDERED [TESTSP11].[dbo].[CV3ObservationDocument].[ObservationGUID], [TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID] 1 0.003125 0.0001581 35 0.0032831 [TESTSP11].[dbo].[CV3ObservationDocument].[ObservationGUID], [TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID] NULL PLAN_ROW 0 10 0 | | |--Filter(WHERE:([TESTSP11].[dbo].[CV3Observation].[ValueText] IS NOT NULL)) 16 16 8 Filter Filter WHERE:([TESTSP11].[dbo].[CV3Observation].[ValueText] IS NOT NULL) NULL 1 0 4.8E-07 9 0.00328358 NULL NULL PLAN_ROW 0 10 0 | | |--Clustered Index Seek(OBJECT:([TESTSP11].[dbo].[CV3Observation].[CV3ObservationPK]), SEEK:([TESTSP11].[dbo].[CV3Observation].[GUID]=[TESTSP11].[dbo].[CV3ObservationDocument].[ObservationGUID]) ORDERED FORWARD) 16 17 16 Clustered Index Seek Clustered Index Seek OBJECT:([TESTSP11].[dbo].[CV3Observation].[CV3ObservationPK]), SEEK:([TESTSP11].[dbo].[CV3Observation].[GUID]=[TESTSP11].[dbo].[CV3ObservationDocument].[ObservationGUID]) ORDERED FORWARD [TESTSP11].[dbo].[CV3Observation].[ValueText] 1 0.003125 0.0001581 75 0.0032831 [TESTSP11].[dbo].[CV3Observation].[ValueText] NULL PLAN_ROW 0 10 0 | |--Clustered Index Seek(OBJECT:([TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[CV3ObsCatalogMasterItemPK]), SEEK:([TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[GUID]=[TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID]) 16 19 7 Clustered Index Seek Clustered Index Seek OBJECT:([TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[CV3ObsCatalogMasterItemPK]), SEEK:([TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[GUID]=[TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID]) ORDERED FORWARD [TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[Name] 1 0.003125 0.0001581 12 0.0032831 [TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[Name] NULL PLAN_ROW 0 10 0 |--Clustered Index Seek(OBJECT:([TESTSP11].[dbo].[CV3UserDictionaryValue].[UserDictionaryValueClustIdx]), SEEK:([TESTSP11].[dbo].[CV3UserDictionaryValue].[UserDictionaryCode]='AQM_SmokingAssess') ORDERED FORWARD) 16 20 6 Clustered Index Seek Clustered Index Seek OBJECT:([TESTSP11].[dbo].[CV3UserDictionaryValue].[UserDictionaryValueClustIdx]), SEEK:([TESTSP11].[dbo].[CV3UserDictionaryValue].[UserDictionaryCode]='AQM_SmokingAssess') ORDERED FORWARD [TESTSP11].[dbo].[CV3UserDictionaryValue].[Value] 1 0.003125 0.0001581 22 0.0032831 [TESTSP11].[dbo].[CV3UserDictionaryValue].[Value] NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 171 ms.Table '#04F99525'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#ClientDataTable____________________________________________________________________________________________________0000000110D6'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------0 1 INSERT INTO @CompliantSELECT DISTINCT 1 AS Compliant ---the patient is compliant with measure ,ClientDataTable.MRN From #ClientDataTable ClientDataTable INNER JOIN CV3ClientDocument --CI =ClientGUID, ChartGUID, PatCareDocGUID, 17 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.04108411 NULL NULL INSERT 0 NULL0 1 |--Table Insert(OBJECT:(@Compliant), SET:([Compliant] = [Expr1026],[MRN] = #ClientDataTable.[MRN] as [ClientDataTable].[MRN])) 17 2 1 Table Insert Insert OBJECT:(@Compliant), SET:([Compliant] = [Expr1026],[MRN] = #ClientDataTable.[MRN] as [ClientDataTable].[MRN]) NULL 1 0.01 1E-06 9 0.04108411 NULL NULL PLAN_ROW 0 10 0 |--Compute Scalar(DEFINE:([Expr1026]=(1))) 17 3 2 Compute Scalar Compute Scalar DEFINE:([Expr1026]=(1)) [Expr1026]=(1) 1 0 1E-07 16 0.03108311 [ClientDataTable].[MRN], [Expr1026] NULL PLAN_ROW 0 10 1 |--Top(ROWCOUNT est 0) 17 4 3 Top Top TOP EXPRESSION:((0)) NULL 1 0 1E-07 12 0.03108301 [ClientDataTable].[MRN] NULL PLAN_ROW 0 10 1 |--Sort(DISTINCT ORDER BY:([ClientDataTable].[MRN] ASC)) 17 5 4 Sort Distinct Sort DISTINCT ORDER BY:([ClientDataTable].[MRN] ASC) NULL 1 0.01126126 0.000100012 12 0.03108291 [ClientDataTable].[MRN] NULL PLAN_ROW 0 10 1 |--Nested Loops(Left Semi Join, WHERE:([TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[Name]=[TESTSP11].[dbo].[CV3UserDictionaryValue].[Value])) 17 6 5 Nested Loops Left Semi Join WHERE:([TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[Name]=[TESTSP11].[dbo].[CV3UserDictionaryValue].[Value]) NULL 1 0 4.18E-06 12 0.01972164 [ClientDataTable].[MRN] NULL PLAN_ROW 0 10 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID])) 17 7 6 Nested Loops Inner Join OUTER REFERENCES:([TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID]) NULL 1 0 4.18E-06 41 0.01643406 [ClientDataTable].[MRN], [TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[Name] NULL PLAN_ROW 0 10 1 | |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTSP11].[dbo].[CV3ClientDocument].[ClientGUID], [TESTSP11].[dbo].[CV3ObservationDocument].[ObservationDocumentGUID])) 17 8 7 Nested Loops Inner Join OUTER REFERENCES:([TESTSP11].[dbo].[CV3ClientDocument].[ClientGUID], [TESTSP11].[dbo].[CV3ObservationDocument].[ObservationDocumentGUID]) NULL 1 0 4.18E-06 47 0.01314678 [ClientDataTable].[MRN], [TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID] NULL PLAN_ROW 0 10 1 | | |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTSP11].[dbo].[CV3ClientDocument].[GUID])) 17 9 8 Nested Loops Inner Join OUTER REFERENCES:([TESTSP11].[dbo].[CV3ClientDocument].[GUID]) NULL 1 0 4.18E-06 56 0.00985902 [ClientDataTable].[MRN], [TESTSP11].[dbo].[CV3ClientDocument].[ClientGUID], [TESTSP11].[dbo].[CV3ObservationDocument].[ObservationDocumentGUID], [TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID] NULL PLAN_ROW 0 10 1 | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ClientDataTable].[ClientGUID], [ClientDataTable].[ChartGUID])) 17 10 9 Nested Loops Inner Join OUTER REFERENCES:([ClientDataTable].[ClientGUID], [ClientDataTable].[ChartGUID]) NULL 1 0 4.18E-06 38 0.00657126 [ClientDataTable].[MRN], [TESTSP11].[dbo].[CV3ClientDocument].[GUID], [TESTSP11].[dbo].[CV3ClientDocument].[ClientGUID] NULL PLAN_ROW 0 10 1 | | | | |--Filter(WHERE:([TESTSP11].[dbo].[AQM_GetAgeAsNum_FN](#ClientDataTable.[BirthDayNum] as [ClientDataTable].[BirthDayNum],#ClientDataTable.[BirthMonthNum] as [ClientDataTable].[BirthMonthNum],#ClientDataTable.[ 17 11 10 Filter Filter WHERE:([TESTSP11].[dbo].[AQM_GetAgeAsNum_FN](#ClientDataTable.[BirthDayNum] as [ClientDataTable].[BirthDayNum],#ClientDataTable.[BirthMonthNum] as [ClientDataTable].[BirthMonthNum],#ClientDataTable.[BirthYearNum] as [ClientDataTable].[BirthYearNum],#Client NULL 1 0 8.8E-07 38 0.00328398 [ClientDataTable].[ClientGUID], [ClientDataTable].[ChartGUID], [ClientDataTable].[MRN] NULL PLAN_ROW 0 10 1 | | | | | |--Table Scan(OBJECT:([tempdb].[dbo].[#ClientDataTable] AS [ClientDataTable])) 17 12 11 Table Scan Table Scan OBJECT:([tempdb].[dbo].[#ClientDataTable] AS [ClientDataTable]) [ClientDataTable].[ClientGUID], [ClientDataTable].[ChartGUID], [ClientDataTable].[MRN], [ClientDataTable].[BirthDayNum], [ClientDataTable].[BirthMonthNum], [ClientDataTable].[BirthYearNum], [ClientDataTable].[AdmitDTM] 1 0.003125 0.0001581 58 0.0032831 [ClientDataTable].[ClientGUID], [ClientDataTable].[ChartGUID], [ClientDataTable].[MRN], [ClientDataTable].[BirthDayNum], [ClientDataTable].[BirthMonthNum], [ClientDataTable].[BirthYearNum], [ClientDataTable].[AdmitDTM] NULL PLAN_ROW 0 10 0 | | | | |--Clustered Index Seek(OBJECT:([TESTSP11].[dbo].[CV3ClientDocument].[DocumentClustIdx]), SEEK:([TESTSP11].[dbo].[CV3ClientDocument].[ClientGUID]=#ClientDataTable.[ClientGUID] as [ClientDataTable].[ClientGUID] 17 14 10 Clustered Index Seek Clustered Index Seek OBJECT:([TESTSP11].[dbo].[CV3ClientDocument].[DocumentClustIdx]), SEEK:([TESTSP11].[dbo].[CV3ClientDocument].[ClientGUID]=#ClientDataTable.[ClientGUID] as [ClientDataTable].[ClientGUID] AND [TESTSP11].[dbo].[CV3ClientDocument].[ChartGUID]=#ClientDataTable. [TESTSP11].[dbo].[CV3ClientDocument].[GUID], [TESTSP11].[dbo].[CV3ClientDocument].[ClientGUID] 1 0.003125 0.0001581 25 0.0032831 [TESTSP11].[dbo].[CV3ClientDocument].[GUID], [TESTSP11].[dbo].[CV3ClientDocument].[ClientGUID] NULL PLAN_ROW 0 10 0 | | | |--Clustered Index Seek(OBJECT:([TESTSP11].[dbo].[CV3ObservationDocument].[CV3ObservationDocumentCI]), SEEK:([TESTSP11].[dbo].[CV3ObservationDocument].[OwnerGUID]=[TESTSP11].[dbo].[CV3ClientDocument].[GUID]), WHER 17 15 9 Clustered Index Seek Clustered Index Seek OBJECT:([TESTSP11].[dbo].[CV3ObservationDocument].[CV3ObservationDocumentCI]), SEEK:([TESTSP11].[dbo].[CV3ObservationDocument].[OwnerGUID]=[TESTSP11].[dbo].[CV3ClientDocument].[GUID]), WHERE:([TESTSP11].[dbo].[CV3ObservationDocument].[Active]=(1)) ORDERED [TESTSP11].[dbo].[CV3ObservationDocument].[ObservationDocumentGUID], [TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID] 1 0.003125 0.0001581 26 0.0032831 [TESTSP11].[dbo].[CV3ObservationDocument].[ObservationDocumentGUID], [TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID] NULL PLAN_ROW 0 10 0 | | |--Clustered Index Seek(OBJECT:([TESTSP11].[dbo].[SCMObsFSListValues].[SCMObsFSListValuesCI]), SEEK:([TESTSP11].[dbo].[SCMObsFSListValues].[ClientGUID]=[TESTSP11].[dbo].[CV3ClientDocument].[ClientGUID] AND [TESTSP11].[d 17 16 8 Clustered Index Seek Clustered Index Seek OBJECT:([TESTSP11].[dbo].[SCMObsFSListValues].[SCMObsFSListValuesCI]), SEEK:([TESTSP11].[dbo].[SCMObsFSListValues].[ClientGUID]=[TESTSP11].[dbo].[CV3ClientDocument].[ClientGUID] AND [TESTSP11].[dbo].[SCMObsFSListValues].[ParentGUID]=[TESTSP11].[dbo].[CV3Ob NULL 1 0.003125 0.0001581 17 0.0032831 NULL NULL PLAN_ROW 0 10 0 | |--Clustered Index Seek(OBJECT:([TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[CV3ObsCatalogMasterItemPK]), SEEK:([TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[GUID]=[TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID]) 17 17 7 Clustered Index Seek Clustered Index Seek OBJECT:([TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[CV3ObsCatalogMasterItemPK]), SEEK:([TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[GUID]=[TESTSP11].[dbo].[CV3ObservationDocument].[ObsMasterItemGUID]) ORDERED FORWARD [TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[Name] 1 0.003125 0.0001581 12 0.0032831 [TESTSP11].[dbo].[CV3ObsCatalogMasterItem].[Name] NULL PLAN_ROW 0 10 0 |--Clustered Index Seek(OBJECT:([TESTSP11].[dbo].[CV3UserDictionaryValue].[UserDictionaryValueClustIdx]), SEEK:([TESTSP11].[dbo].[CV3UserDictionaryValue].[UserDictionaryCode]='AQM_SmokingAssess') ORDERED FORWARD) 17 18 6 Clustered Index Seek Clustered Index Seek OBJECT:([TESTSP11].[dbo].[CV3UserDictionaryValue].[UserDictionaryValueClustIdx]), SEEK:([TESTSP11].[dbo].[CV3UserDictionaryValue].[UserDictionaryCode]='AQM_SmokingAssess') ORDERED FORWARD [TESTSP11].[dbo].[CV3UserDictionaryValue].[Value] 1 0.003125 0.0001581 22 0.0032831 [TESTSP11].[dbo].[CV3UserDictionaryValue].[Value] NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 146 ms.Compliant MRN----------- --------------------Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#ClientDataTable____________________________________________________________________________________________________0000000110D6'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#04F99525'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------ -------- ---------------------------------------------------------------- -------- ------------------0 1 SELECT DISTINCT Compliant.Compliant ,Compliant.MRN FROM @Compliant CompliantUNION ALL SELECT DISTINCT 0 AS Compliant --The patient is NOT compliant with measure ,ClientDataTable.MRN From #ClientDataTable ClientDat 18 1 0 NULL NULL NULL NULL 2 NULL NULL NULL 0.03257822 NULL NULL SELECT 0 NULL0 1 |--Concatenation 18 2 1 Concatenation Concatenation NULL [Union1011] = ([Compliant].[Compliant], [Expr1010]), [Union1012] = ([Compliant].[MRN], [ClientDataTable].[MRN]) 2 0 2E-07 25 0.03257822 [Union1011], [Union1012] NULL PLAN_ROW 0 10 1 |--Sort(DISTINCT ORDER BY:([Compliant].[Compliant] ASC, [Compliant].[MRN] ASC)) 18 3 2 Sort Distinct Sort DISTINCT ORDER BY:([Compliant].[Compliant] ASC, [Compliant].[MRN] ASC) NULL 1 0.01126126 0.000100025 25 0.01464439 [Compliant].[Compliant], [Compliant].[MRN] NULL PLAN_ROW 0 10 1 | |--Table Scan(OBJECT:(@Compliant AS [Compliant])) 18 4 3 Table Scan Table Scan OBJECT:(@Compliant AS [Compliant]) [Compliant].[Compliant], [Compliant].[MRN] 1 0.003125 0.0001581 25 0.0032831 [Compliant].[Compliant], [Compliant].[MRN] NULL PLAN_ROW 0 10 1 |--Sort(DISTINCT ORDER BY:([ClientDataTable].[MRN] ASC)) 18 5 2 Sort Distinct Sort DISTINCT ORDER BY:([ClientDataTable].[MRN] ASC) NULL 1 0.01126126 0.000100016 16 0.01793364 [ClientDataTable].[MRN], [Expr1010] NULL PLAN_ROW 0 10 0 |--Compute Scalar(DEFINE:([Expr1010]=(0))) 18 6 5 Compute Scalar Compute Scalar DEFINE:([Expr1010]=(0)) [Expr1010]=(0) 1 0 1E-07 16 0.00657236 [ClientDataTable].[MRN], [Expr1010] NULL PLAN_ROW 0 10 1 |--Nested Loops(Left Anti Semi Join, WHERE:(#ClientDataTable.[MRN] as [ClientDataTable].[MRN] IS NULL OR [MRN] IS NULL OR #ClientDataTable.[MRN] as [ClientDataTable].[MRN]=[MRN])) 18 7 6 Nested Loops Left Anti Semi Join WHERE:(#ClientDataTable.[MRN] as [ClientDataTable].[MRN] IS NULL OR [MRN] IS NULL OR #ClientDataTable.[MRN] as [ClientDataTable].[MRN]=[MRN]) NULL 1 0 4.18E-06 12 0.00657226 [ClientDataTable].[MRN] NULL PLAN_ROW 0 10 1 |--Filter(WHERE:([TESTSP11].[dbo].[AQM_GetAgeAsNum_FN](#ClientDataTable.[BirthDayNum] as [ClientDataTable].[BirthDayNum],#ClientDataTable.[BirthMonthNum] as [ClientDataTable].[BirthMonthNum],#ClientDataTable.[BirthYearNum] as [ClientD 18 8 7 Filter Filter WHERE:([TESTSP11].[dbo].[AQM_GetAgeAsNum_FN](#ClientDataTable.[BirthDayNum] as [ClientDataTable].[BirthDayNum],#ClientDataTable.[BirthMonthNum] as [ClientDataTable].[BirthMonthNum],#ClientDataTable.[BirthYearNum] as [ClientDataTable].[BirthYearNum],#Client NULL 1 0 8.8E-07 12 0.00328398 [ClientDataTable].[MRN] NULL PLAN_ROW 0 10 1 | |--Table Scan(OBJECT:([tempdb].[dbo].[#ClientDataTable] AS [ClientDataTable])) 18 9 8 Table Scan Table Scan OBJECT:([tempdb].[dbo].[#ClientDataTable] AS [ClientDataTable]) [ClientDataTable].[MRN], [ClientDataTable].[BirthDayNum], [ClientDataTable].[BirthMonthNum], [ClientDataTable].[BirthYearNum], [ClientDataTable].[AdmitDTM] 1 0.003125 0.0001581 32 0.0032831 [ClientDataTable].[MRN], [ClientDataTable].[BirthDayNum], [ClientDataTable].[BirthMonthNum], [ClientDataTable].[BirthYearNum], [ClientDataTable].[AdmitDTM] NULL PLAN_ROW 0 10 0 |--Table Scan(OBJECT:(@Compliant)) 18 11 7 Table Scan Table Scan OBJECT:(@Compliant) [MRN] 1 0.003125 0.0001581 21 0.0032831 [MRN] NULL PLAN_ROW 0 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 132 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 203 ms, elapsed time = 1895 ms. |
|