October 15, 2008 at 7:32 am
rbarryyoung (10/14/2008)
Have you in-lined the functions yet?
HUH?
October 15, 2008 at 7:58 am
MrBaseball34 (10/15/2008)
rbarryyoung (10/14/2008)
Have you in-lined the functions yet?HUH?
He means stripping the time directly in your query, rather than calling the UDF you posted earlier to do it for you.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 15, 2008 at 8:10 am
Sorry, I was using programmer's slang (not even jargon, really).
[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 15, 2008 at 8:15 am
rbarryyoung (10/15/2008)
Sorry, I was using programmer's slang (not even jargon, really).
Isn't jargon just a fancy word for slang? 😛
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 15, 2008 at 8:31 am
jcrawf02 (10/15/2008)
rbarryyoung (10/15/2008)
Sorry, I was using programmer's slang (not even jargon, really).Isn't jargon just a fancy word for slang? 😛
Actually, no, not IMHO. But there are many who disagree (whoever wrote the Wikipedia article, for instance). IMHO, jargon the is formally correct "technical language" of a genre. As such, English Jargon is still correct English (though only within its genre).
Slang on the other hand, are shorthand words and expression that are conventionally understood within a community or subculture. English slang is never correct English, though over time it may come to be.
Using my definitions then, jargons can develop their own slangs (which may eventually become part of the jargon). So "in-lined" is not really technically correct jargon (though it is close), but is generally understood by a subculture or programmers (code-tweakers), making it slang rather than jargon.
But that's just my opinion. 😛
[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 15, 2008 at 10:51 am
Yes, just as Gary showed but it didn't help.
October 15, 2008 at 11:15 am
Gary...
This one returned 1481416 records in 23 secs...
HOWEVER, there are only 340501 records in the ptEncounter table.
Gary Johnson (10/10/2008)
SELECT e.MRN,
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 e.MRN = d.MRN
AND d.DOS = e.DateOfService
AND d.Status = 'Approved'
LEFT JOIN ptSchedule s
ON e.MRN = s.MRN
AND CONVERT(varchar,s.StartDtTm, 10) = CONVERT(varchar,e.DateofService, 10)
AND s.ApptTypeID = 5
--WHERE e.MRN = '5079' -- {Want to return all for testing}
October 15, 2008 at 11:55 am
Ok, how about the following:
CREATE PROCEDURE dbo.PTENCOUNTER_STUFF (
@MRN varchar(5), -- '5079'
@DIC_Status varchar(10),
@ApptTypeID smallint
)
AS
BEGIN
SET NOCOUNT ON
;WITH DICTATION AS (
SELECT MRN, Status, DOS
FROM ptDictation
WHERE MRN = @MRN AND
Status = @DIC_Status
),
SCHEDULE AS (
SELECT MRN, StartDtTm
FROM ptSchedule
WHERE MRN = @MRN AND
ApptTypeID = @ApptTypeID
)
SELECT
CASE
WHEN d.MRN IS NOT NULL AND d.DOS = e.DateOfService THEN 2
WHEN s.MRN IS NOT NULL AND
DATEADD(dd,0, DATEDIFF(dd,0,s.StartDtTm)) = DATEADD(dd,0, DATEDIFF(dd,0,e.DateofService)) THEN 1
ELSE 0
END as DictationState
FROM ptEncounter AS e LEFT OUTER JOIN DICTATION AS d
ON e.MRN = d.MRN
LEFT OUTER JOIN SCHEDULE AS s
ON e.MRN = s.MRN
WHERE e.MRN = @MRN
END
GO
It should do the work for at least this piece of the SP... And you can always just incorporate this code into the main procedure rather than having it be it's own procedure. Remember that with no test data to work with, this code has only had a syntax check.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 15, 2008 at 12:14 pm
Can you de-identify the data to meet HIPAA reqs? John/Jane Doe and scramble the id numbers or something?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 15, 2008 at 12:53 pm
MrB -
Did you ever get a chance to try my version? Assuming you get the right results - it should be a little faster than your original version.
----------------------------------------------------------------------------------
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 15, 2008 at 4:10 pm
Matt Miller (10/15/2008)
MrB -Did you ever get a chance to try my version? Assuming you get the right results - it should be a little faster than your original version.
I killed it after 3:25 and only 80,000 or so records. I have to take out the where for the
MRN as this is supposed to be returning the DictationState for ALL encounter records.
October 15, 2008 at 4:12 pm
jcrawf02 (10/15/2008)
Can you de-identify the data to meet HIPAA reqs? John/Jane Doe and scramble the id numbers or something?
I will try but there are 340,000 ptEncounter records, 610,000 ptDictation records and
163,000 ptSchedule records.
October 15, 2008 at 4:13 pm
MrBaseball34 (10/15/2008)
jcrawf02 (10/15/2008)
Can you de-identify the data to meet HIPAA reqs? John/Jane Doe and scramble the id numbers or something?I will try but there are 340,000 ptEncounter records, 610,000 ptDictation records and
163,000 ptSchedule records.
just pull a sample, I think you said there were 2 encounters, 9 dictations, 2 schedules, something like that. Just enough to make your problem clear.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 15, 2008 at 4:17 pm
smunson (10/15/2008)
Ok, how about the following:
CREATE PROCEDURE dbo.PTENCOUNTER_STUFF (
@MRN varchar(5), -- '5079'
@DIC_Status varchar(10),
@ApptTypeID smallint
)
AS
BEGIN
SET NOCOUNT ON
;WITH DICTATION AS (
SELECT MRN, Status, DOS
FROM ptDictation
WHERE MRN = @MRN AND
Status = @DIC_Status
),
SCHEDULE AS (
SELECT MRN, StartDtTm
FROM ptSchedule
WHERE MRN = @MRN AND
ApptTypeID = @ApptTypeID
)
SELECT
CASE
WHEN d.MRN IS NOT NULL AND d.DOS = e.DateOfService THEN 2
WHEN s.MRN IS NOT NULL AND
DATEADD(dd,0, DATEDIFF(dd,0,s.StartDtTm)) = DATEADD(dd,0, DATEDIFF(dd,0,e.DateofService)) THEN 1
ELSE 0
END as DictationState
FROM ptEncounter AS e LEFT OUTER JOIN DICTATION AS d
ON e.MRN = d.MRN
LEFT OUTER JOIN SCHEDULE AS s
ON e.MRN = s.MRN
WHERE e.MRN = @MRN
END
GO
It should do the work for at least this piece of the SP... And you can always just incorporate this code into the main procedure rather than having it be it's own procedure. Remember that with no test data to work with, this code has only had a syntax check.
Steve
(aka smunson)
:):):)
Steve, in this case, I need it to work for all ptEncounter records, not just one MRN/DOS combination passed in and I need to check the ptDictation for 'Approved' status and ptScedule for AppTypeID = 5.
October 15, 2008 at 4:18 pm
I guess I can but getting past the speed issue seems to be the biggest problem.
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply