Phew!!! That is a lot of unformatted code to read.

CREATE PROCEDURE dbo.NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS @lPatient INT,

@nRecordStatus INT,

@nClassLevel INT = 5,

@nSecurityLevel INT = 0,

@lMap_UserToOffice INT = 0

AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN

DECLARE @lUser INT,

@lUserActionMappingTable INT

SELECT @luser = lUser

FROM map_userToOffice

WHERE lid = @lMap_UserToOffice

SET @lUserActionMappingTable = 32

--Disable the AOS SECURITY FOR CPP

IF @nSecurityLevel = 2

SET @nSecurityLevel = 1

IF @lMap_UserToOffice > 0

AND @nSecurityLevel >= 2 -- if there is a user and masking is on(bit 1=enabled masking)

BEGIN

-------------

DECLARE @dCurDate DATETIME,

@lOffset INT

--set @dCurDate=getDate()-- may need to adjust this for users timezone

EXEC SESSION_GetUserDate @lUser = @luser,

@dUserDate = @dCurDate OUTPUT,

@lOffset = @lOffset OUTPUT --This gets the date in the users timezone

/*

Get the permission ID for this section....

*/

DECLARE @lCustodianPermission INT

SELECT @lCustodianPermission = lid

FROM Custodian_Permissions

WHERE szcode = 'CPP'

DECLARE @PhysiciansAccess TABLE (lPhysician INT PRIMARY KEY)

INSERT INTO @PhysiciansAccess (lPhysician)

SELECT m.lPhysician

FROM map_UserToCustodian m

INNER JOIN map_UserToCustodian_permissions cp ON cp.lmap_UserToCustodian = m.lid

AND cp.lCustodianpermission = @lCustodianPermission

WHERE m.lUser = @luser

AND m.nRecordStatus = 1

AND m.bAllPatient = 1

AND isNull(m.dStartDate, '1900-01-01') <= @dCurDate

AND isnull(m.dEndDate, '9000-01-01') >= @dCurDate

UNION

SELECT lPhysician

FROM map_UserToCustodian m

INNER JOIN map_UserToCustodian_Per_patient p ON p.lmap_UserToCustodian = m.lid

INNER JOIN map_UserToCustodian_permissions cp ON cp.lmap_UserToCustodian_Per_Patient = p.lid

AND lCustodianpermission = @lCustodianPermission

WHERE lUser = @luser

AND m.nRecordStatus = 1

AND p.nRecordStatus = 1

AND bAllPatient = 0

AND p.lPatient = @lPatient

AND isNull(p.dStartDate, '1900-01-01') <= @dCurDate

AND isNull(p.dEndDate, '9000-01-01') >= @dCurDate --only get if the expiry dates are valid

--select * from @PhysiciansAccess

DECLARE @OfficeAccess TABLE (lOffice INT PRIMARY KEY)

/*

go get all offices that the patient is a member of

filter out offices that this patient has a custodain and that custodain did not allow access to this user

Note: there is no need to filter by section for this query. The Section has been filtered above, and is used in the @PhysiciansAccess list

NOTE: the default values for office is -1 if the information was recorded without an office. This means that it is always accessable.

*/

INSERT INTO @OfficeAccess (lOffice)

VALUES (0)

INSERT INTO @OfficeAccess (lOffice)

SELECT map.loffice

FROM map_PatientToOffice map

INNER JOIN map_PatientAtOfficeToCustodian mapc ON mapc.lpatient = map.lpatient

AND mapc.loffice = map.loffice

INNER JOIN @PhysiciansAccess PA ON PA.lphysician = mapc.lPhysician

WHERE map.lpatient = @lPatient

--and PA.lPhysician is not null

UNION

SELECT map.loffice

FROM map_PatientToOffice map

LEFT JOIN map_PatientAtOfficeToCustodian mapc ON mapc.lpatient = map.lpatient

AND mapc.loffice = map.loffice

WHERE map.lpatient = @lPatient

AND isNull(mapc.lPhysician, 0) <= 0

-----------

SELECT a.*,

b.szDescription,

p.dDateOrdered,

p.dDateSignedOff,

p.szLaboratory,

p.dDateCollected,

CASE

WHEN EXISTS (

SELECT *

FROM mask_role

INNER JOIN map_UserAtOfficeToRole mapRole ON mapRole.lUserRole = mask_Role.lRole

INNER JOIN map_userTOoffice map ON map.lid = mapRole.lMap_userToOffice

WHERE mask_role.nMappingTable = @lUserActionMappingTable

AND map.lUser = @luser

AND mask_role.lRowID = p.lid

AND mask_role.nMaskStatus = 1

)

THEN 1

WHEN Mu.lid IS NOT NULL

THEN 1

ELSE 0

END AS bMask

FROM [PhysicianLabReportsTests] a

INNER JOIN [PhysicianLabReports] p ON p.lID = a.lLabReport

INNER JOIN [LaboratoryTest] b ON b.lID = a.lPhysicianLabTest

LEFT JOIN Mask_User MU ON MU.nMappingTable = @lUserActionMappingTable

AND MU.lUser = @luser

AND MU.lRowID = p.lid

AND MU.nMaskStatus = 1

LEFT JOIN @PhysiciansAccess PA ON (PA.lPhysician = p.lPhysician) --LEFT Join for all the physicains the user has access to ALL patient data recorded under that physician

LEFT JOIN @OfficeAccess O ON O.loffice = p.loffice --LEFT Join for all the offices the user has access to ALL patient data in that office

WHERE (

p.lPatient = @lPatient

AND p.bSignedOff = 1

AND p.szFiledBy <> 'auto'

AND a.nRecordStatus = @nRecordStatus

AND p.nclassificationlevel >= @nClassLevel

AND DateDiff(d, p.dDateCollected, (

SELECT Max(y.dDateCollected)

FROM [PhysicianLabReports] y

INNER JOIN [PhysicianLabReportsTests] z ON z.lLabReport = y.lID

WHERE y.lPatient = @lPatient

AND z.lPhysicianLabTest = a.lPhysicianLabTest

AND z.nRecordStatus = @nRecordStatus

AND y.bSignedOff = 1

)) = 0

)

OR (

p.lPatient = @lPatient

-- AND p.bSignedOff=1

AND p.szFiledBy = 'auto'

AND a.bAdd2CPP = 1

AND a.nRecordStatus = @nRecordStatus

AND p.nclassificationlevel >= @nClassLevel

AND DateDiff(d, p.dDateCollected, (

SELECT Max(y.dDateCollected)

FROM [PhysicianLabReports] y

INNER JOIN [PhysicianLabReportsTests] z ON z.lLabReport = y.lID

WHERE y.lPatient = @lPatient

AND z.lPhysicianLabTest = a.lPhysicianLabTest

AND z.nRecordStatus = @nRecordStatus

AND Z.bAdd2CPP = 1

-- AND y.bSignedOff=1

)) = 0

)

AND isnull(PA.lPhysician, O.lOffice) IS NOT NULL --Only return records if EITHER of the left joins returned a matching record

ORDER BY b.szDescription

END

ELSE

IF @lMap_UserToOffice > 0

AND @nSecurityLevel >= 1 -- if there is a user and masking is on(bit 1=enabled masking)

BEGIN

SELECT a.*,

b.szDescription,

p.dDateOrdered,

p.dDateSignedOff,

p.szLaboratory,

p.dDateCollected,

CASE

WHEN EXISTS (

SELECT *

FROM mask_role

INNER JOIN map_UserAtOfficeToRole mapRole ON mapRole.lUserRole = mask_Role.lRole

INNER JOIN map_userTOoffice map ON map.lid = mapRole.lMap_userToOffice

WHERE mask_role.nMappingTable = @lUserActionMappingTable

AND map.lUser = @luser

AND mask_role.lRowID = p.lid

AND mask_role.nMaskStatus = 1

)

THEN 1

WHEN Mu.lid IS NOT NULL

THEN 1

ELSE 0

END AS bMask

FROM [PhysicianLabReportsTests] a

INNER JOIN [PhysicianLabReports] p ON p.lID = a.lLabReport

INNER JOIN [LaboratoryTest] b ON b.lID = a.lPhysicianLabTest

LEFT JOIN Mask_User MU ON MU.nMappingTable = @lUserActionMappingTable

AND MU.lUser = @luser

AND MU.lRowID = p.lid

AND MU.nMaskStatus = 1

WHERE (

p.lPatient = @lPatient

AND p.bSignedOff = 1

AND p.szFiledBy <> 'auto'

AND a.nRecordStatus = @nRecordStatus

AND p.nclassificationlevel >= @nClassLevel

AND DateDiff(d, p.dDateCollected, (

SELECT Max(y.dDateCollected)

FROM [PhysicianLabReports] y

INNER JOIN [PhysicianLabReportsTests] z ON z.lLabReport = y.lID

WHERE y.lPatient = @lPatient

AND z.lPhysicianLabTest = a.lPhysicianLabTest

AND z.nRecordStatus = @nRecordStatus

AND y.bSignedOff = 1

)) = 0

)

OR (

p.lPatient = @lPatient

-- AND p.bSignedOff=1

AND p.szFiledBy = 'auto'

AND a.bAdd2CPP = 1

AND a.nRecordStatus = @nRecordStatus

AND p.nclassificationlevel >= @nClassLevel

AND DateDiff(d, p.dDateCollected, (

SELECT Max(y.dDateCollected)

FROM [PhysicianLabReports] y

INNER JOIN [PhysicianLabReportsTests] z ON z.lLabReport = y.lID

WHERE y.lPatient = @lPatient

AND z.lPhysicianLabTest = a.lPhysicianLabTest

AND z.nRecordStatus = @nRecordStatus

AND Z.bAdd2CPP = 1

-- AND y.bSignedOff=1

)) = 0

)

ORDER BY b.szDescription

END

ELSE

BEGIN

SELECT a.*,

b.szDescription,

p.dDateOrdered,

p.dDateSignedOff,

p.szLaboratory,

0 AS bMask,

p.dDateCollected

FROM [PhysicianLabReportsTests] a

INNER JOIN [PhysicianLabReports] p ON p.lID = a.lLabReport

INNER JOIN [LaboratoryTest] b ON b.lID = a.lPhysicianLabTest

WHERE (

p.lPatient = @lPatient

AND p.bSignedOff = 1

AND p.szFiledBy <> 'auto'

AND a.nRecordStatus = @nRecordStatus

AND DateDiff(d, p.dDateCollected, (

SELECT Max(y.dDateCollected)

FROM [PhysicianLabReports] y

INNER JOIN [PhysicianLabReportsTests] z ON z.lLabReport = y.lID

WHERE y.lPatient = @lPatient

AND z.lPhysicianLabTest = a.lPhysicianLabTest

AND z.nRecordStatus = @nRecordStatus

AND y.bSignedOff = 1

)) = 0

)

OR (

p.lPatient = @lPatient

-- AND p.bSignedOff=1

AND p.szFiledBy = 'auto'

AND a.bAdd2CPP = 1

AND a.nRecordStatus = @nRecordStatus

AND DateDiff(d, p.dDateCollected, (

SELECT Max(y.dDateCollected)

FROM [PhysicianLabReports] y

INNER JOIN [PhysicianLabReportsTests] z ON z.lLabReport = y.lID

WHERE y.lPatient = @lPatient

AND z.lPhysicianLabTest = a.lPhysicianLabTest

AND z.nRecordStatus = @nRecordStatus

AND Z.bAdd2CPP = 1

-- AND y.bSignedOff=1

)) = 0

)

ORDER BY b.szDescription

END

END

GO

There seems to be a number of things that might cause this to be slow. You have a number of UNION, you also have a number of nonSARGable predicates. To be honest your queries that are unioned seem to be unnecessary. They are the exact same query except that the second one has additional filtering requirements. That means that ALL the rows in the second are already there in the first query.

All that aside the biggest performance issue is because you have multiple execution paths. That means that based on certain conditions you execute entirely different blocks of code.

Check out Gail's article about this topic and some of the best ways to overcome this.

http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]