October 11, 2014 at 8:57 am
I am having a issue with a query. It's a Stored Procedure for a report with temp tables. When I run the query for a date range of 01/01/14 to 12/31/14, it runs in a average of 30 seconds for 17,777 rows. If I change the Start Date to 02/01/14, 03/01/14, 04/01/14/, 05/01/14, 06/01/14, 07/01/14 they all run in around 30 seconds. I change the Start Date to 08/01/14 or 09/01/14 and it takes 35 minutes for 4,601 rows.
I am on a virtual test server that no one else is on today. I'm not sure what is causing this huge discrepancy and how to fix it. Any help is very much appreciated.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------values for testing
--EXEC [dbo].[cusUDS2014Table6B-I]
-- @FromDate = N'08/01/14',
-- @ToDate = N'12/31/14',
-- @FacilityID = NULL,
-- @CompanyID = NULL
ALTER PROCEDURE [dbo].[cusUDS2014Table6B-I]
(
@FromDate DATETIME,
@ToDate DATETIME,
@FacilityID VARCHAR(255),
@CompanyID VARCHAR(255)
)
--WITH RECOMPILE
AS
DECLARE
@vFromDate DATETIME,
@vToDate DATETIME,
@vFacilityID VARCHAR(255),
@vCompanyID VARCHAR(255)
SELECT
@vFromDate = @FromDate
,@vToDate = @ToDate
,@vFacilityID = @FacilityID
,@vCompanyID = @CompanyID
BEGIN
DECLARE
@BegThreeAgeDate DATETIME,
@EndThreeAgeDate DATETIME,
@SixtyFourDate DATETIME,
@24Date DATETIME,
@30Date DATETIME,
@OrderDate DATETIME,
@OrderDateHPV DATETIME,
@AssessDate DATETIME,
@AgeDate DATETIME,
@PerinatalAgeDate DATETIME
SET @BegThreeAgeDate = '01/01/' + CONVERT(VARCHAR, (YEAR(@vToDate)- 3))
SET @EndThreeAgeDate = '01/01/' + CONVERT(VARCHAR, (YEAR(@vToDate )- 2))
SET @SixtyFourDate = '01/01/' + CONVERT(VARCHAR, (YEAR(@vToDate )- 64))
SET @24Date = '12/31/' + CONVERT(VARCHAR, (YEAR(@vToDate )- 24))
SET @30Date = '12/31/' + CONVERT(VARCHAR, (YEAR(@vToDate )- 30))
SET @OrderDate = '01/01/' + CONVERT(VARCHAR, (YEAR(@vToDate )- 2))
SET @OrderDateHPV = '01/01/' + CONVERT(VARCHAR, (YEAR(@vToDate )- 4))
SET @AssessDate = '12/31/' + CONVERT(VARCHAR, YEAR(@vToDate)-2)
SET @PerinatalAgeDate = '6/30/' + CONVERT(VARCHAR, YEAR (@vToDate))
SET @AgeDate = '12/31/' + CONVERT(VARCHAR, YEAR(@vToDate))
-- This table contains all of the results for each section of 6B
CREATE TABLE #Results
(
PatientProfileID INT PRIMARY KEY ,
NumberOfDeliveries INT,
Trimester TINYINT,
BirthWeight NUMERIC (8,0),
FirstSite VARCHAR(400),
Birthdate DATETIME,
Age NUMERIC (4,1),
PerinatalAge NUMERIC (4,1),
PatientID VARCHAR (250),
HIV BIT,
DeliveredInClinic INT,
PrenatalPatient INT,
PID NUMERIC (19),
Sex VARCHAR (1),
dtp INT,
dtpd INT,
Fipv INT,
ipvd INT,
mmr INT,
mmrd INT,
hib INT,
hibd INT,
hepb INT,
hepbd INT,
vzv INT,
vzvd INT,
pne INT,
fullyImmunized INT,
ImmunDenom INT,
PapDenom INT,
PapNumerator INT
)
-- INSERT INTO #Results ALL Patients who may or may not have participation in any one of the denominators.
INSERT INTO #Results
(
PatientProfileID ,
NumberOfDeliveries ,
Trimester ,
BirthWeight,
FirstSite,
Birthdate ,
Age ,
PerinatalAge ,
PatientID ,
HIV,
DeliveredInClinic ,
PrenatalPatient,
PID ,
Sex ,
dtp ,
dtpd ,
Fipv ,
ipvd ,
mmr ,
mmrd ,
hib ,
hibd ,
hepb ,
hepbd ,
vzv ,
vzvd ,
pne ,
fullyImmunized ,
ImmunDenom ,
PapDenom ,
PapNumerator
)
SELECT
DISTINCT PatientProfileID = pp.PatientProfileId -- PatientProfileID - int
,NumberOfDeliveries = 0 -- NumberOfDeliveries - int
,Trimester = 0 -- Trimester - tinyint
,BirthWeight = 0 -- BirthWeight - numeric
,FirstSite = 0 -- FirstSite varchar(400)
,Birthdate = pp.Birthdate -- Birthdate - datetime
,Age = (DATEDIFF(DAY,pp.Birthdate,@AgeDate) /365.25) -- Age - numeric
,PerinatalAge = (DATEDIFF(DAY,pp.Birthdate,@PerinatalAgeDate) /365.25) -- Perinatal Age - numeric
,PatientID = pp.PatientID -- PatientID - varchar(250)
,HIV = 0
,DeliveredInClinic = 0 -- DeliveredInClinic - int
,PrenatalPatient = 0 -- PrenatalPatient INT
,PID = pp.Pid -- PID - numeric
,Sex = pp.Sex -- Sex - varchar(1)
,dtp = 0 -- dtp - int
,dtpd = 0 -- dtpd - int
,fipv = 0 -- Fipv - int
,ipvd = 0 -- ipvd - int
,mmr = 0 -- mmr - int
,mmrd = 0 -- mmrd - int
,hib = 0 -- hib - int
,hibd = 0 -- hibd - int
,hepb = 0 -- hepb - int
,hepbd = 0 -- hepbd - int
,vzv = 0 -- vzv - int
,vzvd = 0 -- vzvd - int
,pne = 0 -- pne - int
,fullyimmunized = 0 -- fullyImmunized - int
,ImmunDenom = 0 -- ImmunDenom - int
,PapDenom = 0 -- PapDenom - int
,PapNumerator = 0 -- PapNumerator - int
FROM PatientProfile pp
INNER JOIN PatientVisit pv ON pv.PatientProfileId =pp.PatientProfileId
INNER JOIN PatientVisitProcs pvp ON pvp.PatientVisitId = pv.PatientVisitId
INNER JOIN [Procedures] p ON p.ProceduresId = pvp.ProceduresId
LEFT JOIN MedLists ml ON ml.MedListsId = p.CPTProcedureCodeQualifierMId
WHERE --Limit to Medical Visists
Ml.[Description] LIKE '[HCPC]%[Med%]' AND pvp.DateOfServiceFrom BETWEEN @vFromDate AND @vToDate
AND (@vCompanyID IS NULL OR pv.companyID IN (SELECT Val FROM dbo.Split(@vCompanyID, ',')))
AND (@vFacilityID IS NULL OR pp.FacilityId IN (SELECT Val FROM dbo.Split(@vFacilityID, ',')))
----Create Exclusions Pap table
CREATE TABLE #PAP (RowID INT NOT NULL IDENTITY(1,1),PID NUMERIC (19)PRIMARY KEY (RowID,PID))
UPDATE #PAP
SET PID = obs.PID
FROM #Pap p
INNER JOIN OBS ON p.PID = obs.PID
INNER JOIN cusUDS6b_obsnum_2014 od ON od.HDID = obs.HDID
WHERE od.LineNumber = '6bHx' -- hx Hysterectomy
UPDATE #PAP
SET PID = pm.PID
FROM #Pap p
INNER JOIN PROBLEM pm ON pm.PID = p.PID
WHERE XID = 1000000000000000000 AND CODE IN ('ICD-V88.01','ICD-V45.77')
UPDATE #PAP
SET PID = pp.PID
FROM #Pap p
INNER JOIN PatientProfile pp ON pp.PId = p.PID
INNER JOIN PatientVisit pv ON pp.PatientProfileId = pv.PatientProfileId
INNER JOIN PatientVisitProcs pvp ON pvp.PatientVisitId = pv.PatientVisitId
INNER JOIN cusUDS6Detail_2014 cpt ON pvp.CPTCode = cpt.CPTPrefix
INNER JOIN PatientVisitDiags pvd ON pvd.PatientVisitId = pv.PatientVisitId
INNER JOIN cusUDS6Detail_2014 dx ON pvd.Code = dx.DiagnosisPrefix
WHERE (cpt.LineNumber = '6BHx' ) OR (dx.LineNumber = 'D' )
-------- UDS 6b - Sections A & B
CREATE TABLE #UDSPerinatal
(
RowID INT NOT NULL IDENTITY(1,1)
,PatientProfileID INT
,cusCasesPerinatalDtlID INT
,FacilityName VARCHAR (255)
,Trimester TINYINT
,OutcomeDte DATE
,BirthWt NUMERIC
,PRIMARY KEY (RowID,PatientProfileID)
)
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE name ='cusCasesPN' and TYPE ='u')
INSERT INTO #UDSPerinatal
SELECT
DISTINCT p.PatientProfileID,
pd.cusCasesPNDtlID AS cusCasesPerinatalDtlID,
f.ListName AS FacilityName,
p.Trimester,
pd.OutcomeDte,
pd.BirthWt
FROM dbo.cusCasesPN p
LEFT JOIN dbo.cusCasesPNDtl pd ON p.cusCasesPNID = pd.cusCasesPNID
LEFT JOIN dbo.DoctorFacility f ON p.FirstVisitSiteID = f.DoctorFacilityID
INNER JOIN dbo.PatientVisit pv ON p.CasesID = pv.CasesID
WHERE (pv.Visit BETWEEN @vFromDate AND (DATEADD(d,1,@vToDate)))
ELSE
INSERT INTO #UDSPerinatal
SELECT
p.PatientProfileID,
pd.cusCasesPeriNatalID AS cusCasesPerinatalDtlID,
f.ListName AS FacilityName,
ISNULL(p.Trimester,0) AS Trimester,
pd.OutcomeDte,
ISNULL(pd.BirthWt,0) AS BirthWt
FROM dbo.cusCasesPeriNatal p
INNER JOIN dbo.PatientVisit pv ON p.CasesID = pv.CasesID
LEFT JOIN dbo.cusCasesPeriNatalDtl pd ON p.cusCasesPeriNatalID = pd.cusCasesPeriNatalID
LEFT JOIN dbo.cusCRIMedLists cml1 ON pd.DeliveryTypeMID = cml1.MedListsID
LEFT JOIN dbo.DoctorFacility f ON p.FirstVisitSiteID = f.DoctorFacilityID
WHERE (pv.Visit BETWEEN @vFromDate AND (DATEADD(d,1,@vToDate)))
---- Clean up #UDSTMP to eliminate Outcomes that aren't deliveries
DELETE FROM #UDSPerinatal WHERE OutcomeDte IS NOT NULL AND OutcomeDte < @vFromDate
SELECT PatientProfileID
,NumberofDeliveries = ISNULL(COUNT(cusCasesPerinatalDtlID),0)
,BirthWeight = ISNULL(SUM(BirthWt),0)
INTO #Summary
FROM #UDSPerinatal
GROUP BY PatientProfileID
UPDATE #Results
SET
NumberofDeliveries = u.NumberOfDeliveries
,BirthWeight = u.BirthWeight
,HIV = 0
,DeliveredInClinic = 0
FROM #Summary u
INNER JOIN #Results r ON u.PatientProfileID = r.PatientProfileID
UPDATE #Results
SET
Trimester = u.Trimester
,FirstSite = ISNULL(u.FacilityName,'No First Site Identified')
FROM #UDSPerinatal u
INNER JOIN #Results r ON u.PatientProfileID = r.PatientProfileID
UPDATE #Results
SET PrenatalPatient = 1
FROM #Results r
INNER JOIN PatientVisit pv ON pv.PatientProfileId = r.PatientProfileID
INNER JOIN #UDSPerinatal ON #UDSPerinatal.PatientProfileID = r.PatientProfileID
INNER JOIN dbo.PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
INNER JOIN dbo.cusUDS6Detail_2014 dx ON pvd.Code LIKE (dx.DiagnosisPrefix + '%')
WHERE pv.Visit BETWEEN @vFromDate AND (DATEADD(DAY,1,@vToDate))
AND dx.LineNumber = 'B' AND dx.DiagnosisPrefix IS NOT NULL
DROP TABLE #UDSPerinatal
DROP TABLE #Summary
------ End of UDS 6b - Sections A & B
------ UDS 6b Section C - Immunizations
----Find the correct age range and results for the IMMUNE DENOM
;WITH IMMUN_DENOM_CTE AS (
SELECT DISTINCT pp.PatientProfileID
FROM PatientVisit pv
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
INNER JOIN PatientVisitProcs pvp ON pvp.PatientVisitId = pv.PatientVisitId
INNER JOIN [Procedures] p ON p.ProceduresId = pvp.ProceduresId
LEFT JOIN MedLists ml ON ml.MedListsId = p.CPTProcedureCodeQualifierMId
WHERE pp.Birthdate BETWEEN @BegThreeAgeDate AND (DATEADD(d,1,@EndThreeAgeDate))
AND pvp.DateOfServiceFrom < DATEADD(day,1,@vToDate) AND (ml.[Description] LIKE '[HCPC]%[Med%]')
AND PVP.DateOfServiceFrom < DATEADD(yy,3,pp.Birthdate))
UPDATE #Results
SET ImmunDenom = 1
FROM #Results r
INNER JOIN IMMUN_DENOM_CTE IDC ON idc.PatientProfileId = r.PatientProfileID
---- dtp cpt
UPDATE #Results
SET dtp = i.NumberofImmunizations
FROM #Results t
INNER JOIN dbo.cusUDSImmunOBSData_2014('DTP/DtaP',@EndThreeAgeDate, @BegThreeAgeDate) i ON t.PatientProfileID = i.PatientProfileID
------ dtp icd9
UPDATE #Results
SET dtpd= 1
FROM #Results t
INNER JOIN PROBLEM p ON t.PID = p.PID
LEFT JOIN cusUDS6Detail_2014 ud ON ud.DiagnosisPrefix = p.CODE
WHERE ud.LineNumber = 'dtpd'
---- ipv cpt
UPDATE #Results
SET Fipv = i.NumberofImmunizations
FROM #Results t
INNER JOIN dbo.cusUDSImmunOBSData_2014('IPV',@EndThreeAgeDate, @BegThreeAgeDate) i ON t.PatientProfileID = i.PatientProfileID
---- ipv icd9
UPDATE #Results
SET ipvd= 1
FROM #Results t
INNER JOIN PROBLEM p ON t.PID = p.PID
LEFT JOIN cusUDS6Detail_2014 ud ON ud.DiagnosisPrefix = p.CODE
WHERE ud.LineNumber ='ipvd'
---- mmr cpt
UPDATE #Results
SET mmr = i.NumberofImmunizations
FROM #Results t
INNER JOIN dbo.cusUDSImmunOBSData_2014('MMR',@EndThreeAgeDate, @BegThreeAgeDate) i ON t.PatientProfileID = i.PatientProfileID
---- mmr icd9
UPDATE #Results
SET mmrd= 1
FROM #Results t
INNER JOIN PROBLEM p ON t.PID = p.PID
LEFT JOIN cusUDS6Detail_2014 ud ON ud.DiagnosisPrefix = p.CODE
WHERE ud.LineNumber = 'mmrd'
---- hib cpt
UPDATE #Results
SET hib = i.NumberofImmunizations
FROM #Results t
INNER JOIN dbo.cusUDSImmunOBSData_2014('Hib',@EndThreeAgeDate, @BegThreeAgeDate) i ON t.PatientProfileID = i.PatientProfileID
---- hib icd9
UPDATE #Results
SET hibd= 1
FROM #Results t
INNER JOIN PROBLEM p ON t.PID = p.PID
LEFT JOIN cusUDS6Detail_2014 ud ON ud.DiagnosisPrefix = p.CODE
WHERE ud.LineNumber ='hibd'
---- hepb cpt
UPDATE #Results
SET hepb = i.NumberofImmunizations
FROM #Results t
INNER JOIN dbo.cusUDSImmunOBSData_2014('HepB',@EndThreeAgeDate, @BegThreeAgeDate) i ON t.PatientProfileID = i.PatientProfileID
---- hepb icd9
UPDATE #Results
SET hepbd= 1
FROM #Results t
INNER JOIN PROBLEM p ON t.PID = p.PID
LEFT JOIN cusUDS6Detail_2014 ud ON ud.DiagnosisPrefix = p.CODE
WHERE ud.LineNumber ='hepbd'
-- vzv cpt
UPDATE #Results
SET vzv = i.NumberofImmunizations
FROM #Results t
INNER JOIN dbo.cusUDSImmunOBSData_2014('Varicella',@EndThreeAgeDate, @BegThreeAgeDate) i ON t.PatientProfileID = i.PatientProfileID
-- vzv icd9
UPDATE #Results
SET vzvd= 1
FROM #Results t
INNER JOIN PROBLEM p ON t.PID = p.PID
LEFT JOIN cusUDS6Detail_2014 ud ON ud.DiagnosisPrefix = p.CODE
WHERE ud.LineNumber ='vzvd'
-- pne cpt
UPDATE #Results
SET pne = i.NumberofImmunizations
FROM #Results t
INNER JOIN dbo.cusUDSImmunOBSData_2014('Pneumoccocal',@EndThreeAgeDate, @BegThreeAgeDate) i ON t.PatientProfileID = i.PatientProfileID
---- Now Update those Patients that are fully immunized
UPDATE #Results
SET FullyImmunized = 1
WHERE
(dtp >= 4 OR dtpd = 1)
AND (Fipv >= 3 OR ipvd = 1)
AND (mmr >= 1 OR mmrd = 1)
AND (hib >= 3 OR hibd = 1)
AND (hepb >= 3 OR hepbd = 1)
AND (vzv >= 1 OR vzvd = 1)
AND (pne >= 4)
AND ImmunDenom = 1
---- End of UDS 6b - Section C - Immunizations
---- UDS 6b D -
---- Update Patients that meet criteria for Cervical Cancer Screen Denominator
UPDATE #Results
SET PapDenom = 1
FROM #Results r
LEFT JOIN #PAP p ON p.PID = r.PID
WHERE r.Birthdate BETWEEN (DATEADD(d,1,@SixtyFourDate)) AND (DATEADD(d,1,@24Date))
AND r.Sex = 'F'
AND p.PID IS NULL
UPDATE #Results -- PAP Only
SET PapNumerator = 1
FROM #Results r
INNER JOIN ORDERS o ON r.pid = o.PID
INNER JOIN cusUDS6Detail_2014 cpt ON o.code = cpt.CPTPrefix
WHERE o.XID = 1000000000000000000
AND o.ORDERDATE > @OrderDate AND o.ORDERDATE < DATEADD(day,1,@vToDate)
AND r.PapDenom = 1
AND cpt.LineNumber = '23'
AND cpt.CPTPrefix IS NOT NULL
UPDATE #Results -- PAP Only
SET PapNumerator = 1
FROM #Results r
INNER JOIN OBS ON r.pid = OBS.PID
LEFT JOIN cusUDS6b_obsnum_2014 od ON od.HDID = obs.HDID
WHERE od.LineNumber = 'PAP' AND OBSDATE > @OrderDate AND OBS.OBSDATE < DATEADD(day,1,@vToDate)
AND r.PapDenom = 1
UPDATE #Results -- HPV
SET PapNumerator = 1
FROM #Results r
INNER JOIN ORDERS o ON r.pid = o.PID
INNER JOIN cusUDS6Detail_2014 cpt ON o.code = cpt.CPTPrefix
WHERE cpt.LineNumber = '23' -- HPV Testing
AND o.XID = 1000000000000000000
AND o.ORDERDATE > @OrderDateHPV AND o.ORDERDATE < DATEADD(day,1, @vToDate)
AND r.PapDenom = 1
AND r.Birthdate BETWEEN (DATEADD(d,1,@SixtyFourDate)) AND (DATEADD(d,1,@30Date))
UPDATE #Results -- HPV
SET PapNumerator = 1
FROM #Results r
INNER JOIN OBS ON r.pid = OBS.PID
LEFT JOIN cusUDS6b_obsnum_2014 od ON od.HDID = obs.HDID
WHERE od.LineNumber ='HPV' -- HPV Testing
AND OBS.OBSDATE > @OrderDate AND OBS.OBSDATE < DATEADD(day,1, @vToDate)
AND r.PapDenom = 1
AND r.Birthdate BETWEEN (DATEADD(d,1,@SixtyFourDate)) AND (DATEADD(d,1,@30Date))
END
SELECT * FROM #Results
DROP TABLE #Results
DROP TABLE #PAP
-- End of UDS 6b(D)
GO
***SQL born on date Spring 2013:-)
October 11, 2014 at 11:09 am
Just checking, but have you had issues with this procedure before? The beginning of the proc is very familiar with all the date calculations at the start.
Both query plans indicate the possibility of missing indexes. Not saying you should implement those, but just an observation. Would it be possible for you to share the DDL (including indexes currently defined) of the tables involved in the processing in this procedure?
October 11, 2014 at 11:12 am
Lynn, it is. This is my redo. It's still better than the original which would run for hours. I'll see what other information I can provide.
***SQL born on date Spring 2013:-)
October 11, 2014 at 11:16 am
Here is the Immunization function. This is one of the spots it seems to hang up on when I'm watching the execution plan update.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[cusUDSImmunOBSData_2014](@OBSCategory VARCHAR(50), @EndThreeAgeDate DATETIME, @BegThreeAgeDate DATETIME)
RETURNS TABLE AS RETURN
SELECT pp.PatientProfileID
,ISNULL(COUNT(*),0) AS NumberofImmunizations
FROM OBS o
JOIN PatientProfile pp ON o.PID = pp.PID
JOIN OBSHEAD oh ON o.HDID = oh.HDID
JOIN cusUDSImmunizationCategoryProcedures_2014 cPAP ON cPAP.ProceduresId = oh.MLCODE
JOIN cusUDSSections_2014 pag ON cPAP.ImmunizationCategoryGrpMId = pag.SectionCategoryId
AND pag.[DESCRIPTION] = @OBSCategory
WHERE
pp.Birthdate >= @BegThreeAgeDate AND pp.Birthdate < DATEADD(d,1,@EndThreeAgeDate)
AND (
(
@OBSCategory IN ('DTP/DtaP','IPV','Hib') AND o.OBSDATE >= DATEADD(dd,42,pp.Birthdate) AND o.OBSDATE < DATEADD(yy,3,pp.Birthdate)
)
OR
(@OBSCategory <> 'DTP/DtaP' AND @OBSCategory <> 'IPV' AND @OBSCategory <> 'Hib' AND o.OBSDATE < DATEADD(yy,3,pp.Birthdate))
)
AND o.XID = 1000000000000000000
AND o.CHANGE = 2
GROUP BY pp.PatientProfileId
GO
***SQL born on date Spring 2013:-)
October 11, 2014 at 1:34 pm
Okay, I removed the CTE that was being used for Immune denom and set it to a regular query update and now it ran in 24 seconds.. So confused.:w00t:
***SQL born on date Spring 2013:-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply