more efficient procedure, speed it up

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

SQL Server 2008 Forums

Transact-SQL (2008)

more efficient procedure, speed it up

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-05-04 : 16:28:12

does anyone have any suggestions on how to make this a more efficient procedure, or to speed it up please.

create PROCEDURE [dbo].[NYP_ld_triage_eff_msrs_rpt]
( @StartDate datetime,
@EndDate datetime)

AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN
--cd.PatCareDocGUID IN '9000001670102020',Nursing OB Triage History;'4000001085202001',Nursing OB Triage History;'1000001197202001'OB Triage Note - Allen
--loc.ParentGUID in( 2000001000061001, 4000001145061001 , 8000001069061001)

DECLARE
@start_date datetime,
@end_date datetime

--use convert to mm/dd/yyyy with default Start_time 12:00:00 and default End_time 11:59:59
SELECT @start_date = convert(datetime,convert(varchar(10), @StartDate, 101))
SELECT @end_date = dateadd(ss, -1, dateadd(dd, 1,convert(datetime,convert(varchar(10), @EndDate, 101))))

CREATE TABLE #FINAL
( Site varchar(10),
Campus varchar(30),
ClientVisitGUID numeric(16,0),
PATIENT varchar(50),
MRN varchar(20),
EncNumber varchar(20),
TIME_PATIENT_ARRIVAL Datetime,
TIME_INIT_NURSE_ASSES DATETIME,
TIME_INIT_PHYS_ASSES DATETIME,
TIME_INIT_ORDERS DATETIME,
TIME_IN_BED DATETIME,
TIME_DISPOSITION DATETIME,
DISPOSITION varchar(2000),
CHIEF_COMPLAINT VARCHAR(2000),
GESTATIONAL_AGE_WEEKS VARCHAR(2000),
GESTATIONAL_AGE_DAYS VARCHAR(2000))

DECLARE @NBTRIAGE TABLE
( ClientVisitGUID numeric(16,0),
time_in_bed DATETIME,
time_disposition VARCHAR(2000),
disposition VARCHAR(2000))

INSERT into @NBTRIAGE

SELECT DISTINCT
cv.GUID AS ClientVisitGUID,
CV.ADMITDTM AS time_in_bed,
case when obsCatalog.Name='nsg_OBTriageHx_pt_disposition_dt1' then
ISNULL(obser.valuetext,[DBO].[NYP_AddObsValue_Fn] (cv.guid, obserdoc.ObservationDocumentGUID)) end AS TIME_DISPOSITION,
case when obsCatalog.Name='nsg_OBTriageHx_pt_disposition' then
ISNULL(obser.valuetext,[DBO].[NYP_AddObsValue_Fn] (cv.guid, obserdoc.ObservationDocumentGUID)) end AS disposition
FROM
dbo.CV3ClientVisit AS cv
INNER JOIN
dbo.CV3Location AS loc WITH (nolock) ON cv.CurrentLocationGUID = loc.GUID
AND loc.ParentGUID in('2000001000061001','4000001145061001 ','8000001069061001')
INNER JOIN
dbo.CV3ClientDocument AS cd WITH (nolock) ON cv.GUID = cd.ClientVisitGUID
AND cv.TypeCode = 'Inpatient' and cd.PatCareDocGUID IN ('9000001670102020','4000001085202001')
INNER JOIN
dbo.CV3ObservationDocument AS obserdoc WITH (nolock) ON cd.GUID = obserdoc.OwnerGUID
INNER JOIN
dbo.CV3Observation AS obser WITH (nolock) ON obser.GUID = obserdoc.ObservationGUID
INNER JOIN
dbo.CV3ObsCatalogItem AS obscatitem WITH (nolock) ON obscatitem.GUID = obser.ObsItemGUID
INNER JOIN
dbo.CV3ObsCatalogMasterItem AS obsCatalog WITH (nolock) ON obsCatalog.GUID = obscatitem.MasterObsGUID
and obsCatalog.Name in ('nsg_OBTriageHx_AdmData_arrival_time','nsg_OBTriageHx_pt_disposition_dt1',
'nsg_OBTriageHx_pt_disposition')
inner JOIN
dbo.SCMObsFSListValues AS obsFS WITH (nolock) ON obsFS.ParentGUID = obserdoc.ObservationDocumentGUID

group BY
cv.GUID,
CV.ADMITDTM,
obsCatalog.Name,
obser.ValueText,
obsFS.Value,
obserdoc.ObservationDocumentGUID

DECLARE @minord TABLE
( ClientVisitGUID numeric(16,0),
min_order_date DATETIME)

INSERT INTO @MINORD

SELECT
ClientVisitGUID,
MIN(RequestedDtm) AS min_order_date

FROM dbo.CV3Order
INNER JOIN
dbo.CV3User WITH (nolock) ON dbo.CV3Order.UserGUID = dbo.CV3User.GUID
where OrderRoleType in ('Resident', 'NP','PA')
GROUP BY ClientVisitGUID,OrderRoleType

--Get all patients who had a Note within the select date time by specific attendings and fellows
SELECT DISTINCT
Site = 'WEST',
Campus =
CASE
when loc.ParentGUID = 2000001000061001 then 'Allen Hospital'
when loc.ParentGUID = 4000001145061001 then 'Milstein Hospital'
when loc.ParentGUID = 8000001069061001 then 'MS CHONY'
END,
cv.GUID AS ClientVisitGUID,
cv.ClientDisplayName AS PATIENT,
cv.IDCode AS MRN,
cv.VisitIDCode AS EncNumber,
cv.AdmitDtm AS TIME_PATIENT_ARRIVAL,
obsCatalog.Name as cat_name,
ISNULL(obser.valuetext,[DBO].[NYP_AddObsValue_Fn] (cv.guid, obserdoc.ObservationDocumentGUID)) AS ChartedValue2,
CD.AuthoredDtm AS TIME_INIT_NURSE_ASSES,
CD.AuthoredDtm AS TIME_INIT_PHYS_ASSES,
min(MO.min_order_date) AS TIME_INIT_ORDERS,
NB.ClientVisitGUID AS NBGUID,
NB.time_in_bed AS TIME_IN_BED,
NB.TIME_DISPOSITION AS TIME_DISPOSITION,
NB.disposition AS DISPOSITION

into #result
FROM
dbo.CV3ClientVisit AS cv
inner JOIN
@NBTRIAGE AS NB ON CV.guid=NB.ClientVisitGUID
AND cv.TypeCode = 'Inpatient'
INNER JOIN
@minord MO on CV.guid=MO.ClientVisitGUID
INNER JOIN
dbo.CV3Location AS loc WITH (nolock) ON cv.CurrentLocationGUID = loc.GUID
AND loc.ParentGUID in('2000001000061001','4000001145061001 ','8000001069061001')
INNER JOIN
dbo.CV3ClientDocument AS cd WITH (nolock) ON cv.GUID = cd.ClientVisitGUID
and cd.PatCareDocGUID ='1000001197202001'INNER JOIN
dbo.CV3ObservationDocument AS obserdoc WITH (nolock) ON cd.GUID = obserdoc.OwnerGUID
INNER JOIN
dbo.CV3Observation AS obser WITH (nolock) ON obser.GUID = obserdoc.ObservationGUID
INNER JOIN
dbo.CV3ObsCatalogItem AS obscatitem WITH (nolock) ON obscatitem.GUID = obser.ObsItemGUID
INNER JOIN
dbo.CV3ObsCatalogMasterItem AS obsCatalog WITH (nolock) ON obsCatalog.GUID = obscatitem.MasterObsGUID
and obsCatalog.Name in('note_OB_Triage_chiefcomp_ls','note_OB_Delivery_GA_weeks_ft',
'note_OB_Delivery_GA_days_ft','note_OB_Triage_Admit_ls')
LEFT OUTER JOIN
dbo.SCMObsFSListValues AS obsFS WITH (nolock) ON obsFS.ParentGUID = obserdoc.ObservationDocumentGUID
WHERE cv.AdmitDtm BETWEEN '04/01/2011' AND '04/02/2011'

group BY
loc.ParentGUID,
cv.GUID,
cv.ClientDisplayName,
cv.IDCode,
cv.VisitIDCode,
cv.AdmitDtm,
obsCatalog.Name ,
obser.valuetext,
obserdoc.ObservationDocumentGUID,
CD.AuthoredDtm,
CD.AuthoredDtm,
NB.ClientVisitGUID,
NB.time_in_bed,
NB.TIME_DISPOSITION,
NB.disposition
select * from #result
--Add all data to the final table
INSERT #Final
( Site ,
Campus,
ClientVisitGUID,
PATIENT,
MRN,
EncNumber,
TIME_PATIENT_ARRIVAL,
TIME_INIT_NURSE_ASSES,
TIME_INIT_PHYS_ASSES,
TIME_INIT_ORDERS,
TIME_IN_BED,
TIME_DISPOSITION,
DISPOSITION)

SELECT
Site ,
Campus,
ClientVisitGUID,
PATIENT,
MRN,
EncNumber,
TIME_PATIENT_ARRIVAL,
TIME_INIT_NURSE_ASSES,
TIME_INIT_PHYS_ASSES,
TIME_INIT_ORDERS,
TIME_IN_BED,
TIME_DISPOSITION,
DISPOSITION
FROM #Result

group by
Site ,
Campus,
ClientVisitGUID,
PATIENT,
MRN,
EncNumber,
TIME_PATIENT_ARRIVAL,
TIME_INIT_NURSE_ASSES,
TIME_INIT_PHYS_ASSES,
TIME_INIT_ORDERS,
TIME_IN_BED,
TIME_DISPOSITION,
DISPOSITION

-- Update final table to assign Date and Time for Result Received and Result Communicated (Notification)for each patient
UPDATE #Final
SET TIME_INIT_ORDERS = R.TIME_INIT_ORDERS
FROM #Result r, #Final f
WHERE f.ClientVisitGUID=r.ClientVisitGUID

UPDATE #Final
SET TIME_IN_BED = R.TIME_IN_BED
FROM #Result r, #Final f
WHERE f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'nsg_OBTriageHx_AdmData_arrival_time'

UPDATE #Final
SET TIME_DISPOSITION= R.ChartedValue2
FROM #Result r, #Final f
WHERE f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'nsg_OBTriageHx_pt_disposition_dt1'

UPDATE #Final
SET DISPOSITION = R.DISPOSITION
FROM #Result r, #Final f
WHERE f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name ='note_OB_Triage_Admit_ls'

UPDATE #Final
SET CHIEF_COMPLAINT =R.ChartedValue2
FROM #Result r, #Final f
WHERE f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name ='note_OB_Triage_chiefcomp_ls'

UPDATE #Final
SET GESTATIONAL_AGE_WEEKS = R.ChartedValue2
FROM #Result r, #Final f
WHERE f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'note_OB_Delivery_GA_weeks_ft'

UPDATE #Final
SET GESTATIONAL_AGE_DAYS = R.ChartedValue2
FROM #Result r, #Final f
WHERE f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'note_OB_Delivery_GA_days_ft'

--Final select
SELECT DISTINCT
Site,
Campus,
ClientVisitGUID,
PATIENT,
MRN ,
EncNumber,
TIME_PATIENT_ARRIVAL,
TIME_INIT_NURSE_ASSES,
TIME_INIT_PHYS_ASSES,
TIME_INIT_ORDERS,
TIME_IN_BED,
TIME_DISPOSITION,
DISPOSITION,
CHIEF_COMPLAINT,
GESTATIONAL_AGE_WEEKS,
GESTATIONAL_AGE_DAYS

FROM #Final
group BY
Site,
Campus,
ClientVisitGUID,
PATIENT,
MRN ,
EncNumber,
TIME_PATIENT_ARRIVAL,
TIME_INIT_NURSE_ASSES,
TIME_INIT_PHYS_ASSES,
TIME_INIT_ORDERS,
TIME_IN_BED,
TIME_DISPOSITION,
DISPOSITION,
CHIEF_COMPLAINT,
GESTATIONAL_AGE_WEEKS,
GESTATIONAL_AGE_DAYS

DROP TABLE #Result
DROP TABLE #Final

END

IF OBJECT_ID('dbo.NYP_ld_triage_eff_msrs_rpt') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.NYP_ld_triage_eff_msrs_rpt >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.NYP_ld_triage_eff_msrs_rpt >>>'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-04 : 16:30:38

I'd start by viewing the execution plan and adding indexes on the problem areas.

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

Subscribe to my blog

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-05-04 : 19:32:40

Right off the top - I would review the usage of DISTINCT and GROUP BY in every query. First, there is no reason to have both and in every one of your queries you use GROUP BY without using an aggregates.

If you have included those to eliminate duplicate rows, then you need to review the tables and the joins to determine why you are getting duplicate rows and fix the query to only return what you need.

Next, I would review each update that follows the final insert. In almost every case, the update is either not needed or can be included in the insert statement. In fact, I would work on removing that final insert statement and building the final select instead of inserting into a 'final' table.

For example, I cannot see any use for the following update:

-- Update final table to assign Date and Time for Result Received and Result Communicated (Notification)for each patient
UPDATE #Final
SET TIME_INIT_ORDERS = R.TIME_INIT_ORDERS
FROM #Result r, #Final f
WHERE f.ClientVisitGUID=r.ClientVisitGUID

In this update, you are updating the #final table with TIME_INIT_ORDERS value from the #result table. But, you just got done inserting the data in the #result table from the #final table. How would that column be missing the data and why would you need to update it?

And, if you look at all of the update statements - they are very similar. As far as I can tell, all of them can be included in the select statement that is used to populate the #final table. Now, since all of that can be done when populating the #final table - the table isn't needed and that query would be the 'final' output query.

Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-05-05 : 00:13:49

I'd also see the othe answers here
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/33992ac5-9d20-4d12-bd70-9863d1084555

N 56°04'39.26"
E 12°55'05.63"

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-05 : 01:50:22

I'd strongly recommend a reconsideration of the isolation level. "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" (and a couple of nolock hints as well). Unless it has been determined that dirty data, potential missing rows and potential duplicate rows are acceptable, neither should be used.

Read Uncommitted is not a 'go faster' switch. It's telling SQL that you don't mind if the data is slightly inaccurate.

--
Gail Shaw
SQL Server MVP

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-05-05 : 09:04:56

i have to use nolocks and the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
per the standards set by my boss.
thanks for the info.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-05 : 09:08:25

Does your boss know that using them can result in inaccurate data, in the query completely missing rows, in the query returning rows multiple times?

--
Gail Shaw
SQL Server MVP

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-05-05 : 10:10:35

not sure, dont like to challenge her, like to keep my job, just do what i tell you. but the few suggestions made helped greatly. thanks appreciate it.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-05-05 : 11:24:12

quote:
Originally posted by sharona

not sure, dont like to challenge her, like to keep my job, just do what i tell you. but the few suggestions made helped greatly. thanks appreciate it.

Personally I'm not big on no lock. But lots of people use it, this might surpise people:
http://blog.sqlauthority.com/2008/09/24/sql-server-coding-standards-guidelines-part-2/

     Using the NOLOCK query optimizer hint is considered good
     practice in order to improve concurrency on a busy system.

In my opinion, a good first step is to determine which of the statements is the bottleneck. Maybe that is too obvious but if you need help in doing that, just say so?

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-05-05 : 11:25:36

quote:
Originally posted by Peso

I'd also see the othe answers here
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/33992ac5-9d20-4d12-bd70-9863d1084555

N 56°04'39.26"
E 12°55'05.63"

It's fair game, Peso. Nothing wrong with that. That is not cross-posting.

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-05-05 : 15:33:36

yes i posted there as well. all were helpful

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources