July 18, 2013 at 2:56 pm
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]
@lPhysicianint,
@lOfficeint,
@SDatedatetime,
@EDatedatetime
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
July 18, 2013 at 3:05 pm
Much like your other post, if you want help with tuning you need to give us enough information to work with. Follow the advice about what to post from the link Gail posted in your other thread.
Actual execution plan
Table definition
Indexes definition
Also when posting code it will make it a lot easier to read if you can use the IFCode shortcuts. They are over on the left when you are posting. If you use the CODE one it will keep your formatting and coloring in addition to putting it inside the nice little scrolling window.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 18, 2013 at 4:26 pm
As Sean says, there is not much we can do with the query alone.
However, this:
and DATEDIFF(d, @SDate, a.dDateCreated) >= 0
and DATEDIFF(d, @EDate, a.dDateCreated) <= 0
is better written as
and a.dDateCreated >= @SDate
and a.dDateCreated < dateadd(DAY, 1, @EDate)
As this permits use of any index on dDateCreated. Although, guessing from the table names, the effect may be limited.
You could change UNION to UNION ALL, as UNION implies DISTINCT. Then again, if you need duplicates deleted, you should keep UNION.
I would suggest that you run one of the UNION queries at a time, to isolate the slow part.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 18, 2013 at 4:29 pm
I forgot: Change SET ANSI_NULLS OFF to ANSI_NULLS ON. ANSI_NULLS OFF is a legacy setting, and there are features in SQL Server that are unavailable when this setting is OFF.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply