October 10, 2008 at 1:30 pm
I'm sure there are many ways to skin a cat and probably many ways to do what I'm trying to do here:
select CASE
WHEN EXISTS (SELECT MRN FROM ptDictation d
WHERE d.MRN = e.MRN
AND d.DOS = e.DateOfService
AND d.Status = 'Approved') THEN 2
WHEN EXISTS (SELECT MRN FROM ptSchedule s
WHERE s.MRN = e.MRN
AND dbo.rr_GetDateFromDateTime(s.StartDtTm) = dbo.rr_GetDateFromDateTime(e.DateofService)
AND s.ApptTypeID = 5)
AND (NOT EXISTS(SELECT MRN FROM ptDictation d
WHERE d.MRN = e.MRN
AND d.DOS = e.DateOfService
AND d.Status = 'Approved')) THEN 1
WHEN NOT EXISTS(SELECT MRN FROM ptSchedule s
WHERE s.MRN = e.MRN
AND dbo.rr_GetDateFromDateTime(s.StartDtTm) = dbo.rr_GetDateFromDateTime(e.DateofService)
AND s.ApptTypeID = 5) THEN 0
END as DictationState
FROM ptEncounter e
where e.MRN = '5079'
Can someone show me an easier, FASTER, way to do this?
This will be part of a larger SP returning ptEncounter records based on criteria passed to the SP.
October 10, 2008 at 1:47 pm
Any chance you could pass along a bit of sample data and ddl as per http://www.sqlservercentral.com/articles/Best+Practices/61537/.
With just a quick look is this something that might go faster if you break it up and then union it back together?
-Luke.
October 10, 2008 at 2:01 pm
Luke L (10/10/2008)
Any chance you could pass along a bit of sample data and ddl as per http://www.sqlservercentral.com/articles/Best+Practices/61537/.With just a quick look is this something that might go faster if you break it up and then union it back together?
-Luke.
On the first, I can't post data due to HIPPA regulations
On the second, possibly. I need three "State indicators" one for Dictation with the rules shown above,
another for Chemo and another for LabResults, I'm still working on the rules for those.
October 10, 2008 at 2:07 pm
I was able to cut the execution time by 2/3 by reformatting it like this:
select CASE
WHEN EXISTS (SELECT d.MRN FROM ptDictation d
INNER JOIN ptEncounter ee ON ee.MRN = d.MRN AND d.DOS = e.DateOfService
WHERE d.Status = 'Approved') THEN 2
WHEN EXISTS (SELECT s.MRN FROM ptSchedule s
INNER JOIN ptEncounter eee ON eee.MRN = s.MRN
AND dbo.rr_GetDateFromDateTime(s.StartDtTm) = dbo.rr_GetDateFromDateTime(e.DateofService)
WHERE s.ApptTypeID = 5) THEN 1
ELSE 0
END as DictationState
FROM ptEncounter e
where e.MRN = '5079'
October 10, 2008 at 2:12 pm
I am writing generic script :-
select (case
when not ft.col1 is null then 2
when (not ft.col1 is null) and ( st.col1 is null) then 4
else 0 end)
from MainTable mt
left outer join FirstTable ft
on
left outer join SecondTable st
on
where mt.column1 = 'eere'
Hope that was the answer you were looking for.
The way you have written it is actually a indirect cursor where for each row value you search some table,
so as your data grows your query will take longer time.
October 10, 2008 at 2:19 pm
No one ever said the sample data had to be real your actual data.
Kudos on the improvement so far, but I'd have to imagine that you should be able to do something to get around all of those if exists with subqueries...
-Luke.
October 10, 2008 at 2:30 pm
The correlated sub-queries still bother me - but even with leaving them in - you have an extra reference to the ptEncounter in there I don't think you need:
select CASE
WHEN EXISTS (SELECT d.MRN FROM ptDictation d
WHERE d.Status = 'Approved' and e.MRN = d.MRN AND d.DOS = e.DateOfService ) THEN 2
WHEN EXISTS (SELECT s.MRN FROM ptSchedule s
WHERE s.ApptTypeID = 5 and
e.MRN = s.MRN AND
dbo.rr_GetDateFromDateTime(s.StartDtTm) = dbo.rr_GetDateFromDateTime(e.DateofService)) THEN 1
ELSE 0
END as DictationState
FROM ptEncounter e
where e.MRN = '5079'
I can't help but think a derived table would work better.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 10, 2008 at 3:35 pm
Those two UDF calls in the second subquery are probably killing any chance of optimizing it right now. Can you post their definition?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 10, 2008 at 3:45 pm
rbarryyoung (10/10/2008)
Those two UDF calls in the second subquery are probably killing any chance of optimizing it right now. Can you post their definition?
ALTER FUNCTION [dbo].[rr_GetDateFromDateTime](@DT datetime)
RETURNS datetime
AS
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
BEGIN
RETURN DATEADD(dd,0, DATEDIFF(dd,0,@DT))
END
October 10, 2008 at 3:50 pm
Ok, try replacing the calls in the Query with just the definition. That should help some, anyway.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 10, 2008 at 4:34 pm
I would try this. Note that I'm only checking for existence in the case statement based on the left joins. I also removed your UDF in favor of the builtin CONVERT function to strip off the time.
SELECT CASE
WHEN d.MRN IS NOT NULL THEN 2
WHEN s.MRN IS NOT NULL THEN 1
ELSE 0
END as DictationState
FROM ptEncounter e
LEFT JOIN ptDictation d
ON ee.MRN = d.MRN
AND d.DOS = e.DateOfService
AND d.Status = 'Approved'
LEFT JOIN ptSchedule s
ON eee.MRN = s.MRN
AND CONVERT(varchar,s.StartDtTm, 10) = CONVERT(varchar,e.DateofService, 10)
AND s.ApptTypeID = 5
WHERE e.MRN = '5079'
Gary Johnson
Sr Database Engineer
October 10, 2008 at 8:03 pm
Gary Johnson (10/10/2008)
I would try this. Note that I'm only checking for existence in the case statement based on the left joins. I also removed your UDF in favor of the builtin CONVERT function to strip off the time.
SELECT CASE
WHEN d.MRN IS NOT NULL THEN 2
WHEN s.MRN IS NOT NULL THEN 1
ELSE 0
END as DictationState
FROM ptEncounter e
LEFT JOIN ptDictation d
ON ee.MRN = d.MRN
AND d.DOS = e.DateOfService
AND d.Status = 'Approved'
LEFT JOIN ptSchedule s
ON eee.MRN = s.MRN
AND CONVERT(varchar,s.StartDtTm, 10) = CONVERT(varchar,e.DateofService, 10)
AND s.ApptTypeID = 5
WHERE e.MRN = '5079'
I wasn't aware that we knew what the Cardinality of those relations was yet?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 8:04 am
Ok, I have 2 ptEncounter records with the same MRN and DateOfService. 9 ptDictation
records with the same MRN/DOS combination as the ptEncounter records, 2 ptSchedule
records with the same MRN/StartDtTm combination as the ptEncounter records
The code above is returning 18 rows when it should only be returning 2, one for each
ptEncounter record.
Any more ideas? How would you handle this. I am open to using a temp table or variables to hold then return the results.
Here are the schemas:
CREATE TABLE [dbo].[ptEncounter](
[EncounterID] [int] IDENTITY(1,1) NOT NULL,
[BillNumber] [int] NOT NULL,
[ClaimNumber] [nvarchar](20) NULL,
[LatestRec] [bit] NOT NULL,
[MRN] [varchar](25) NULL,
[SubscriberContactID] [int] NULL,
[LocationID] [int] NULL,
[AuthID] [int] NULL,
[BillerUserID] [int] NULL,
[PostingDtTm] [datetime] NULL,
[SiteID] [int] NULL,
[DeptID] [int] NULL,
[InsuranceID] [int] NULL,
[RefPhysID] [int] NULL,
[ProviderID] [int] NULL,
[DateOfService] [datetime] NULL,
[DateOfIllness] [datetime] NULL,
[TotalAmt] [decimal](18, 2) NULL,
[PaidAmt] [decimal](18, 2) NULL,
[OpenAmt] [decimal](18, 2) NULL,
[PaidInFull] [bit] NULL,
[ClaimSubmissionDtTm] [datetime] NULL,
[ClaimStatus] [int] NULL,
[Diag1] [nvarchar](10) NULL,
[Diag2] [nvarchar](10) NULL,
[Diag3] [nvarchar](10) NULL,
[Diag4] [nvarchar](10) NULL,
[StatementMessage1] [nvarchar](500) NULL,
[StatementMessage2] [nvarchar](500) NULL,
[BillComment1] [nvarchar](500) NULL,
[BillComment2] [nvarchar](500) NULL,
[AdmitDtTm] [datetime] NULL,
[DischargeDtTm] [datetime] NULL,
[AutoAccidentRelated] [bit] NULL,
[EmploymentAccidentRelated] [bit] NULL,
[OtherAccidentRelated] [bit] NULL,
[State] [nvarchar](2) NULL,
[PreAuthNumber] [nvarchar](50) NULL,
[CreatedByUserID] [int] NULL,
[CreatedDtTm] [datetime] NULL,
[ModifiedByUserID] [int] NULL,
[ModifiedDtTm] [datetime] NULL,
[DeletedByUserID] [int] NULL,
[DeletedDtTm] [datetime] NULL,
[FirstOnsetDtTm] [datetime] NULL,
[AcceptAssignment] [bit] NULL,
[Errors] [varchar](max) NULL,
[BucketID] [int] NULL,
)
CREATE TABLE [dbo].[ptDictation](
[ID] [int] IDENTITY(1,1) NOT NULL,
[MRN] [varchar](25) NOT NULL,
[DOS] [datetime] NULL,
[DocumentNum] [int] NULL,
[SectionTitle] [nvarchar](50) NULL,
[SectionOrder] [decimal](10, 2) NULL,
[Status] [varchar](50) NULL,
[Doctor] [varchar](50) NULL,
[SystemTime] [timestamp] NULL,
[Text] [varchar](7800) NULL,
[UserID] [char](10) NULL,
[Deleted] [int] NULL,
[DeletedBy] [char](10) NULL,
[DeletedDate] [datetime] NULL,
[Signed] [char](10) NULL,
[Printed] [int] NULL,
[DictationDoc] [nvarchar](30) NULL,
[OrgDocNum] [int] NULL,
[TimeStamp] [datetime] NULL,
[PrintedDate] [datetime] NULL,
[Location] [varchar](40) NULL,
)
CREATE TABLE [dbo].[ptSchedule](
[ScheduleId] [int] IDENTITY(1,1) NOT NULL,
[MRN] [varchar](25) NULL,
[ApptTypeID] [int] NULL,
[ResourceId] [int] NULL,
[StartDtTm] [datetime] NULL,
[EndDtTm] [datetime] NULL,
[Description] [nvarchar](1000) NULL,
[Notes] [nvarchar](100) NULL,
[Status] [int] NULL,
[CheckedInDtTm] [datetime] NULL,
[CheckedOutDtTm] [datetime] NULL,
[Flow] [nvarchar](50) NULL,
[ModifiedDtTm] [datetime] NULL,
[ModifiedByUserID] [int] NULL,
[WaitList] [int] NULL,
[ApptBlockID] [int] NULL,
[temp] [nvarchar](1000) NULL,
[EncounterID] [int] NULL,
)
October 14, 2008 at 8:19 am
Essentially, I will be providing indicators in the UI based on the values returned in the
DictationState column returned from the stored procedure based on this criteria:
•Green – An entry in ptDictation for that MRN/DOS with a status of approved.
•Grey – ptSchedule records linked to that Encounter do contain an OV, but
these is no entry in ptDictation for that MRN/DOS or if there is
an entry where the status is not approved.
•White – ptSchedule records linked to that Encounter do not contain an OV.
an OV is same as having ptSchedule.ApptTypeID = 5
DOS = DateOfService
October 14, 2008 at 8:25 am
Have you in-lined the functions yet? And if so, how much impact did that have?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply