none
stored procedure slow, thoughts?

    Question

  • exec NIC_ENTERPRISE_PatientActivityLogSearch  @lUser  = 208551,

                                                    @SDate                = '2013-07-22 00:00:00.000' ,

                                                    @EDate                = '2013-07-22 23:59:59.777' ,

                                                    @lEnterprise  = 2674

    It took 1 min in rush time and 15 seconds after 4:30 p.m


    k

    Tuesday, July 23, 2013 2:26 PM

Answers

All replies

  • Have you checked the execution plan of the stored procedure?

    Without the code, table + index structure no one could guess a resaon.


    Olaf Helper

    Blog Xing

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


    k


    • Edited by '''HuuM''' Tuesday, July 23, 2013 2:33 PM comments
    Tuesday, July 23, 2013 2:31 PM
  • exec NIC_ENTERPRISE_PatientActivityLogSearch  @lUser  = 208551,

                                                    @SDate                = '2013-07-22 00:00:00.000' ,

                                                    @EDate                = '2013-07-22 23:59:59.777' ,

                                                    @lEnterprise  = 2674

    It took 1 min in rush time and 15 seconds after 4:30 p.m


    k

    Obtain the execution plan and see where, if any, the bottleneck is.

    Why would it surprise you that the procedure would run longer during your busy period?

    Tuesday, July 23, 2013 2:32 PM
  • do  you think its normal as there are 199,092,593 records in a database.

    k

    Tuesday, July 23, 2013 2:47 PM
  • for a performance problem , first thing that anyone would ask is "Have you looked at the execution plan?"

    So have you? :)


    Satheesh

     
    Tuesday, July 23, 2013 2:47 PM
    Answerer
  • And posting entire code  in public forum could land you in trouble, So please be careful ....

    Satheesh

    Tuesday, July 23, 2013 2:50 PM
    Answerer

  • k

    Tuesday, July 23, 2013 3:04 PM
  • Only a part of the plan is visible :( 

    However you could check the highlighted part, looks like huge number of rows are returned, this could be avoided by tweaking indexes.

    Could you post the xml plan or saving it and upload rather than taking the screenshot? 


    Satheesh

    Tuesday, July 23, 2013 3:55 PM
    Answerer
  • <?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" />



    k

    Tuesday, July 23, 2013 5:30 PM
  • </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>


    k

    Tuesday, July 23, 2013 5:34 PM
  • <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>


    k

    Tuesday, July 23, 2013 5:35 PM
  •   <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>


    k

    Tuesday, July 23, 2013 5:37 PM
  • <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]&gt;=CONVERT_IMPLICIT(datetime,[@2],0) AND [myNIC_PROD].[dbo].[UserActionLog].[dDateOfAction] as [UALog].[dDateOfAction]&lt;=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>


    k

    Tuesday, July 23, 2013 5:37 PM

  • k

    Tuesday, July 23, 2013 7:23 PM

  • k

    Tuesday, July 23, 2013 7:23 PM
  • I have uploaded the of most costly execution below,

    Please comment


    k

    Tuesday, July 23, 2013 8:18 PM
  • what are the indexes present on table "UserActionLog"?

    Try creating this index which may have an impact 

    CREATE NONCLUSTERED INDEX [idx_userActionLog]
    ON [dbo].[UserActionLog] ([dDateOfAction])
    INCLUDE ([lid],[lMap_UserToOffice],[lUserAction],[lUserActionType],[lPatient],[lOffset])
    


    Satheesh

    Wednesday, July 24, 2013 4:15 AM
    Answerer
  • Its not helping, i re ran the execution plan but this time the stats are different

    k

    Wednesday, July 24, 2013 5:56 AM
  • Did the plan change after the index? Did you recompile the sp after index creation?

    Satheesh

    Wednesday, July 24, 2013 6:07 AM
    Answerer
  • yes it did change

    k

    Wednesday, July 24, 2013 1:06 PM

  • k

    Wednesday, July 24, 2013 1:43 PM
  • i have already attached the indexes snapshot

    k

    Wednesday, July 24, 2013 3:07 PM
  • okay, so it didn't improve really well?

    Could you run the statistics by setting below option on ?

    SET STATISTICS IO ON

    SET STATISTICS TIME ON


    Satheesh

    Wednesday, July 24, 2013 4:53 PM
    Answerer
  • sorry in did not understand about running the statistics?

    k

    Wednesday, July 24, 2013 5:08 PM
  • SQL Server parse and compile time: 
       CPU time = 0 ms, elapsed time = 0 ms.
    Table 'MAP_AuditActionTypeTableToMappingTable'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    (1 row(s) affected)
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 6 ms.
    Table 'MAP_AuditActionTypeTableToMappingTable'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    (1 row(s) affected)
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 42 ms.
    Table 'MAP_AuditActionTypeTableToMappingTable'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    (1 row(s) affected)
    
    (1 row(s) affected)
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 37 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'MAP_AuditActionTypeTableToMappingTable'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    SQL Server parse and compile time: 
       CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server parse and compile time: 
       CPU time = 0 ms, elapsed time = 0 ms.
    Table 'UserActionLogComment'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'MAP_AuditActionTypeTableToMappingTable'. Scan count 1, logical reads 37927, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'address'. Scan count 0, logical reads 4462, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'patient'. Scan count 0, logical reads 8926, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'UserAction'. Scan count 0, logical reads 4664, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'UserActionType'. Scan count 0, logical reads 6996, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Office'. Scan count 0, logical reads 4664, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Map_UserToOffice'. Scan count 0, logical reads 13810, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'UserActionLog'. Scan count 1, logical reads 20755, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'User'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
     SQL Server Execution Times:
       CPU time = 688 ms,  elapsed time = 4767 ms.
    
     SQL Server Execution Times:
       CPU time = 688 ms,  elapsed time = 4768 ms.
    
     SQL Server Execution Times:
       CPU time = 688 ms,  elapsed time = 5031 ms.
    SQL Server parse and compile time: 
       CPU time = 0 ms, elapsed time = 0 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    


    k

    Thursday, July 25, 2013 7:49 AM
  • So it took about 5 seconds to finish? Is that good/bad? 

    What is the amount of data it is retrieving?

    You could look at the red part, its having a high read there? is that justified(Are you retrieving/touching those many data)  ?


    Table 'UserActionLogComment'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'MAP_AuditActionTypeTableToMappingTable'. Scan count 1, logical reads 37927, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'address'. Scan count 0, logical reads 4462, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'patient'. Scan count 0, logical reads 8926, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'UserAction'. Scan count 0, logical reads 4664, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'UserActionType'. Scan count 0, logical reads 6996, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Office'. Scan count 0, logical reads 4664, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Map_UserToOffice'. Scan count 0, logical reads 13810, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'UserActionLog'. Scan count 1, logical reads 20755, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'User'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
     SQL Server Execution Times:


    Satheesh


    Thursday, July 25, 2013 1:00 PM
    Answerer
  • yes the table size is 300 Million Rows

    k

    Thursday, July 25, 2013 3:30 PM
  • Still an issue?

    Thanks!


    Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    • Marked as answer by '''HuuM''' Sunday, August 25, 2013 2:55 AM
    Saturday, August 03, 2013 9:35 AM
    Owner