Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 analyze sql plan

Author  Topic 

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 NULL
1 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 NULL
1 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 NULL
1 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 NULL
1 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 NULL
1 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 NULL
1 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 NULL
1 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 NULL
1 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 NULL
1 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 NULL
1 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 NULL
0 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 1
0 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 1
1 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 1
1 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 NULL
0 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 1
1 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 1
1 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 1
1 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 1
1 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 NULL
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 NULL
0 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 1
0 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 1
0 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 #ClientDataTable

SELECT DISTINCT
CV3ClientVisit.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 NULL
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 @Compliant
SELECT 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 NULL
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 @Compliant
SELECT 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 NULL
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 Compliant

UNION 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 NULL
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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 1
0 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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-05-02 : 01:22:02
Do you thing anybody will have time and patience to do entire analysis for you? You could be better off identifying the costly steps and then putting it here so that somebody could help you in fine tuning it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site