Stored procedure really slow..Sugestions?

Question

Post reply

Stored procedure really slow..Sugestions?

huum

Right there with Babe

Points: 743
More actions
July 23, 2013 at 1:28 pm

#277788

USE []
GO
ALTER PROCEDURE [dbo].[NIC_ENTERPRISE_PatientActivityLogSearch]
@lUser INT,
@szFirst VARCHAR(20) = '',
@szLast VARCHAR(40) = '',
@szChartNum VARCHAR(10) = NULL,
@SDate DATETIME = NULL,
@EDate DATETIME = NULL,
@szIdentifierValue VARCHAR(50) = '',
@lIdentifierType AS INT = NULL,
@nSex AS INT = NULL,
@bSearchInactive AS BIT = 0,
@szPhoneArea VARCHAR(10) = '',
@szPhone1 VARCHAR(10) = '',
@szPhone2 VARCHAR(10) = '',
@DayDOB VARCHAR(10) = NULL,
@MonthDOB VARCHAR(10) = NULL,
@YearDOB VARCHAR(10) = NULL,
@lEnterprise INT
AS
/**
if there is any aduit record in DB for current Enterprise, the sp will check aduit, otherwise ignore the aduit check
**/
if exists (select lid from MAP_AuditActionTypeTableToMappingTable WITH (NOLOCK) where luserAction=83 and lUserActionType=5 and lEnterprise=@lenterprise)
update MAP_AuditActionTypeTableToMappingTable set bIsavailable=1 where luserAction=83 and lUserActionType=5 and lEnterprise=@lenterprise
if exists (select lid from MAP_AuditActionTypeTableToMappingTable WITH (NOLOCK) where luserAction=84 and lUserActionType=5 and lEnterprise=@lenterprise)
update MAP_AuditActionTypeTableToMappingTable set bIsavailable=1 where luserAction=84 and lUserActionType=5 and lEnterprise=@lenterprise
DECLARE @sSQLSelect NVARCHAR(4000)
DECLARE @sSQLSearch NVARCHAR(4000)
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET @sSQLSelect = ''
SET @sSQLSearch = ''
IF @sDate IS NOT NULL
AND @sDate <> ''
AND @eDate IS NOT NULL
AND @eDate <> ''
BEGIN
SET @sSQLSearch = @sSQLSearch + ' AND UALog.dDateOfAction BETWEEN ''' + CONVERT(VARCHAR, @SDate) + '''' + ' AND '+'''' + CONVERT(VARCHAR, @eDate ) + ''''
END
IF @szLast IS NOT NULL
AND @szLast <> ''
BEGIN
SET @sSQLSearch = @sSQLSearch + ' AND p.szLast LIKE '''
+ @szLast + '%'''
END
IF @szFirst IS NOT NULL
AND @szFirst <> ''
BEGIN
SET @sSQLSearch = @sSQLSearch + ' AND p.szFirst LIKE '''
+ @szFirst + '%'''
END
IF @szChartNum IS NOT NULL
AND @szChartNum <> ''
BEGIN
SET @sSQLSearch = @sSQLSearch + ' AND p.szChartNum LIKE '''
+ @szChartNum + '%'''
END
/* Gender check*/
IF @nSex IS NOT NULL
BEGIN
SET @sSQLSearch = @sSQLSearch + ' AND p.nSex = '
+ CONVERT(VARCHAR, @nSex)
END
/* Check for date of birth */
IF @YearDOB IS NOT NULL
AND @YearDOB <> ''
AND @MonthDOB IS NOT NULL
AND @MonthDOB <> ''
AND @DayDOB IS NOT NULL
AND @DayDOB <> ''
BEGIN
SET @sSQLSearch = @sSQLSearch + ' AND p.dDOB= ''' + @YearDOB
+ '-' + @MonthDOB + '-' + @DayDOB + ''' '
END
ELSE
BEGIN
/* Year part of Date of Birth */
IF @YearDOB IS NOT NULL
AND @YearDOB <> ''
BEGIN
SET @sSQLSearch = @sSQLSearch + ' AND Year(p.dDOB)= '''
+ @YearDOB + '%'' '
END
/* Month part of Date of Birth */
IF @MonthDOB IS NOT NULL
AND @MonthDOB <> ''
BEGIN
SET @sSQLSearch = @sSQLSearch + ' AND Month(p.dDOB)= '''
+ @MonthDOB + '%'' '
END
/* Day part of Date of Birth */
IF @DayDOB IS NOT NULL
AND @DayDOB <> ''
BEGIN
SET @sSQLSearch = @sSQLSearch + ' AND Day(p.dDOB)= '''
+ @DayDOB + '%'' '
END
END
/* First part of Phone number */
IF @szPhoneArea IS NOT NULL
AND @szPhoneArea <> ''
BEGIN
SET @sSQLSearch = @sSQLSearch + ' AND a.szPhoneArea LIKE '''
+ @szPhoneArea + '%'''
END
/* Second part of Phone number */
IF @szPhone1 IS NOT NULL
AND @szPhone1 <> ''
BEGIN
SET @sSQLSearch = @sSQLSearch + ' AND a.szPhone1 LIKE '''
+ @szPhone1 + '%'''
END
/* Third part of Phone number */
IF @szPhone2 IS NOT NULL
AND @szPhone2 <> ''
BEGIN
SET @sSQLSearch = @sSQLSearch + ' AND a.szPhone2 LIKE '''
+ @szPhone2 + '%'''
END
IF EXISTS ( SELECT TOP 1
lid
FROM MAP_AuditActionTypeTableToMappingTable WITH (NOLOCK)
WHERE lEnterprise = @lEnterprise )
BEGIN --with audit record
IF @lUser = 0
BEGIN
IF @szIdentifierValue = ''
BEGIN
SET @sSQLSelect = @sSQLSelect +
' SELECT
UALog.*,
p.szFirst,
p.szLast,
p.szChartNum,
o.szOfficeName,
UA.szAction,
UAT.szType + '' '' + UA.szAction AS szFullDescription,
u.szLast AS szUserLast,
u.szFirst AS szUserFirst,
UALog.dDateOfAction AS dAction,
UALC.szComment,
mapAudit.bisavailable
FROM UserActionLog UALog WITH (NOLOCK)
INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID
INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType
INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID
INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID
INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID
INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID
INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID
LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice
AND p.lID = mapPO.lPatient
LEFT JOIN MAP_AuditActionTypeTableToMappingTable mapAudit WITH (NOLOCK) ON mapAudit.lUserAction = UALog.lUserAction --Added by Roger for Audit
AND mapAudit.lEnterprise = ' + CAST( @lEnterprise AS VARCHAR)
+' AND mapAudit.lUserActionType = UALog.lUserActionType '
+ ' LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog
WHERE 1=1 ' + @sSQLSearch
+ ' AND o.lEnterprise = ' + CAST( @lEnterprise AS VARCHAR)
+ ' AND ISNULL(mapAudit.bIsavailable, 1) = 1 ' + ' ORDER BY dAction'
exec sp_executesql @sSQLSelect
END
ELSE
BEGIN
SET @sSQLSelect = @sSQLSelect +
' SELECT
UALog.*,
p.szFirst,
p.szLast,
p.szChartNum,
o.szOfficeName,
UA.szAction,
UAT.szType + '' '' + UA.szAction AS szFullDescription,
UALog.dDateOfAction AS dAction,
u.szLast AS szUserLast,
u.szFirst AS szUserFirst,
UALC.szComment,
mapAudit.bIsavailable
FROM UserActionLog UALog WITH (NOLOCK)
INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID
INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType
INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID
INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID
INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID
INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID
LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice
AND p.lID = mapPO.lPatient
INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID
LEFT JOIN MAP_AuditActionTypeTableToMappingTable mapAudit WITH (NOLOCK) ON mapAudit.lUserAction = UALog.lUserAction --Added by Roger for Audit
AND mapAudit.lEnterprise = ' + CAST( @lEnterprise as varchar )
+' AND mapAudit.lUserActionType = UALog.lUserActionType '
+' INNER JOIN UserIdentifier e WITH (NOLOCK) ON p.lid = e.lPatient
AND e.lUserIdentifierType = ' + CAST (@lIdentifierType AS VARCHAR)
+ ' AND e.nStatus = 1 '
+ ' AND e.sValue = ''' + @szIdentifierValue +''''
+ ' LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog
WHERE 1=1 ' + @sSQLSearch
+ ' AND o.lEnterprise = ' + CAST( @lEnterprise AS VARCHAR)
+ ' AND ISNULL(mapAudit.bIsavailable, 1) = 1 ' + ' ORDER BY dAction'
exec sp_executesql @sSQLSelect
END
END
ELSE
BEGIN
IF @szIdentifierValue = ''
BEGIN
SET @sSQLSelect = @sSQLSelect +
' SELECT
UALog.*,
p.szFirst,
p.szLast,
p.szChartNum,
o.szOfficeName,
UA.szAction,
UAT.szType + '' '' + UA.szAction AS szFullDescription,
u.szLast AS szUserLast,
u.szFirst AS szUserFirst,
UALog.dDateOfAction AS dAction,
UALC.szComment,
mapAudit.bIsavailable
FROM UserActionLog UALog WITH (NOLOCK)
INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID
INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType
INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID
INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID
INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID
INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID
INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID
LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice
AND p.lID = mapPO.lPatient
LEFT JOIN MAP_AuditActionTypeTableToMappingTable mapAudit WITH (NOLOCK) ON mapAudit.lUserAction = UALog.lUserAction --Added by Roger for Audit
AND mapAudit.lEnterprise = '+ CAST (@lEnterprise AS VARCHAR)
+ ' AND mapAudit.lUserActionType = UALog.lUserActionType '
SET @sSQLSelect = @sSQLSelect +
' LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog '
SET @sSQLSelect = @sSQLSelect +
' WHERE map.lUser = '+ CAST( @lUser AS VARCHAR)+ @sSQLSearch
+ ' AND o.lEnterprise = ' + CAST( @lEnterprise AS VARCHAR)
+ ' AND ISNULL(mapAudit.bIsavailable, 1) = 1 ' + ' ORDER BY dAction'
exec sp_executesql @sSQLSelect
END
ELSE
BEGIN
SET @sSQLSelect = @sSQLSelect +
' SELECT
UALog.*,
p.szFirst,
p.szLast,
p.szChartNum,
o.szOfficeName,
UA.szAction,
UAT.szType + '' '' + UA.szAction AS szFullDescription,
u.szLast AS szUserLast,
u.szFirst AS szUserFirst,
UALog.dDateOfAction AS dAction,
UALC.szComment,
mapAudit.bIsavailable
FROM UserActionLog UALog WITH (NOLOCK)
INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID
INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType
INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID
INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID
INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID
INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID
LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice
AND p.lID = mapPO.lPatient
INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID
INNER JOIN UserIdentifier e WITH (NOLOCK) ON p.lid = e.lPatient
AND e.lUserIdentifierType = ' + CAST (@lIdentifierType AS VARCHAR)
+ ' AND e.nStatus = 1 '
+ ' AND e.sValue = ''' + @szIdentifierValue +''''
+' LEFT JOIN MAP_AuditActionTypeTableToMappingTable mapAudit WITH (NOLOCK) ON mapAudit.lUserAction = UALog.lUserAction
AND mapAudit.lEnterprise = ' + CAST( @lEnterprise AS VARCHAR)
+' AND mapAudit.lUserActionType = UALog.lUserActionType '
SET @sSQLSelect = @sSQLSelect +
' LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog '
SET @sSQLSelect = @sSQLSelect +
+ ' WHERE map.lUser = ' + CAST (@lUser AS VARCHAR) + @sSQLSearch
+ ' AND o.lEnterprise = ' + CAST( @lEnterprise AS VARCHAR)
+ ' AND ISNULL(mapAudit.bIsavailable, 1) = 1 ' + ' ORDER BY dAction'
exec sp_executesql @sSQLSelect
END
END
END --with audit record
ELSE
BEGIN --without audit record
IF @lUser = 0
BEGIN
IF @szIdentifierValue = ''
BEGIN
SET @sSQLSelect = @sSQLSelect +
' SELECT
UALog.*,
p.szFirst,
p.szLast,
p.szChartNum,
o.szOfficeName,
UA.szAction,
UAT.szType + '' '' + UA.szAction AS szFullDescription,
u.szLast AS szUserLast,
u.szFirst AS szUserFirst,
UALog.dDateOfAction AS dAction,
UALC.szComment
FROM UserActionLog UALog WITH (NOLOCK)
INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID
INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType
INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID
INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID
INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID
INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID
INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID
LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice
AND p.lID = mapPO.lPatient
LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog
WHERE 1=1 ' + @sSQLSearch
+' AND o.lEnterprise = ' + CAST( @lEnterprise AS VARCHAR) + ' ORDER BY dAction'
exec sp_executesql @sSQLSelect
END
ELSE
BEGIN
PRINT 'SIX'
SET @sSQLSelect = @sSQLSelect +
' SELECT
UALog.*,
p.szFirst,
p.szLast,
p.szChartNum,
o.szOfficeName,
UA.szAction,
UAT.szType + '' '' + UA.szAction AS szFullDescription,
UALog.dDateOfAction AS dAction,
u.szLast AS szUserLast,
u.szFirst AS szUserFirst,
UALC.szComment
FROM UserActionLog UALog WITH (NOLOCK)
INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID
INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType
INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID
INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID
INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID
INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID
LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice
AND p.lID = mapPO.lPatient
INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID
INNER JOIN UserIdentifier e WITH (NOLOCK) ON p.lid = e.lPatient
AND e.lUserIdentifierType = ' + CAST (@lIdentifierType AS VARCHAR)
+ ' AND e.nStatus = 1 '
+ ' AND e.sValue = ''' + @szIdentifierValue +''''
+ ' LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog
WHERE 1=1 ' + @sSQLSearch
+' AND o.lEnterprise = ' + CAST(@lEnterprise AS VARCHAR) + ' ORDER BY dAction'
exec sp_executesql @sSQLSelect
END
END
ELSE
BEGIN
IF @szIdentifierValue = ''
BEGIN
PRINT 'SEVEN'
SET @sSQLSelect = @sSQLSelect +
' SELECT
UALog.*,
p.szFirst,
p.szLast,
p.szChartNum,
o.szOfficeName,
UA.szAction,
UAT.szType + '' '' + UA.szAction AS szFullDescription,
UALog.dDateOfAction AS dAction,
u.szLast AS szUserLast,
u.szFirst AS szUserFirst,
UALC.szComment
FROM UserActionLog UALog WITH (NOLOCK)
INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID
INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType
INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID
INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID
INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID
INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID
INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID
LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice
AND p.lID = mapPO.lPatient
LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog
WHERE map.lUser = '+ CAST( @lUser AS VARCHAR)+ @sSQLSearch
+' AND o.lEnterprise = ' + CAST(@lEnterprise AS VARCHAR) + ' ORDER BY dAction'
exec sp_executesql @sSQLSelect
END
ELSE
BEGIN
SET @sSQLSelect = @sSQLSelect +
' SELECT
UALog.*,
p.szFirst,
p.szLast,
p.szChartNum,
o.szOfficeName,
UA.szAction,
UAT.szType + '' '' + UA.szAction AS szFullDescription,
u.szLast AS szUserLast,
u.szFirst AS szUserFirst,
UALog.dDateOfAction AS dAction,
UALC.szComment
FROM UserActionLog UALog WITH (NOLOCK)
INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID
INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType
INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID
INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID
INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID
INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID
LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice
AND p.lID = mapPO.lPatient
INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID
INNER JOIN UserIdentifier e WITH (NOLOCK) ON p.lid = e.lPatient
AND e.lUserIdentifierType = ' + CAST ( @lIdentifierType AS VARCHAR )
+' AND e.nStatus = 1 '
+ ' AND e.sValue = ''' + @szIdentifierValue +''''
+ ' LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog
WHERE map.lUser = '+ CAST( @lUser AS VARCHAR)+ @sSQLSearch
+' AND o.lEnterprise = ' + CAST(@lEnterprise AS VARCHAR) + ' ORDER BY dAction'
exec sp_executesql @sSQLSelect
END
END
END --without audit record

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

huum Right there with Babe Points: 743 More actions · Answer 1

Here is the Execution Plan

<?xml version="1.0" encoding="utf-16"?>

<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.0.4000.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

<BatchSequence>

<Batch>

<Statements>

<StmtSimple StatementCompId="3" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0216381" StatementText="if exists (select lid from MAP_AuditActionTypeTableToMappingTable WITH (NOLOCK) where luserAction=83 and lUserActionType=5 and lEnterprise=@lenterprise) " StatementType="COND WITH QUERY" QueryHash="0xDC5AF5D2851D9884" QueryPlanHash="0x038AFEF6EE12ADAE">

<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

<QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="27" CompileCPU="4" CompileMemory="600">

<RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0216381">

<OutputList>

<ColumnReference Column="Expr1003" />

</OutputList>

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1003" />

<ScalarOperator ScalarString="CASE WHEN [Expr1004] THEN (1) ELSE (0) END">

<IF>

<Condition>

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1004" />

</Identifier>

</ScalarOperator>

</Condition>

<Then>

<ScalarOperator>

<Const ConstValue="(1)" />

</ScalarOperator>

</Then>

<Else>

<ScalarOperator>

<Const ConstValue="(0)" />

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.021638">

<OutputList>

<ColumnReference Column="Expr1004" />

</OutputList>

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<NestedLoops Optimized="false">

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1004" />

</DefinedValue>

</DefinedValues>

<ProbeColumn>

<ColumnReference Column="Expr1004" />

</ProbeColumn>

<RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Constant Scan" NodeId="2" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">

<OutputList />

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<ConstantScan />

</RelOp>

<RelOp AvgRowSize="19" EstimateCPU="0.0043645" EstimateIO="0.0127546" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0171191" TableCardinality="3825">

<OutputList />

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">

<DefinedValues />

<Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Index="[PK_MAP_AuditActionTypeTableToMappingTable]" IndexKind="Clustered" />

<Predicate>

<ScalarOperator ScalarString="[myNIC_PROD].[dbo].[MAP_AuditActionTypeTableToMappingTable].[lEnterprise]=[@lEnterprise] AND [myNIC_PROD].[dbo].[MAP_AuditActionTypeTableToMappingTable].[luserAction]=(83) AND [myNIC_PROD].[dbo].[MAP_AuditActionTypeTableToMappingTable].[lUserActionType]=(5)">

<Logical Operation="AND">

<ScalarOperator>

<Compare CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Column="lEnterprise" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Identifier>

<ColumnReference Column="@lEnterprise" />

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

<ScalarOperator>

<Compare CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Column="luserAction" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="(83)" />

</ScalarOperator>

</Compare>

</ScalarOperator>

<ScalarOperator>

<Compare CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Column="lUserActionType" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="(5)" />

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</ComputeScalar>

</RelOp>

<ParameterList>

<ColumnReference Column="@lEnterprise" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />

</ParameterList>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

<Batch>

<Statements>

<StmtSimple StatementCompId="6" StatementEstRows="1" StatementId="2" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0216381" StatementText="if exists (select lid from MAP_AuditActionTypeTableToMappingTable WITH (NOLOCK) where luserAction=84 and lUserActionType=5 and lEnterprise=@lenterprise) " StatementType="COND WITH QUERY" QueryHash="0xDC5AF5D2851D9884" QueryPlanHash="0x038AFEF6EE12ADAE">

<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

<QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="1" CompileCPU="1" CompileMemory="600">

<RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0216381">

<OutputList>

<ColumnReference Column="Expr1003" />

</OutputList>

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1003" />

<ScalarOperator ScalarString="CASE WHEN [Expr1004] THEN (1) ELSE (0) END">

<IF>

<Condition>

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1004" />

</Identifier>

</ScalarOperator>

</Condition>

<Then>

<ScalarOperator>

<Const ConstValue="(1)" />

</ScalarOperator>

</Then>

<Else>

<ScalarOperator>

<Const ConstValue="(0)" />

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.021638">

<OutputList>

<ColumnReference Column="Expr1004" />

</OutputList>

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<NestedLoops Optimized="false">

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1004" />

</DefinedValue>

</DefinedValues>

<ProbeColumn>

<ColumnReference Column="Expr1004" />

</ProbeColumn>

<RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Constant Scan" NodeId="2" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">

<OutputList />

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<ConstantScan />

</RelOp>

<RelOp AvgRowSize="19" EstimateCPU="0.0043645" EstimateIO="0.0127546" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0171191" TableCardinality="3825">

<OutputList />

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />

</RunTimeInformation>

<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">

<DefinedValues />

<Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Index="[PK_MAP_AuditActionTypeTableToMappingTable]" IndexKind="Clustered" />

<Predicate>

<ScalarOperator ScalarString="[myNIC_PROD].[dbo].[MAP_AuditActionTypeTableToMappingTable].[lEnterprise]=[@lEnterprise] AND [myNIC_PROD].[dbo].[MAP_AuditActionTypeTableToMappingTable].[luserAction]=(84) AND [myNIC_PROD].[dbo].[MAP_AuditActionTypeTableToMappingTable].[lUserActionType]=(5)">

<Logical Operation="AND">

<ScalarOperator>

<Compare CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Column="lEnterprise" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Identifier>

<ColumnReference Column="@lEnterprise" />

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

<ScalarOperator>

<Compare CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Column="luserAction" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="(84)" />

</ScalarOperator>

</Compare>

</ScalarOperator>

<ScalarOperator>

<Compare CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Column="lUserActionType" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="(5)" />

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</ComputeScalar>

</RelOp>

<ParameterList>

<ColumnReference Column="@lEnterprise" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />

</ParameterList>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

<Batch>

<Statements>

<StmtSimple StatementCompId="7" StatementEstRows="1" StatementId="3" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.0278088" StatementText="update MAP_AuditActionTypeTableToMappingTable set bIsavailable=1 where luserAction=84 and lUserActionType=5 and lEnterprise=@lenterprise " StatementType="UPDATE" QueryHash="0x1B6C1BEC408FEB58" QueryPlanHash="0x86E393B3923A1877">

<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

<QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="656">

<RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Update" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.0278088">

<OutputList />

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<Update DMLRequestSort="false">

<Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Index="[PK_MAP_AuditActionTypeTableToMappingTable]" IndexKind="Clustered" />

<SetPredicate>

<ScalarOperator ScalarString="[myNIC_PROD].[dbo].[MAP_AuditActionTypeTableToMappingTable].[bIsavailable] = [Expr1003]">

<ScalarExpressionList>

<ScalarOperator>

<MultipleAssign>

<Assign>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Column="bIsavailable" />

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1003" />

</Identifier>

</ScalarOperator>

</Assign>

</MultipleAssign>

</ScalarOperator>

</ScalarExpressionList>

</ScalarOperator>

</SetPredicate>

<RelOp AvgRowSize="15" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0178078">

<OutputList>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Column="lid" />

<ColumnReference Column="Expr1003" />

</OutputList>

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1003" />

<ScalarOperator ScalarString="(1)">

<Const ConstValue="(1)" />

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="2" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0178077">

<OutputList>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Column="lid" />

</OutputList>

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<Top RowCount="true" IsPercent="false" WithTies="false">

<TopExpression>

<ScalarOperator ScalarString="(0)">

<Const ConstValue="(0)" />

</ScalarOperator>

</TopExpression>

<RelOp AvgRowSize="23" EstimateCPU="0.0043645" EstimateIO="0.0127546" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0171191" TableCardinality="3825">

<OutputList>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Column="lid" />

</OutputList>

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<IndexScan Ordered="true" ForcedIndex="false" NoExpandHint="false">

<DefinedValues>

<DefinedValue>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Column="lid" />

</DefinedValue>

</DefinedValues>

<Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Index="[PK_MAP_AuditActionTypeTableToMappingTable]" IndexKind="Clustered" />

<Predicate>

<ScalarOperator ScalarString="[myNIC_PROD].[dbo].[MAP_AuditActionTypeTableToMappingTable].[lEnterprise]=[@lEnterprise] AND [myNIC_PROD].[dbo].[MAP_AuditActionTypeTableToMappingTable].[luserAction]=(84) AND [myNIC_PROD].[dbo].[MAP_AuditActionTypeTableToMappingTable].[lUserActionType]=(5)">

<Logical Operation="AND">

<ScalarOperator>

<Compare CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Column="lEnterprise" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Identifier>

<ColumnReference Column="@lEnterprise" />

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

<ScalarOperator>

<Compare CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Column="luserAction" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="(84)" />

</ScalarOperator>

</Compare>

</ScalarOperator>

<ScalarOperator>

<Compare CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Column="lUserActionType" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="(5)" />

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</Top>

</RelOp>

</ComputeScalar>

</RelOp>

</Update>

</RelOp>

<ParameterList>

<ColumnReference Column="@lEnterprise" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />

</ParameterList>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

<Batch>

<Statements>

<StmtSimple StatementCompId="51" StatementEstRows="1" StatementId="4" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00335413" StatementText="IF EXISTS ( SELECT TOP 1 lid FROM MAP_AuditActionTypeTableToMappingTable WITH (NOLOCK) WHERE lEnterprise = @lEnterprise ) " StatementType="COND WITH QUERY" QueryHash="0x1FABBE50746740AD" QueryPlanHash="0x58FB7D06F7DE6E69">

<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

<QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="576">

<RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00335413">

<OutputList>

<ColumnReference Column="Expr1003" />

</OutputList>

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1003" />

<ScalarOperator ScalarString="CASE WHEN [Expr1004] THEN (1) ELSE (0) END">

<IF>

<Condition>

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1004" />

</Identifier>

</ScalarOperator>

</Condition>

<Then>

<ScalarOperator>

<Const ConstValue="(1)" />

</ScalarOperator>

</Then>

<Else>

<ScalarOperator>

<Const ConstValue="(0)" />

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00335403">

<OutputList>

<ColumnReference Column="Expr1004" />

</OutputList>

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<NestedLoops Optimized="false">

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1004" />

</DefinedValue>

</DefinedValues>

<ProbeColumn>

<ColumnReference Column="Expr1004" />

</ProbeColumn>

<RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Constant Scan" NodeId="2" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">

<OutputList />

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<ConstantScan />

</RelOp>

<RelOp AvgRowSize="11" EstimateCPU="0.0043645" EstimateIO="0.0127546" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00334059" TableCardinality="3825">

<OutputList />

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />

</RunTimeInformation>

<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">

<DefinedValues />

<Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Index="[PK_MAP_AuditActionTypeTableToMappingTable]" IndexKind="Clustered" />

<Predicate>

<ScalarOperator ScalarString="[myNIC_PROD].[dbo].[MAP_AuditActionTypeTableToMappingTable].[lEnterprise]=[@lEnterprise]">

<Compare CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Column="lEnterprise" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Identifier>

<ColumnReference Column="@lEnterprise" />

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</ComputeScalar>

</RelOp>

<ParameterList>

<ColumnReference Column="@lEnterprise" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />

</ParameterList>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

<Batch>

<Statements>

<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

<QueryPlan DegreeOfParallelism="1" MemoryGrant="2080" CachedPlanSize="168" CompileTime="142" CompileCPU="64" CompileMemory="2432">

<MissingIndexes>

<MissingIndexGroup Impact="66.7222">

<MissingIndex Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]">

<ColumnGroup Usage="INEQUALITY">

<Column Name="[dDateOfAction]" ColumnId="5" />

</ColumnGroup>

<ColumnGroup Usage="INCLUDE">

<Column Name="[lid]" ColumnId="1" />

<Column Name="[lMap_UserToOffice]" ColumnId="2" />

<Column Name="[lUserAction]" ColumnId="3" />

<Column Name="[lUserActionType]" ColumnId="4" />

<Column Name="[lPatient]" ColumnId="6" />

<Column Name="[lOffset]" ColumnId="7" />

</ColumnGroup>

</MissingIndex>

</MissingIndexGroup>

<MissingIndexGroup Impact="87.0249">

<MissingIndex Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]">

<ColumnGroup Usage="EQUALITY">

<Column Name="[lMap_UserToOffice]" ColumnId="2" />

</ColumnGroup>

<ColumnGroup Usage="INEQUALITY">

<Column Name="[dDateOfAction]" ColumnId="5" />

</ColumnGroup>

<ColumnGroup Usage="INCLUDE">

<Column Name="[lid]" ColumnId="1" />

<Column Name="[lUserAction]" ColumnId="3" />

<Column Name="[lUserActionType]" ColumnId="4" />

<Column Name="[lPatient]" ColumnId="6" />

<Column Name="[lOffset]" ColumnId="7" />

</ColumnGroup>

</MissingIndex>

</MissingIndexGroup>

</MissingIndexes>

<RelOp AvgRowSize="715" EstimateCPU="9.67433E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9.67433" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.84546">

<OutputList>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lid" />

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lMap_UserToOffice" />

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lUserAction" />

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lUserActionType" />

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="dDateOfAction" />

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lPatient" />

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lOffset" />

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserAction]" Alias="[UA]" Column="szAction" />

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[Office]" Alias="[o]" Column="szOfficeName" />

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[User]" Alias="" Column="szFirst" />

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[User]" Alias="" Column="szLast" />

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="szFirst" />

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="szLast" />

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="szChartNum" />

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Alias="[mapAudit]" Column="bIsavailable" />

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLogComment]" Alias="[UALC]" Column="szComment" />

<ColumnReference Column="Expr1024" />

</OutputList>

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1024" />

<ScalarOperator ScalarString="[Expr1027]+[myNIC_PROD].[dbo].[UserAction].[szAction] as [UA].[szAction]">

<Arithmetic Operation="ADD">

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1027" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Identifier>

<ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserAction]" Alias="[UA]" Column="szAction" />

</Identifier>

</ScalarOperator>

</Arithmetic>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<RelOp AvgRowSize="615" EstimateCPU="4.04387E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9.67433" LogicalOp="Left Outer Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.84546">