September 16, 2011 at 8:11 am
I have this query very large having number of criteria that needs to run through more then one table.
I feel that I can improve this query of someone helps me with the performance improvement.
Please review attached two files one contains the criteria and one has the actual query I have written so far.
Here is the table Schema and sample data
CREATE TABLE Encounter(
EnconterID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
PatientID varchar(15),
SourceTime SmalldateTime,
VersionID INT,
Source INT,
CancelTime Smalldatetime
)
INSERT INTO Encounter
(PatientID,SourceTime,VersionID,Source)
SELECT 1,'01/21/2010',1,1 UNION ALL
SELECT 2,'02/13/2010',1,4 UNION ALL
SELECT 3,'07/08/2010',1,1 UNION ALL
SELECT 4,'03/19/2010',1,1 UNION ALL
SELECT 5,'04/29/2010',1,1
CREATE TABLE Diagnosis(
DiagnosisID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
PatientID varchar(15),
SourceTime SmalldateTime,
VersionID INT,
Source INT,
CancelTime Smalldatetime,
ICD9 varchar(40),
Diagnosis Varchar(255),
Comments Varchar(255)
)
INSERT INTO Diagnosis
(PatientID,SourceTime,VersionID,Source,ICD9,Diagnosis,Comments)
SELECT 1,'01/21/2010',1,1,'V70.5','Encounter Outpatient',NULL UNION ALL
SELECT 2,'02/13/2010',1,4,'V27','Encounter OBGYN',NULL UNION ALL
SELECT 3,'07/08/2010',1,1,'V70.8','Encounter Outpatient',NULL UNION ALL
SELECT 4,'03/19/2010',1,1,'V70.9','Encounter Outpatient',NULL UNION ALL
SELECT 5,'04/29/2010',1,1,'V70.9','Encounter Outpatient',NULL
CREATE TABLE Procedures2(
DiagnosisID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
PatientID varchar(15),
SourceTime SmalldateTime,
VersionID INT,
Source INT,
CancelTime Smalldatetime,
CPT varchar(40),
[Procedure] Varchar(255),
Comments Varchar(255)
)
INSERT INTO Procedures2
(PatientID,SourceTime,VersionID,Source,CPT,[Procedure],Comments)
SELECT 1,'01/21/2010',1,1,'99387','Encounter Outpatient',NULL UNION ALL
SELECT 2,'02/13/2010',1,4,'99211','Encounter Outpatient',NULL UNION ALL
SELECT 3,'07/08/2010',1,1,'99218','Encounter Outpatient',NULL UNION ALL
SELECT 4,'03/19/2010',1,1,'99242','Encounter Outpatient',NULL UNION ALL
SELECT 5,'04/29/2010',1,1,'99214','Encounter Outpatient',NULL
CREATE TABLE Medication(
DiagnosisID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
PatientID varchar(15),
SourceTime SmalldateTime,
VersionID INT,
Source INT,
CancelTime Smalldatetime,
Medication varchar(40),
Comments Varchar(255)
)
INSERT INTO Medication
(PatientID,SourceTime,VersionID,Source,Medication,Comments)
SELECT 1,'01/21/2010',1,1,'MMR Vaccine','762820' UNION ALL
SELECT 3,'02/13/2010',1,4,'Measles Vaccine','804183' UNION ALL
SELECT 5,'04/29/2010',1,1,'Rubella Vaccine','762820'
CREATE TABLE Allergy(
DiagnosisID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
PatientID varchar(15),
SourceTime SmalldateTime,
VersionID INT,
Source INT,
CancelTime Smalldatetime,
Allergy varchar(40),
Comments Varchar(255)
)
INSERT INTO Allergy
(PatientID,SourceTime,VersionID,Source,Allergy,Comments)
SELECT 2,'01/21/2010',1,1,'MMR Vaccine','762820' UNION ALL
SELECT 4,'02/13/2010',1,4,'Measles Vaccine','804183' UNION ALL
SELECT 6,'04/29/2010',1,1,'Rubella Vaccine','762820'
Create TABLE tempMU
(
Code Varchar(20),
CodeType varchar(40),
CodeDesc varchar(100)
)
INSERT INTO tempMU
(Code,CodeType,CodeDesc)
SELECT '99387','Procedure-CPT','Encounter Outpatient' UNION ALL
SELECT '99211','Procedure-CPT','Encounter Outpatient' UNION ALL
SELECT '99218','Procedure-CPT','Encounter Outpatient' UNION ALL
SELECT '99242','Procedure-CPT','Encounter Outpatient' UNION ALL
SELECT '99214','Procedure-CPT','Encounter Outpatient' UNION ALL
SELECT 'V70.5','Diagnosis-ICD9','Encounter Outpatient' UNION ALL
SELECT 'V27','Diagnosis-ICD9','Encounter OBGYN' UNION ALL
SELECT 'V70.8','Diagnosis-ICD9','Encounter Outpatient' UNION ALL
SELECT 'V70.9','Diagnosis-ICD9','Encounter Outpatient' UNION ALL
SELECT 'V70.9','Diagnosis-ICD9','Encounter Outpatient'
Thanks,
September 16, 2011 at 8:19 am
could you not use the ICD10 and or OPCS codes. I'm presuming you work in a Hosp.
Thanks
September 16, 2011 at 8:29 am
Please read this article[/url], to find out how you should be posting your query and about sample data which is required for us to help.
Obviously obfuscate your data in some way, but leave us with something that is the same type so that we can provide you with tested working code.
Please read this article[/url], to find out how you should be posting questions relating to performance.
Thanks.
--edit--
I hadn't even looked at your code when I posted the above. . . wow. That is a lot of work. What are you actually trying to do?
SELECT COUNT(a.patientid)
FROM (
SELECT DISTINCT encounter.patientid
FROM encounter
INNER JOIN patient ON encounter.patientid = patient.patientid
WHERE canceltime IS NULL
AND sourcetime BETWEEN @StartDate
AND @EndDate
AND Datediff(yy, sourcetime, dob) BETWEEN 1
AND 2
AND encounter.patientid IN (
SELECT p.patientid
FROM procedures2 p
WHERE p.canceltime IS NULL
AND p.sourcetime BETWEEN @StartDate
AND @EndDate
AND p.cpt IN (
SELECT code
FROM tempmu
WHERE codetype = 'Parocedure-CPT'
AND codedesc = 'Encounter Outpatient'
)
)
AND encounter.patientid IN (
SELECT d.patientid
FROM diagnosis d
WHERE d.canceltime IS NULL
AND d.sourcetime BETWEEN @StartDate
AND @EndDate
AND d.icd9 IN (
SELECT code
FROM tempmu
WHERE codetype = 'Diagnosis-ICD9'
AND codedesc IN (
'Encounter OBGYN'
,'Encounter Outpatient'
)
)
)
AND (
encounter.patientid IN (
SELECT m.patientid
FROM medication m
INNER JOIN patient ON m.patientid = patient.patientid
WHERE m.canceltime IS NULL
AND Datediff(dd, patient.dob, m.sourcetime) < 730
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Medication-RxNorm'
AND codedesc = 'MMR vaccine'
)
GROUP BY m.patientid
HAVING COUNT(m.patientid) > 1
)
OR (
encounter.patientid IN (
SELECT m.patientid
FROM medication m
INNER JOIN patient ON m.patientid = patient.patientid
WHERE m.canceltime IS NULL
AND Datediff(dd, patient.dob, m.sourcetime) < 730
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Medication-RxNorm'
AND codedesc = 'Mumps vaccine'
)
AND m.patientid NOT IN (
SELECT al.patientid
FROM allergy al
WHERE m.canceltime IS NULL
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Allergy-RxNorm'
AND codedesc = 'Mumps vaccine'
)
)
GROUP BY m.patientid
HAVING COUNT(m.patientid) > 1
)
AND encounter.patientid IN (
SELECT m.patientid
FROM medication m
INNER JOIN patient ON m.patientid = patient.patientid
WHERE m.canceltime IS NULL
AND Datediff(dd, patient.dob, m.sourcetime) < 730
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Medication-RxNorm'
AND codedesc = 'Mesales vaccine'
)
AND m.patientid NOT IN (
SELECT al.patientid
FROM allergy al
WHERE m.canceltime IS NULL
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Allergy-RxNorm'
AND codedesc = 'Mesales vaccine'
)
)
GROUP BY m.patientid
HAVING COUNT(m.patientid) > 1
)
AND encounter.patientid IN (
SELECT m.patientid
FROM medication m
INNER JOIN patient ON m.patientid = patient.patientid
WHERE m.canceltime IS NULL
AND Datediff(dd, patient.dob, m.sourcetime) < 730
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Medication-RxNorm'
AND codedesc = 'Rubella vaccine'
)
AND m.patientid NOT IN (
SELECT al.patientid
FROM allergy al
WHERE m.canceltime IS NULL
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Allergy-RxNorm'
AND codedesc = 'Rubella vaccine'
)
)
GROUP BY m.patientid
HAVING COUNT(m.patientid) > 1
)
)
OR encounter.patientid IN (
SELECT DISTINCT d.patientid
FROM diagnosis d
INNER JOIN patient ON d.patientid = patient.patientid
WHERE d.canceltime IS NULL
AND d.icd9 IN (
SELECT code
FROM tempmu
WHERE codetype = 'Diagnosis-SNOMED-CT'
AND codedesc = 'Measles'
)
AND d.patientid IN (
SELECT m.patientid
FROM medication m
INNER JOIN patient ON m.patientid = patient.patientid
WHERE m.canceltime IS NULL
AND Datediff(dd, patient.dob, m.sourcetime) < 730
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Medication-RxNorm'
AND codedesc = 'Mumps vaccine'
)
AND m.patientid NOT IN (
SELECT al.patientid
FROM allergy al
WHERE m.canceltime IS NULL
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Allergy-RxNorm'
AND codedesc = 'Mumps vaccine'
)
)
GROUP BY m.patientid
HAVING COUNT(m.patientid) > 1
)
AND d.patientid IN (
SELECT m.patientid
FROM medication m
INNER JOIN patient ON m.patientid = patient.patientid
WHERE m.canceltime IS NULL
AND Datediff(dd, patient.dob, m.sourcetime) < 730
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Medication-RxNorm'
AND codedesc = 'Rubella vaccine'
)
AND m.patientid NOT IN (
SELECT al.patientid
FROM allergy al
WHERE m.canceltime IS NULL
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Allergy-RxNorm'
AND codedesc = 'Rubella vaccine'
)
)
GROUP BY m.patientid
HAVING COUNT(m.patientid) > 1
)
)
OR encounter.patientid IN (
SELECT DISTINCT d.patientid
FROM diagnosis d
INNER JOIN patient ON d.patientid = patient.patientid
WHERE d.canceltime IS NULL
AND d.icd9 IN (
SELECT code
FROM tempmu
WHERE codetype = 'Diagnosis-SNOMED-CT'
AND codedesc = 'Mumps'
)
AND d.patientid IN (
SELECT m.patientid
FROM medication m
INNER JOIN patient ON m.patientid = patient.patientid
WHERE m.canceltime IS NULL
AND Datediff(dd, patient.dob, m.sourcetime) < 730
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Medication-RxNorm'
AND codedesc = 'Measles vaccine'
)
AND m.patientid NOT IN (
SELECT al.patientid
FROM allergy al
WHERE m.canceltime IS NULL
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Allergy-RxNorm'
AND codedesc = 'Measles vaccine'
)
)
GROUP BY m.patientid
HAVING COUNT(m.patientid) > 1
)
AND d.patientid IN (
SELECT m.patientid
FROM medication m
INNER JOIN patient ON m.patientid = patient.patientid
WHERE m.canceltime IS NULL
AND Datediff(dd, patient.dob, m.sourcetime) < 730
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Medication-RxNorm'
AND codedesc = 'Rubella vaccine'
)
AND m.patientid NOT IN (
SELECT al.patientid
FROM allergy al
WHERE m.canceltime IS NULL
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Allergy-RxNorm'
AND codedesc = 'Rubella vaccine'
)
)
GROUP BY m.patientid
HAVING COUNT(m.patientid) > 1
)
)
OR encounter.patientid IN (
SELECT DISTINCT d.patientid
FROM diagnosis d
INNER JOIN patient ON d.patientid = patient.patientid
WHERE d.canceltime IS NULL
AND d.icd9 IN (
SELECT code
FROM tempmu
WHERE codetype = 'Diagnosis-SNOMED-CT'
AND codedesc = 'Rubella'
)
AND d.patientid IN (
SELECT m.patientid
FROM medication m
INNER JOIN patient ON m.patientid = patient.patientid
WHERE m.canceltime IS NULL
AND Datediff(dd, patient.dob, m.sourcetime) < 730
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Medication-RxNorm'
AND codedesc = 'Measles vaccine'
)
AND m.patientid NOT IN (
SELECT al.patientid
FROM allergy al
WHERE m.canceltime IS NULL
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Allergy-RxNorm'
AND codedesc = 'Measles vaccine'
)
)
GROUP BY m.patientid
HAVING COUNT(m.patientid) > 1
)
AND d.patientid IN (
SELECT m.patientid
FROM medication m
INNER JOIN patient ON m.patientid = patient.patientid
WHERE m.canceltime IS NULL
AND Datediff(dd, patient.dob, m.sourcetime) < 730
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Medication-RxNorm'
AND codedesc = 'Mumps vaccine'
)
AND m.patientid NOT IN (
SELECT al.patientid
FROM allergy al
WHERE m.canceltime IS NULL
AND comments IN (
SELECT code
FROM tempmu
WHERE codetype = 'Allergy-RxNorm'
AND codedesc = 'Mumps vaccine'
)
)
GROUP BY m.patientid
HAVING COUNT(m.patientid) > 1
)
)
)
AND (
encounter.patientid NOT IN (
SELECT DISTINCT d.patientid
FROM diagnosis d
WHERE d.canceltime IS NULL
AND d.icd9 IN (
SELECT code
FROM tempmu
WHERE codetype = 'Diagnosis-SNOMED-CT'
AND codedesc IN (
'Cancer of Lymphoreticular or Histiocytic Tissue'
,'Asymptomatic HIV'
,'Multiple Myeloma'
,'Leukemia'
,'immunodeficiency'
)
)
)
OR encounter.patientid NOT IN (
SELECT al.patientid
FROM allergy al
WHERE canceltime IS NULL
AND code IN (
SELECT code
FROM tempmu
WHERE codetype = 'Allergy-RxNorm'
AND codedesc = 'IPV'
)
)
)
) AS a
September 16, 2011 at 8:30 am
Yes, it looks a mess and can certainly be optimised, but I don't think anyone here's going to spend the hours required to pick through it all and refactor it (that's what paid employees/consultants do).
Some general advise would be:
1) Most of the distincts look entirely redundant and are not needed
2) Some of these subqueries may be better to be re-written as NOT EXISTS although try it and see
3) Help yourself out and use a SQL formatter to at least show you where all the nested levels begin and end - Someone posted a good free online one yesterday
September 16, 2011 at 8:34 am
Using long list of this codes in query will make it dificult for me to read the query. That is wjhey I am generating a temporary table 'tempMU' to look for ICD9 and SNOMED-CT before the execution of this queries. This is only one of many queries.
September 16, 2011 at 8:36 am
This is a query for one of our Hospital client needs to submit Clinical measure report required by CDC. I have attached a word document which describes the Criteria for this report.
September 16, 2011 at 8:38 am
U made a nice indentation of the code. I like it, I used to do the same but that will put most of my code out of visible area of the screen.
September 16, 2011 at 8:40 am
I am just asking for suggestions, I never expect someone to rebuild muy query.
September 16, 2011 at 8:45 am
ranask_47 (9/16/2011)
This is a query for one of our Hospital client needs to submit Clinical measure report required by CDC. I have attached a word document which describes the Criteria for this report.
Your doc contains the following: -
?AND: “Patient characteristic: birth date” (age) >=1 year and <2 years to capture all patients who will reach 2 years during the “measurement period”;
?AND: All patients in the initial patient population;
?AND: “Encounter: encounter outpatient w/PCP & Obgyn”;
?AND: “Medication administered: MMR” >=1, occurring <2 years after the “Patient characteristic: birth date”;
oOR:
?AND: >1 count(s) of “Medication administered: mumps vaccine”, occurring <2 years after “Patient characteristic: birth date”;
?AND NOT: “Medication allergy: mumps vaccine”;
?AND: > 1 count(s) of “Medication administered: measles vaccine”, occurring <2 years after “Patient characteristic: birth date”;
?AND NOT: “Medication allergy: measles vaccine”;
?AND: >1 count(s) of “Medication administered: rubella vaccine”, occurring <2 years after “Patient characteristic: birth date”;
?AND NOT: “Medication allergy: rubella vaccine”;
oOR: “Diagnosis resolved: measles”;
?AND: >1 count(s) of “Medication administered: mumps vaccine”, occurring <2 years after “Patient characteristic: birth date”;
?AND NOT: “Medication allergy: mumps vaccine”;
?AND: >1 count(s) of “Medication administered: rubella vaccine”, occurring <2 years after “Patient characteristic: birth date”;
?AND NOT: “Medication allergy: rubella vaccine”;
oOR: “Diagnosis resolved: mumps”;
?AND: >1 count(s) of “Medication administered: measles vaccine”, occurring <2 years after “Patient characteristic: birth date”;
?AND NOT: “Medication allergy: measles vaccine”;
?AND: >1 count(s) of “Medication administered: rubella vaccine”, occurring <2 years after “Patient characteristic: birth date”;
?AND NOT: “Medication allergy: rubella vaccine”;
oOR: “Diagnosis resolved: rubella”;
?AND: >1 count(s) of “Medication administered: mumps vaccine”, occurring <2 years after “Patient characteristic: birth date”;
?AND NOT: “Medication allergy: mumps vaccine”;
?AND: >1 count(s) of “Medication administered: measles vaccine”, occurring <2 years after “Patient characteristic: birth date”;
?AND NOT: “Medication allergy: measles vaccine”;
oAND NOT:
?OR: “Diagnosis active: cancer of lymphoreticular or histiocytic tissue”;
?OR: “Diagnosis inactive: cancer of lymphoreticular or histiocytic tissue”;
?OR: “Diagnosis active: asymptomatic HIV”;
?OR: “Diagnosis active: multiple myeloma”;
?OR: “Diagnosis active: leukemia”;
?OR: “Medication allergy: MMR”;
?OR: “Diagnosis active: immunodeficiency”;
That doesn't explain what you're trying to do, it doesn't provide me with an actual execution plan from your query, it doesn't provide me with sample data or DDL. In fact, overall it's worth is pretty much nil.
What I meant was can you give an English description of what the query is intended to do. Also, DDL and Sample data (suitably obfuscated).
Also, please read this article --> http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
It will explain what we need from you to help with performance problems.
September 16, 2011 at 8:57 am
@SSchasing
I have attached the updated document which describes what I am looking for.
This what I want to compile from the database:
The percentage of children 2 years of age who had four diphtheria, tetanus and acellular pertussis (DTaP); three polio (IPV); one measles, mumps and rubella (MMR); two H influenza type B (HiB); three hepatitis B (Hep B), one chicken pox (VZV); four pneumococcal conjugate (PCV); two hepatitis A (Hep A); two or three rotavirus (RV); and two influenza (flu) vaccines by their second birthday.
The Criteria included in my doc file is just one of many criterias for this report.
September 16, 2011 at 10:15 am
Hi and welcome to SSC. The point Cadavre was making is that we don't know your data and there is nobody who is going to translate business rules into your data structures. Also, most people are not going to open up a Word document unless it somebody they know (too much nastiness in those things). Take a look at the link posted or the one in my signature about how to post questions in a format suited to get help. We are all volunteers around here so we would rather spend our time working on your problem instead of setting up tables and data.
_______________________________________________________________
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/
September 16, 2011 at 10:49 am
Another information that has been asked for is the actual execution plan.
As a pot shot I would probably extract the relevant data from Medication and Allergy into a temp table and work from there in order to reduce the number of calls to those tables. Maybe already reduced to the relevant patientid, but this would depend on the number of rows in each table and the (estimated) number of matching rows.
Or it would be enough to rewrite the query to reduce the number of separate selects against most of the tables.
September 16, 2011 at 1:23 pm
Hi,
I have attached an actual execution plan. It might not be as relevant because I am not working on Live environment as I have just wrote this query and felt that it might put to much laod on database when executed. Thats why I am looking for some suggestions to improve my query.
Thanks,
September 16, 2011 at 2:01 pm
It looks like the major issue is the table scan of the Diagnosis table and the Hash Match against TempMu. You might want to add an index on Diagnosis.ICD9 and to TempMu.Code.
The next issue would be to get rid of all those KeyLookups by adding the columns being looked up as included columns to the related index.
September 16, 2011 at 2:10 pm
ranask_47 (9/16/2011)
@SSchasingI have attached the updated document which describes what I am looking for.
This what I want to compile from the database:
The percentage of children 2 years of age who had four diphtheria, tetanus and acellular pertussis (DTaP); three polio (IPV); one measles, mumps and rubella (MMR); two H influenza type B (HiB); three hepatitis B (Hep B), one chicken pox (VZV); four pneumococcal conjugate (PCV); two hepatitis A (Hep A); two or three rotavirus (RV); and two influenza (flu) vaccines by their second birthday.
The Criteria included in my doc file is just one of many criterias for this report.
If all you want is a count of patients that have had certain vaccinations there is no need look at Diagnosis or Allergies. I mean you don't care what they were diagnosed with or what (or how many) thier allergies are. I'd take those joins out of your query making it a little more managable. Its a start anyway.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply