September 16, 2011 at 2:25 pm
is there any other way to write this query to improve efficiency? As the indexes are not the issue our database is very much evolved.
Here is the table structure 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'
September 16, 2011 at 2:29 pm
Count of patients who got vaccinated is not all that I want. It needs to be filtered with certain criterias.
Thats why I have also attached the criteria in word file.
September 16, 2011 at 2:59 pm
ranask_47 (9/16/2011)
is there any other way to write this query to improve efficiency? As the indexes are not the issue our database is very much evolved.Here is the table structure 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'
Please tell me this is NOT how the production tables are designed. If it is where are you located because I do not want to be a patient needing care at this hospital/clinic.
I've worked with hospital data for close to 20 years (prior to them going to SQL & when they converted to SQL) in prior jobs & they are designed for retrieving everything about ONE patient at a time (so the PatientID is the Primary Key on 90% tables), you want the physician to know everything about YOU. What you have posted has none of that.
I see no keys linking your tables to each other & those keys need to be Indexed in some way, so you are going to be getting tables scans on every table. Just my opinion anyway. /end rant
September 19, 2011 at 7:25 am
This is just an extract of the original tables, this is only to show you the table design and with only those fields those I requre in my queries.
I don't need to givve you the entire structure of my database here. I showed only those fields those I need here in my queries.
September 19, 2011 at 11:45 am
Ranask, we need to see the index definitions as well, the schema is only half the battle... and speaking of, I understand the desire to only give us the columns you feel are necessary, but tell me what you would see here:
(Some table, Cola INT colb INT colc INT)
... wierd, 5000 rows shouldn't take that long, can you fully drop the schema?
... if I must...
(some table, colA INT, colB DECIMAL 33,5, colC INT, WordDoc1 nText)
WITH fillfactor = 5
Minor difference.
Everything matters when you start looking to optimize. We're not going to tear into your business rules and build it for you, but we will help you with the existing build if we have enough information. If you were looking for a logical failure just the query itself would be useful. Since you're looking for an optimization failure, we need a LOT more, to start, and from there we start actually asking questions.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply