Suggestions on tuning this stored procedure

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

Suggestions on tuning this stored procedure

Author

Topic

kshahzad
Starting Member

45 Posts

Posted - 2013-07-18 : 16:56:28

[code]USE [myNIC_PROD]
GO
/****** Object: StoredProcedure [dbo].[NIC_REPORTS_GetReferal] Script Date: 07/18/2013 20:51:38 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[NIC_REPORTS_GetReferal]
@lPhysician int,
@lOffice int,
@SDate datetime,
@EDate datetime

AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @lEnterprise int
SELECT @lEnterprise=lEnterprise FROM Office WHERE lID=@lOffice

/*
select b.szlast + ', ' + b.szfirst as szPhysicianName, c.szlast + ', ' + c.szfirst as szPatientName,
a.dDateCreated, a.dCheckForReport, a.nPriority
from consultants a, physician b, patient c
where
a.lphysician = b.lid
and a.lphysician in (select lPhysician from map_physiciantooffice where loffice = @lOffice)
and c.lid in (select lpatient from address where lid = a.lpatientaddress)
and DATEDIFF(d, @SDate, a.dDateCreated) >= 0
and DATEDIFF(d, @EDate, a.dDateCreated) <= 0
order by a.dDateCreated, szPatientName, a.nPriority desc
*/

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname,
ap.szfirst + ' ' + ap.szlast as szphysicianname, ap.szorganization as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a
INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
INNER JOIN Office o ON PN.lOffice=o.lID
Join patient p on p.lid=PN.lPatient
JOIN alliedHealthProviders AP ON a.lAlliedProvider=AP.lid
LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where a.nRecordStatus=1
AND DATEDIFF(d, @SDate, a.dDateCreated) >= 0
AND DATEDIFF(d, @EDate, a.dDateCreated) <= 0
AND o.lID=@lOffice
AND a.dLastPrinted IS NOT NULL

UNION

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname,
r.szfirst + ' ' + r.szlast as szphysicianname, r.szdepartment as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a
INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
INNER JOIN Office o ON PN.lOffice=o.lID
Join patient p on p.lid=PN.lPatient
JOIN Map_ConsultantsToRolodex d ON a.lID = d.lConsultants
JOIN Rolodex r ON r.lID = d.lRolodex
LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where a.nRecordStatus=1
and DATEDIFF(d, @SDate, a.dDateCreated) >= 0
and DATEDIFF(d, @EDate, a.dDateCreated) <= 0
AND o.lID=@lOffice
AND a.dLastPrinted IS NOT NULL

UNION

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname,
ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a
INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
INNER JOIN Office o ON PN.lOffice=o.lID
Join patient p on p.lid=PN.lPatient
JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants
JOIN state s on s.lid = d.lDirectoryState
JOIN directoryAB ds on ds.lid = d.lDirectory
LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where s.szDirectoryTableName = 'directoryab'
and a.nRecordStatus=1
and DATEDIFF(d, @SDate, a.dDateCreated) >= 0
and DATEDIFF(d, @EDate, a.dDateCreated) <= 0
AND o.lID=@lOffice
AND a.dLastPrinted IS NOT NULL

UNION

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname,
ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a
INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
INNER JOIN Office o ON PN.lOffice=o.lID
Join patient p on p.lid=PN.lPatient
JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants
JOIN state s on s.lid = d.lDirectoryState
JOIN directorybc ds on ds.lid = d.lDirectory
LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where s.szDirectoryTableName = 'directorybc'
and a.nRecordStatus=1
and DATEDIFF(d, @SDate, a.dDateCreated) >= 0
and DATEDIFF(d, @EDate, a.dDateCreated) <= 0
AND o.lID=@lOffice
AND a.dLastPrinted IS NOT NULL

UNION

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname,
ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a
INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
INNER JOIN Office o ON PN.lOffice=o.lID
Join patient p on p.lid=PN.lPatient
JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants
JOIN state s on s.lid = d.lDirectoryState
JOIN directorynb ds on ds.lid = d.lDirectory
LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where s.szDirectoryTableName = 'directorynb'
and a.nRecordStatus=1
and DATEDIFF(d, @SDate, a.dDateCreated) >= 0
and DATEDIFF(d, @EDate, a.dDateCreated) <= 0
AND o.lID=@lOffice
AND a.dLastPrinted IS NOT NULL

UNION

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname,
ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a
INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
INNER JOIN Office o ON PN.lOffice=o.lID
Join patient p on p.lid=PN.lPatient
JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants
JOIN state s on s.lid = d.lDirectoryState
JOIN directoryns ds on ds.lid = d.lDirectory
LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where s.szDirectoryTableName = 'directoryns'
and a.nRecordStatus=1
and DATEDIFF(d, @SDate, a.dDateCreated) >= 0
and DATEDIFF(d, @EDate, a.dDateCreated) <= 0
AND o.lID=@lOffice
AND a.dLastPrinted IS NOT NULL

UNION

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname,
ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a
INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
INNER JOIN Office o ON PN.lOffice=o.lID
Join patient p on p.lid=PN.lPatient
JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants
JOIN state s on s.lid = d.lDirectoryState
JOIN directoryon ds on ds.lid = d.lDirectory
LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where s.szDirectoryTableName = 'directoryon'
and a.nRecordStatus=1
and DATEDIFF(d, @SDate, a.dDateCreated) >= 0
and DATEDIFF(d, @EDate, a.dDateCreated) <= 0
AND o.lID=@lOffice
AND a.dLastPrinted IS NOT NULL

UNION

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname,
ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a
INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
INNER JOIN Office o ON PN.lOffice=o.lID
Join patient p on p.lid=PN.lPatient
JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants
JOIN state s on s.lid = d.lDirectoryState
JOIN directorysk ds on ds.lid = d.lDirectory
LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where s.szDirectoryTableName = 'directorysk'
and a.nRecordStatus=1
and DATEDIFF(d, @SDate, a.dDateCreated) >= 0
and DATEDIFF(d, @EDate, a.dDateCreated) <= 0
AND o.lID=@lOffice
AND a.dLastPrinted IS NOT NULL
order by szPatientName, a.dDateCreated, szfacility, a.nPriority desc
[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-18 : 17:51:17

Same questions that I asked in your other thread...

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 : 16:09:52

i can see the execution plan ,

but i am not sure how to pull stats for particular stored procedure?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources