SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Stored procedure working very slow Thoughts pls
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kshahzad
Starting Member

45 Posts

Posted - 07/23/2013 :  09:32:40  Show Profile  Reply with Quote
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

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 07/23/2013 :  13:11:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
What does the execution plan show? Any missing indexes? What does STATISTICS IO and TIME show?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

kshahzad
Starting Member

45 Posts

Posted - 07/23/2013 :  13:48:59  Show Profile  Reply with Quote
<?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)#xD;#xA;   " 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)#xD;#xA;   " 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#xD;#xA;#xD;#xA;#xD;#xA;#xD;#xA;#xD;" 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#xD;#xA;                        lid#xD;#xA;                FROM    MAP_AuditActionTypeTableToMappingTable   WITH (NOLOCK) #xD;#xA;                WHERE   lEnterprise = @lEnterprise ) #xD;#xA;       " 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>
        <StmtSimple StatementCompId="101" StatementEstRows="9.67433" StatementId="5" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" StatementSubTreeCost="1.84546" StatementText="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 and mapAudit . lEnterprise = @0 and mapAudit . lUserActionType = UALog . lUserActionType left join UserActionLogComment UALC with ( NOLOCK ) on UALog . lid = UALC . lUserActionLog where map . lUser = @1 and UALog . dDateOfAction between @2 and @3 and o . lEnterprise = @4 and ISNULL ( mapAudit . bIsavailable , @5 ) = @6 order by dAction" StatementType="SELECT" QueryHash="0x17859F37492DB7CE" QueryPlanHash="0x01FBE241A159950C">
          <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="[u]" Column="szFirst" />
                <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[User]" Alias="[u]" 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">
                  <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="[u]" Column="szFirst" />
                    <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[User]" Alias="[u]" 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="Expr1027" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="426" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <NestedLoops Optimized="false">
                    <Predicate>
                      <ScalarOperator ScalarString="[myNIC_PROD].[dbo].[UserActionLog].[lid] as [UALog].[lid]=[myNIC_PROD].[dbo].[UserActionLogComment].[lUserActionLog] as [UALC].[lUserActionLog]">
                        <Compare CompareOp="EQ">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lid" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLogComment]" Alias="[UALC]" Column="lUserActionLog" />
                            </Identifier>
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </Predicate>
                    <RelOp AvgRowSize="512" EstimateCPU="0.000149925" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9.67433" LogicalOp="Sort" NodeId="2" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1.84144">
                      <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="[u]" Column="szFirst" />
                        <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[User]" Alias="[u]" 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 Column="Expr1027" />
                      </OutputList>
                      <MemoryFractions Input="1" Output="1" />
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="426" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <Sort Distinct="false">
                        <OrderBy>
                          <OrderByColumn Ascending="true">
                            <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="dDateOfAction" />
                          </OrderByColumn>
                        </OrderBy>
                        <RelOp AvgRowSize="512" EstimateCPU="6.84957E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9.67433" LogicalOp="Filter" NodeId="3" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="1.83003">
                          <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="[u]" Column="szFirst" />
                            <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[User]" Alias="[u]" 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 Column="Expr1027" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="426" ActualEndOfScans="1" ActualExecutions="1" />
                          </RunTimeInformation>
                          <Filter StartupExpression="false">
                            <RelOp AvgRowSize="512" EstimateCPU="0.0227191" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10.0729" LogicalOp="Right Outer Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.83002">
                              <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="[u]" Column="szFirst" />
                                <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[User]" Alias="[u]" 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 Column="Expr1027" />
                              </OutputList>
                              <MemoryFractions Input="0" Output="0" />
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="1" ActualExecutions="1" />
                              </RunTimeInformation>
                              <Hash>
                                <DefinedValues />
                                <HashKeysBuild>
                                  <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Alias="[mapAudit]" Column="luserAction" />
                                  <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Alias="[mapAudit]" Column="lUserActionType" />
                                </HashKeysBuild>
                                <HashKeysProbe>
                                  <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lUserAction" />
                                  <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lUserActionType" />
                                </HashKeysProbe>
                                <ProbeResidual>
                                  <ScalarOperator ScalarString="[myNIC_PROD].[dbo].[MAP_AuditActionTypeTableToMappingTable].[luserAction] as [mapAudit].[luserAction]=[myNIC_PROD].[dbo].[UserActionLog].[lUserAction] as [UALog].[lUserAction] AND [myNIC_PROD].[dbo].[MAP_AuditActionTypeTableToMappingTable].[lUserActionType] as [mapAudit].[lUserActionType]=[myNIC_PROD].[dbo].[UserActionLog].[lUserActionType] as [UALog].[lUserActionType]">
                                    <Logical Operation="AND">
                                      <ScalarOperator>
                                        <Compare CompareOp="EQ">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Alias="[mapAudit]" Column="luserAction" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lUserAction" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Compare CompareOp="EQ">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Alias="[mapAudit]" Column="lUserActionType" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lUserActionType" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Logical>
                                  </ScalarOperator>
                                </ProbeResidual>
                                <RelOp AvgRowSize="23" EstimateCPU="0.0043645" EstimateIO="0.0127546" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="226.494" LogicalOp="Clustered Index Scan" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0171191" TableCardinality="3825">
                                  <OutputList>
                                    <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Alias="[mapAudit]" Column="luserAction" />
                                    <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Alias="[mapAudit]" Column="lUserActionType" />
                                    <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Alias="[mapAudit]" Column="bIsavailable" />
                                  </OutputList>
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="0" ActualRows="214" ActualEndOfScans="1" ActualExecutions="1" />
                                  </RunTimeInformation>
                                  <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Alias="[mapAudit]" Column="luserAction" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Alias="[mapAudit]" Column="lUserActionType" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Alias="[mapAudit]" Column="bIsavailable" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Index="[PK_MAP_AuditActionTypeTableToMappingTable]" Alias="[mapAudit]" IndexKind="Clustered" />
                                    <Predicate>
                                      <ScalarOperator ScalarString="[myNIC_PROD].[dbo].[MAP_AuditActionTypeTableToMappingTable].[lEnterprise] as [mapAudit].[lEnterprise]=[@0]">
                                        <Compare CompareOp="EQ">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Alias="[mapAudit]" Column="lEnterprise" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="@0" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Predicate>
                                  </IndexScan>
                                </RelOp>
                                <RelOp AvgRowSize="508" EstimateCPU="4.21047E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10.0729" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.78834">
                                  <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="[u]" Column="szFirst" />
                                    <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[User]" Alias="[u]" 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 Column="Expr1027" />
                                  </OutputList>
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="1" ActualExecutions="1" />
                                  </RunTimeInformation>
                                  <NestedLoops Optimized="false">
                                    <RelOp AvgRowSize="113" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="993">
                                      <OutputList>
                                        <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[User]" Alias="[u]" Column="szFirst" />
                                        <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[User]" Alias="[u]" Column="szLast" />
                                      </OutputList>
                                      <RunTimeInformation>
                                        <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
                                      </RunTimeInformation>
                                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                        <DefinedValues>
                                          <DefinedValue>
                                            <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[User]" Alias="[u]" Column="szFirst" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[User]" Alias="[u]" Column="szLast" />
                                          </DefinedValue>
                                        </DefinedValues>
                                        <Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[User]" Index="[PK_User]" Alias="[u]" IndexKind="Clustered" />
                                        <SeekPredicates>
                                          <SeekPredicateNew>
                                            <SeekKeys>
                                              <Prefix ScanType="EQ">
                                                <RangeColumns>
                                                  <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[User]" Alias="[u]" Column="lID" />
                                                </RangeColumns>
                                                <RangeExpressions>
                                                  <ScalarOperator ScalarString="[@1]">
                                                    <Identifier>
                                                      <ColumnReference Column="@1" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </RangeExpressions>
                                              </Prefix>
                                            </SeekKeys>
                                          </SeekPredicateNew>
                                        </SeekPredicates>
                                      </IndexScan>
                                    </RelOp>
                                    <RelOp AvgRowSize="404" EstimateCPU="4.21047E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10.0729" LogicalOp="Inner Join" NodeId="8" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.78501">
                                      <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="[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 Column="Expr1027" />
                                      </OutputList>
                                      <RunTimeInformation>
                                        <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="1" ActualExecutions="1" />
                                      </RunTimeInformation>
                                      <NestedLoops Optimized="false">
                                        <OuterReferences>
                                          <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[Map_UserToOffice]" Alias="[map]" Column="lOffice" />
                                        </OuterReferences>
                                        <RelOp AvgRowSize="206" EstimateCPU="1.00729E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10.0729" LogicalOp="Compute Scalar" NodeId="9" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.77482">
                                          <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="[Map_UserToOffice]" Alias="[map]" Column="lOffice" />
                                            <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 Column="Expr1027" />
                                          </OutputList>
                                          <ComputeScalar>
                                            <DefinedValues>
                                              <DefinedValue>
                                                <ColumnReference Column="Expr1027" />
                                                <ScalarOperator ScalarString="[myNIC_PROD].[dbo].[UserActionType].[szType] as [UAT].[szType]+' '">
                                                  <Arithmetic Operation="ADD">
                                                    <ScalarOperator>
                                                      <Identifier>
                                                        <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionType]" Alias="[UAT]" Column="szType" />
                                                      </Identifier>
                                                    </ScalarOperator>
                                                    <ScalarOperator>
                                                      <Const ConstValue="' '" />
                                                    </ScalarOperator>
                                                  </Arithmetic>
                                                </ScalarOperator>
                                              </DefinedValue>
                                            </DefinedValues>
                                            <RelOp AvgRowSize="206" EstimateCPU="4.21047E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10.0729" LogicalOp="Inner Join" NodeId="10" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.77481">
                                              <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="[UserActionType]" Alias="[UAT]" Column="szType" />
                                                <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[Map_UserToOffice]" Alias="[map]" Column="lOffice" />
                                                <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" />
                                              </OutputList>
                                              <RunTimeInformation>
                                                <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="1" ActualExecutions="1" />
                                              </RunTimeInformation>
                                              <NestedLoops Optimized="false">
                                                <OuterReferences>
                                                  <ColumnReference Column="Bmk1004" />
                                                </OuterReferences>
                                                <RelOp AvgRowSize="187" EstimateCPU="4.21047E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10.0729" LogicalOp="Inner Join" NodeId="11" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.77005">
                                                  <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 Column="Bmk1004" />
                                                    <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[Map_UserToOffice]" Alias="[map]" Column="lOffice" />
                                                    <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" />
                                                  </OutputList>
                                                  <RunTimeInformation>
                                                    <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="1" ActualExecutions="1" />
                                                  </RunTimeInformation>
                                                  <NestedLoops Optimized="false">
                                                    <OuterReferences>
                                                      <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lUserActionType" />
                                                    </OuterReferences>
                                                    <RelOp AvgRowSize="179" EstimateCPU="4.21047E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10.0729" LogicalOp="Inner Join" NodeId="12" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.76529">
                                                      <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="[Map_UserToOffice]" Alias="[map]" Column="lOffice" />
                                                        <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" />
                                                      </OutputList>
                                                      <RunTimeInformation>
                                                        <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="1" ActualExecutions="1" />
                                                      </RunTimeInformation>
                                                      <NestedLoops Optimized="false">
                                                        <OuterReferences>
                                                          <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lUserAction" />
                                                        </OuterReferences>
                                                        <RelOp AvgRowSize="77" EstimateCPU="4.75636E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10.0729" LogicalOp="Inner Join" NodeId="13" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.75742">
                                                          <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="[Map_UserToOffice]" Alias="[map]" Column="lOffice" />
                                                            <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" />
                                                          </OutputList>
                                                          <RunTimeInformation>
                                                            <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="1" ActualExecutions="1" />
                                                          </RunTimeInformation>
                                                          <NestedLoops Optimized="false">
                                                            <OuterReferences>
                                                              <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="lAddress" />
                                                            </OuterReferences>
                                                            <RelOp AvgRowSize="81" EstimateCPU="4.75636E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11.3788" LogicalOp="Inner Join" NodeId="14" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.72357">
                                                              <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="[Map_UserToOffice]" Alias="[map]" Column="lOffice" />
                                                                <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="lAddress" />
                                                                <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="szChartNum" />
                                                              </OutputList>
                                                              <RunTimeInformation>
                                                                <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="1" ActualExecutions="1" />
                                                              </RunTimeInformation>
                                                              <NestedLoops Optimized="false">
                                                                <OuterReferences>
                                                                  <ColumnReference Column="Uniq1015" />
                                                                  <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="szLast" />
                                                                </OuterReferences>
                                                                <RelOp AvgRowSize="57" EstimateCPU="5.61148E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11.3788" LogicalOp="Inner Join" NodeId="15" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.69001">
                                                                  <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="[Map_UserToOffice]" Alias="[map]" Column="lOffice" />
                                                                    <ColumnReference Column="Uniq1015" />
                                                                    <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="szLast" />
                                                                  </OutputList>
                                                                  <RunTimeInformation>
                                                                    <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="1" ActualExecutions="1" />
                                                                  </RunTimeInformation>
                                                                  <NestedLoops Optimized="false">
                                                                    <OuterReferences>
                                                                      <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lPatient" />
                                                                    </OuterReferences>
                                                                    <RelOp AvgRowSize="43" EstimateCPU="0.0211492" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="13.4246" LogicalOp="Inner Join" NodeId="16" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.6615">
                                                                      <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="[Map_UserToOffice]" Alias="[map]" Column="lOffice" />
                                                                      </OutputList>
                                                                      <MemoryFractions Input="0" Output="0" />
                                                                      <RunTimeInformation>
                                                                        <RunTimeCountersPerThread Thread="0" ActualRows="601" ActualEndOfScans="1" ActualExecutions="1" />
                                                                      </RunTimeInformation>
                                                                      <Hash>
                                                                        <DefinedValues />
                                                                        <HashKeysBuild>
                                                                          <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[Map_UserToOffice]" Alias="[map]" Column="lID" />
                                                                        </HashKeysBuild>
                                                                        <HashKeysProbe>
                                                                          <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lMap_UserToOffice" />
                                                                        </HashKeysProbe>
                                                                        <RelOp AvgRowSize="19" EstimateCPU="0.0019753" EstimateIO="0.00979167" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8.02914" LogicalOp="Clustered Index Scan" NodeId="17" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.011767" TableCardinality="1653">
                                                                          <OutputList>
                                                                            <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[Map_UserToOffice]" Alias="[map]" Column="lID" />
                                                                            <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[Map_UserToOffice]" Alias="[map]" Column="lOffice" />
                                                                          </OutputList>
                                                                          <RunTimeInformation>
                                                                            <RunTimeCountersPerThread Thread="0" ActualRows="8" ActualEndOfScans="1" ActualExecutions="1" />
                                                                          </RunTimeInformation>
                                                                          <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                                                                            <DefinedValues>
                                                                              <DefinedValue>
                                                                                <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[Map_UserToOffice]" Alias="[map]" Column="lID" />
                                                                              </DefinedValue>
                                                                              <DefinedValue>
                                                                                <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[Map_UserToOffice]" Alias="[map]" Column="lOffice" />
                                                                              </DefinedValue>
                                                                            </DefinedValues>
                                                                            <Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[Map_UserToOffice]" Index="[PK_Map_UserToOffice]" Alias="[map]" IndexKind="Clustered" />
                                                                            <Predicate>
                                                                              <ScalarOperator ScalarString="[myNIC_PROD].[dbo].[Map_UserToOffice].[lUser] as [map].[lUser]=[@1]">
                                                                                <Compare CompareOp="EQ">
                                                                                  <ScalarOperator>
                                                                                    <Identifier>
                                                                                      <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[Map_UserToOffice]" Alias="[map]" Column="lUser" />
                                                                                    </Identifier>
                                                                                  </ScalarOperator>
                                                                                  <ScalarOperator>
                                                                                    <Identifier>
                                                                                      <ColumnReference Column="@1" />
                                                                                    </Identifier>
                                                                                  </ScalarOperator>
                                                                                </Compare>
                                                                              </ScalarOperator>
                                                                            </Predicate>
                                                                          </IndexScan>
                                                                        </RelOp>
                                                                        <RelOp AvgRowSize="39" EstimateCPU="0.306364" EstimateIO="1.07646" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="720.327" LogicalOp="Clustered Index Scan" NodeId="18" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1.38282" TableCardinality="278370">
                                                                          <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" />
                                                                          </OutputList>
                                                                          <RunTimeInformation>
                                                                            <RunTimeCountersPerThread Thread="0" ActualRows="1057" ActualEndOfScans="1" ActualExecutions="1" />
                                                                          </RunTimeInformation>
                                                                          <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                                                                            <DefinedValues>
                                                                              <DefinedValue>
                                                                                <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lid" />
                                                                              </DefinedValue>
                                                                              <DefinedValue>
                                                                                <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lMap_UserToOffice" />
                                                                              </DefinedValue>
                                                                              <DefinedValue>
                                                                                <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lUserAction" />
                                                                              </DefinedValue>
                                                                              <DefinedValue>
                                                                                <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lUserActionType" />
                                                                              </DefinedValue>
                                                                              <DefinedValue>
                                                                                <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="dDateOfAction" />
                                                                              </DefinedValue>
                                                                              <DefinedValue>
                                                                                <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lPatient" />
                                                                              </DefinedValue>
                                                                              <DefinedValue>
                                                                                <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lOffset" />
                                                                              </DefinedValue>
                                                                            </DefinedValues>
                                                                            <Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Index="[PK_UserActionLog]" Alias="[UALog]" IndexKind="Clustered" />
                                                                            <Predicate>
                                                                              <ScalarOperator ScalarString="[myNIC_PROD].[dbo].[UserActionLog].[dDateOfAction] as [UALog].[dDateOfAction]>=CONVERT_IMPLICIT(datetime,[@2],0) AND [myNIC_PROD].[dbo].[UserActionLog].[dDateOfAction] as [UALog].[dDateOfAction]<=CONVERT_IMPLICIT(datetime,[@3],0)">
                                                                                <Logical Operation="AND">
                                                                                  <ScalarOperator>
                                                                                    <Compare CompareOp="GE">
                                                                                      <ScalarOperator>
                                                                                        <Identifier>
                                                                                          <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="dDateOfAction" />
                                                                                        </Identifier>
                                                                                      </ScalarOperator>
                                                                                      <ScalarOperator>
                                                                                        <Identifier>
                                                                                          <ColumnReference Column="ConstExpr1025">
                                                                                            <ScalarOperator>
                                                                                              <Convert DataType="datetime" Style="0" Implicit="true">
                                                                                                <ScalarOperator>
                                                                                                  <Identifier>
                                                                                                    <ColumnReference Column="@2" />
                                                                                                  </Identifier>
                                                                                                </ScalarOperator>
                                                                                              </Convert>
                                                                                            </ScalarOperator>
                                                                                          </ColumnReference>
                                                                                        </Identifier>
                                                                                      </ScalarOperator>
                                                                                    </Compare>
                                                                                  </ScalarOperator>
                                                                                  <ScalarOperator>
                                                                                    <Compare CompareOp="LE">
                                                                                      <ScalarOperator>
                                                                                        <Identifier>
                                                                                          <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="dDateOfAction" />
                                                                                        </Identifier>
                                                                                      </ScalarOperator>
                                                                                      <ScalarOperator>
                                                                                        <Identifier>
                                                                                          <ColumnReference Column="ConstExpr1026">
                                                                                            <ScalarOperator>
                                                                                              <Convert DataType="datetime" Style="0" Implicit="true">
                                                                                                <ScalarOperator>
                                                                                                  <Identifier>
                                                                                                    <ColumnReference Column="@3" />
                                                                                                  </Identifier>
                                                                                                </ScalarOperator>
                                                                                              </Convert>
                                                                                            </ScalarOperator>
                                                                                          </ColumnReference>
                                                                                        </Identifier>
                                                                                      </ScalarOperator>
                                                                                    </Compare>
                                                                                  </ScalarOperator>
                                                                                </Logical>
                                                                              </ScalarOperator>
                                                                            </Predicate>
                                                                          </IndexScan>
                                                                        </RelOp>
                                                                      </Hash>
                                                                    </RelOp>
                                                                    <RelOp AvgRowSize="20" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="12.4246" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="20" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0284505" TableCardinality="3156">
                                                                      <OutputList>
                                                                        <ColumnReference Column="Uniq1015" />
                                                                        <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="szLast" />
                                                                      </OutputList>
                                                                      <RunTimeInformation>
                                                                        <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="151" ActualExecutions="601" />
                                                                      </RunTimeInformation>
                                                                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                                                        <DefinedValues>
                                                                          <DefinedValue>
                                                                            <ColumnReference Column="Uniq1015" />
                                                                          </DefinedValue>
                                                                          <DefinedValue>
                                                                            <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="szLast" />
                                                                          </DefinedValue>
                                                                        </DefinedValues>
                                                                        <Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Index="[PK_patient]" Alias="[p]" IndexKind="NonClustered" />
                                                                        <SeekPredicates>
                                                                          <SeekPredicateNew>
                                                                            <SeekKeys>
                                                                              <Prefix ScanType="EQ">
                                                                                <RangeColumns>
                                                                                  <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="lID" />
                                                                                </RangeColumns>
                                                                                <RangeExpressions>
                                                                                  <ScalarOperator ScalarString="[myNIC_PROD].[dbo].[UserActionLog].[lPatient] as [UALog].[lPatient]">
                                                                                    <Identifier>
                                                                                      <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lPatient" />
                                                                                    </Identifier>
                                                                                  </ScalarOperator>
                                                                                </RangeExpressions>
                                                                              </Prefix>
                                                                            </SeekKeys>
                                                                          </SeekPredicateNew>
                                                                        </SeekPredicates>
                                                                      </IndexScan>
                                                                    </RelOp>
                                                                  </NestedLoops>
                                                                </RelOp>
                                                                <RelOp AvgRowSize="37" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="10.378" EstimateRewinds="0.000861352" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="22" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0335156" TableCardinality="3156">
                                                                  <OutputList>
                                                                    <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="szFirst" />
                                                                    <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="lAddress" />
                                                                    <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="szChartNum" />
                                                                  </OutputList>
                                                                  <RunTimeInformation>
                                                                    <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="0" ActualExecutions="450" />
                                                                  </RunTimeInformation>
                                                                  <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                                                    <DefinedValues>
                                                                      <DefinedValue>
                                                                        <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="szFirst" />
                                                                      </DefinedValue>
                                                                      <DefinedValue>
                                                                        <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="lAddress" />
                                                                      </DefinedValue>
                                                                      <DefinedValue>
                                                                        <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="szChartNum" />
                                                                      </DefinedValue>
                                                                    </DefinedValues>
                                                                    <Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Index="[IX_patient_1_szLast]" Alias="[p]" TableReferenceId="-1" IndexKind="Clustered" />
                                                                    <SeekPredicates>
                                                                      <SeekPredicateNew>
                                                                        <SeekKeys>
                                                                          <Prefix ScanType="EQ">
                                                                            <RangeColumns>
                                                                              <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="szLast" />
                                                                              <ColumnReference Column="Uniq1015" />
                                                                            </RangeColumns>
                                                                            <RangeExpressions>
                                                                              <ScalarOperator ScalarString="[myNIC_PROD].[dbo].[patient].[szLast] as [p].[szLast]">
                                                                                <Identifier>
                                                                                  <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="szLast" />
                                                                                </Identifier>
                                                                              </ScalarOperator>
                                                                              <ScalarOperator ScalarString="[Uniq1015]">
                                                                                <Identifier>
                                                                                  <ColumnReference Column="Uniq1015" />
                                                                                </Identifier>
                                                                              </ScalarOperator>
                                                                            </RangeExpressions>
                                                                          </Prefix>
                                                                        </SeekKeys>
                                                                      </SeekPredicateNew>
                                                                    </SeekPredicates>
                                                                  </IndexScan>
                                                                </RelOp>
                                                              </NestedLoops>
                                                            </RelOp>
                                                            <RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="10.3788" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="30" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0338039" TableCardinality="22487">
                                                              <OutputList />
                                                              <RunTimeInformation>
                                                                <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="0" ActualExecutions="450" />
                                                              </RunTimeInformation>
                                                              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                                                <DefinedValues />
                                                                <Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[address]" Index="[PK_address]" Alias="[a]" IndexKind="Clustered" />
                                                                <SeekPredicates>
                                                                  <SeekPredicateNew>
                                                                    <SeekKeys>
                                                                      <Prefix ScanType="EQ">
                                                                        <RangeColumns>
                                                                          <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[address]" Alias="[a]" Column="lID" />
                                                                        </RangeColumns>
                                                                        <RangeExpressions>
                                                                          <ScalarOperator ScalarString="[myNIC_PROD].[dbo].[patient].[lAddress] as [p].[lAddress]">
                                                                            <Identifier>
                                                                              <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[patient]" Alias="[p]" Column="lAddress" />
                                                                            </Identifier>
                                                                          </ScalarOperator>
                                                                        </RangeExpressions>
                                                                      </Prefix>
                                                                    </SeekKeys>
                                                                  </SeekPredicateNew>
                                                                </SeekPredicates>
                                                              </IndexScan>
                                                            </RelOp>
                                                          </NestedLoops>
                                                        </RelOp>
                                                        <RelOp AvgRowSize="111" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="9.07289" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="31" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00783092" TableCardinality="132">
                                                          <OutputList>
                                                            <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserAction]" Alias="[UA]" Column="szAction" />
                                                          </OutputList>
                                                          <RunTimeInformation>
                                                            <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="0" ActualExecutions="450" />
                                                          </RunTimeInformation>
                                                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                                            <DefinedValues>
                                                              <DefinedValue>
                                                                <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserAction]" Alias="[UA]" Column="szAction" />
                                                              </DefinedValue>
                                                            </DefinedValues>
                                                            <Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserAction]" Index="[PK_UserAction]" Alias="[UA]" IndexKind="Clustered" />
                                                            <SeekPredicates>
                                                              <SeekPredicateNew>
                                                                <SeekKeys>
                                                                  <Prefix ScanType="EQ">
                                                                    <RangeColumns>
                                                                      <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserAction]" Alias="[UA]" Column="lid" />
                                                                    </RangeColumns>
                                                                    <RangeExpressions>
                                                                      <ScalarOperator ScalarString="[myNIC_PROD].[dbo].[UserActionLog].[lUserAction] as [UALog].[lUserAction]">
                                                                        <Identifier>
                                                                          <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lUserAction" />
                                                                        </Identifier>
                                                                      </ScalarOperator>
                                                                    </RangeExpressions>
                                                                  </Prefix>
                                                                </SeekKeys>
                                                              </SeekPredicateNew>
                                                            </SeekPredicates>
                                                          </IndexScan>
                                                        </RelOp>
                                                      </NestedLoops>
                                                    </RelOp>
                                                    <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="8.72463" EstimateRewinds="0.34826" EstimateRows="1" LogicalOp="Index Seek" NodeId="32" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00471752" TableCardinality="20">
                                                      <OutputList>
                                                        <ColumnReference Column="Bmk1004" />
                                                      </OutputList>
                                                      <RunTimeInformation>
                                                        <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="0" ActualExecutions="450" />
                                                      </RunTimeInformation>
                                                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                                        <DefinedValues>
                                                          <DefinedValue>
                                                            <ColumnReference Column="Bmk1004" />
                                                          </DefinedValue>
                                                        </DefinedValues>
                                                        <Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionType]" Index="[PK_UserActionType]" Alias="[UAT]" IndexKind="NonClustered" />
                                                        <SeekPredicates>
                                                          <SeekPredicateNew>
                                                            <SeekKeys>
                                                              <Prefix ScanType="EQ">
                                                                <RangeColumns>
                                                                  <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionType]" Alias="[UAT]" Column="lid" />
                                                                </RangeColumns>
                                                                <RangeExpressions>
                                                                  <ScalarOperator ScalarString="[myNIC_PROD].[dbo].[UserActionLog].[lUserActionType] as [UALog].[lUserActionType]">
                                                                    <Identifier>
                                                                      <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLog]" Alias="[UALog]" Column="lUserActionType" />
                                                                    </Identifier>
                                                                  </ScalarOperator>
                                                                </RangeExpressions>
                                                              </Prefix>
                                                            </SeekKeys>
                                                          </SeekPredicateNew>
                                                        </SeekPredicates>
                                                      </IndexScan>
                                                    </RelOp>
                                                  </NestedLoops>
                                                </RelOp>
                                                <RelOp AvgRowSize="36" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="8.72463" EstimateRewinds="0.34826" EstimateRows="1" LogicalOp="RID Lookup" NodeId="34" Parallel="false" PhysicalOp="RID Lookup" EstimatedTotalSubtreeCost="0.00471752" TableCardinality="20">
                                                  <OutputList>
                                                    <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionType]" Alias="[UAT]" Column="szType" />
                                                  </OutputList>
                                                  <RunTimeInformation>
                                                    <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="0" ActualExecutions="450" />
                                                  </RunTimeInformation>
                                                  <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                                    <DefinedValues>
                                                      <DefinedValue>
                                                        <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionType]" Alias="[UAT]" Column="szType" />
                                                      </DefinedValue>
                                                    </DefinedValues>
                                                    <Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionType]" Alias="[UAT]" TableReferenceId="-1" IndexKind="Heap" />
                                                    <SeekPredicates>
                                                      <SeekPredicateNew>
                                                        <SeekKeys>
                                                          <Prefix ScanType="EQ">
                                                            <RangeColumns>
                                                              <ColumnReference Column="Bmk1004" />
                                                            </RangeColumns>
                                                            <RangeExpressions>
                                                              <ScalarOperator ScalarString="[Bmk1004]">
                                                                <Identifier>
                                                                  <ColumnReference Column="Bmk1004" />
                                                                </Identifier>
                                                              </ScalarOperator>
                                                            </RangeExpressions>
                                                          </Prefix>
                                                        </SeekKeys>
                                                      </SeekPredicateNew>
                                                    </SeekPredicates>
                                                  </IndexScan>
                                                </RelOp>
                                              </NestedLoops>
                                            </RelOp>
                                          </ComputeScalar>
                                        </RelOp>
                                        <RelOp AvgRowSize="215" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="8.40091" EstimateRewinds="0.671984" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="41" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0101526" TableCardinality="104">
                                          <OutputList>
                                            <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[Office]" Alias="[o]" Column="szOfficeName" />
                                          </OutputList>
                                          <RunTimeInformation>
                                            <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="0" ActualExecutions="450" />
                                          </RunTimeInformation>
                                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                            <DefinedValues>
                                              <DefinedValue>
                                                <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[Office]" Alias="[o]" Column="szOfficeName" />
                                              </DefinedValue>
                                            </DefinedValues>
                                            <Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[Office]" Index="[PK_Office]" Alias="[o]" IndexKind="Clustered" />
                                            <SeekPredicates>
                                              <SeekPredicateNew>
                                                <SeekKeys>
                                                  <Prefix ScanType="EQ">
                                                    <RangeColumns>
                                                      <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[Office]" Alias="[o]" Column="lID" />
                                                    </RangeColumns>
                                                    <RangeExpressions>
                                                      <ScalarOperator ScalarString="[myNIC_PROD].[dbo].[Map_UserToOffice].[lOffice] as [map].[lOffice]">
                                                        <Identifier>
                                                          <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[Map_UserToOffice]" Alias="[map]" Column="lOffice" />
                                                        </Identifier>
                                                      </ScalarOperator>
                                                    </RangeExpressions>
                                                  </Prefix>
                                                </SeekKeys>
                                              </SeekPredicateNew>
                                            </SeekPredicates>
                                            <Predicate>
                                              <ScalarOperator ScalarString="[myNIC_PROD].[dbo].[Office].[lEnterprise] as [o].[lEnterprise]=[@4]">
                                                <Compare CompareOp="EQ">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[Office]" Alias="[o]" Column="lEnterprise" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Column="@4" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Compare>
                                              </ScalarOperator>
                                            </Predicate>
                                          </IndexScan>
                                        </RelOp>
                                      </NestedLoops>
                                    </RelOp>
                                  </NestedLoops>
                                </RelOp>
                              </Hash>
                            </RelOp>
                            <Predicate>
                              <ScalarOperator ScalarString="isnull([myNIC_PROD].[dbo].[MAP_AuditActionTypeTableToMappingTable].[bIsavailable] as [mapAudit].[bIsavailable],[@5])=[@6]">
                                <Compare CompareOp="EQ">
                                  <ScalarOperator>
                                    <Intrinsic FunctionName="isnull">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[MAP_AuditActionTypeTableToMappingTable]" Alias="[mapAudit]" Column="bIsavailable" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Column="@5" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </Intrinsic>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="@6" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </Predicate>
                          </Filter>
                        </RelOp>
                      </Sort>
                    </RelOp>
                    <RelOp AvgRowSize="115" EstimateCPU="7.96E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="8.67433" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="44" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00397358" TableCardinality="0">
                      <OutputList>
                        <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLogComment]" Alias="[UALC]" Column="lUserActionLog" />
                        <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLogComment]" Alias="[UALC]" Column="szComment" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="426" ActualExecutions="426" />
                      </RunTimeInformation>
                      <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLogComment]" Alias="[UALC]" Column="lUserActionLog" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLogComment]" Alias="[UALC]" Column="szComment" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[myNIC_PROD]" Schema="[dbo]" Table="[UserActionLogComment]" Index="[PK_UserActionLogComment]" Alias="[UALC]" IndexKind="Clustered" />
                      </IndexScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </ComputeScalar>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@6" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
              <ColumnReference Column="@5" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
              <ColumnReference Column="@4" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
              <ColumnReference Column="@3" ParameterCompiledValue="'Jul 23 2013 11:59PM'" ParameterRuntimeValue="'Jul 23 2013 11:59PM'" />
              <ColumnReference Column="@2" ParameterCompiledValue="'Jul  1 2013 12:00AM'" ParameterRuntimeValue="'Jul  1 2013 12:00AM'" />
              <ColumnReference Column="@1" ParameterCompiledValue="(300044)" ParameterRuntimeValue="(300044)" />
              <ColumnReference Column="@0" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Edited by - kshahzad on 07/23/2013 13:49:59
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 9.64 seconds. Powered By: Snitz Forums 2000