Stored procedure working very slow

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

All Forums

General SQL Server Forums

New to SQL Server Administration

Stored procedure working very slow

Author

Topic

kshahzad
Starting Member

45 Posts

Posted - 2013-07-18 : 15:16:30

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
JOIN [PhysicianLabReports] p ON p.lID=a.lLabReport
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
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
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
JOIN [PhysicianLabReports] p ON p.lID=a.lLabReport
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
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
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
JOIN [PhysicianLabReports] p ON p.lID=a.lLabReport
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
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
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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-18 : 15:47:43

What does statistics IO and time show? What does the execution plan show? Any missing indexes? Are stats out-dated?

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

Subscribe to my blog

kshahzad
Starting Member

45 Posts

Posted - 2013-07-18 : 15:55:57

IO are slow and since this SP is runnng on Microsoft SQL Enterprise Manager Version: 8.0, so i cant collect execution plan.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-18 : 17:52:04

You CAN collect an execution plan on that version, which is SQL Server 2000.

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

Subscribe to my blog

kshahzad
Starting Member

45 Posts

Posted - 2013-07-19 : 13:28:18

Sorry i could not figure out how to collect execution plan of a stored procedure on a run time,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-19 : 15:08:11

I don't have any SQL Server 2000 servers as we upgraded years ago, but I know for sure the option is there in Query Analyzer. It's up there in the menus somewhere, likely under Query.

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources