View for real-time list pulls in an OLTP
-
Hello,
I’ve been struggling with finding a good way to create a View with de-normalized data from a few normalized tables (in an OLTP) with 1-to-many relationships. The image I’ve attached contains a very simplified version of the schema and the desired format of the view ( in reality there are more tables and a few dozen columns in the resulting view). Each of the underlying tables contains 2-3 million rows. I’ve created a couple of versions of the code (120+ lines; I've attached a stripped down version/pseudocode). The results are correct but selecting from the view is terribly slow, not surprisingly so with 6-7 left and inner joins. The environment is SQL Server 2012.
There is an application that needs to be able to pull lists from this view that get updated in near-real time.
I thought about a number of other solutions but they don't work for different reasons (persist the data once calculated and read from a table-> not possible because not all data is static; set up CDC on the source tables + schedule SP’s to update a separate table that could be used for these list pulls -> more latency and processing time; materialized view -> not possible with all the left joins; tried rewriting the view but it was slower). I also did quite a bit of reading of blog posts and forums on similar topics and don’t seem to be finding a better way to solve this … and I know there should be at least one.
Is there a better way to do this – both in terms of solution of the problem (reading from a large de-normalized data set in an OLTP) and the logic of the view?
Any help would be much appreciated!
Thank you,
Vilya
-
complex - and a few answers required.
your code only contemplates 2 treatments and 2 courses - what if there are 5 or 10 treatments? or if there are more than 2 courses per treatment?
Just an initial look at what you posted looks like a good target for pivot/cross join as it seems you wish to have records grouped by HospitalPatientID, but, as others will most likely point out, it would be great if you could post ddl to create the tables (even if not all columns), sample data and sample desired output of that data.
-
frederico_fonseca (9/17/2016)
complex - and a few answers required.your code only contemplates 2 treatments and 2 courses - what if there are 5 or 10 treatments? or if there are more than 2 courses per treatment?
Just an initial look at what you posted looks like a good target for pivot/cross join as it seems you wish to have records grouped by HospitalPatientID, but, as others will most likely point out, it would be great if you could post ddl to create the tables (even if not all columns), sample data and sample desired output of that data.
Thank you for responding.
Only the first 2 treatments are of interest + only the first 2 therapy courses & the first 2 medication courses for each of the 2 treatments (so 10 main attributes for each HospitalPatientID). There won't be any more complexity to the code but I wonder if its possible to make it less, or if this view is ever the best way to provide a source data sets for list pulls on these attributes. I hesitate posting actual data but will make some up.
-
That helps.
regarding data by all means do not put real data.
something like
patient 1
patient 2
treatment 1
treatment 2
will do fine as long as the joins work.
And I hope you have dummy data on your development database - if not so do speak with your managers as that is a big NO NO on this industry.
-
Hi, I've attached the DDL, sample data, the View ad a code snippet.
-
It seems the design is faulty.
PatientMedicationCourse must be allocated to PatientTherapyCourse, not directly to PatientTreatment.
Medications prescribed for one Therapy Course must not be mixed with medications for another one.
Relying on "First" and "Second" based on ID's is all fun and games until a patient dies because medications for the 1st course were recorded in the database after medications for the 2nd one.
In such case medications for 1st course will be given during 2nd one, and another way around.
With possibly very grim outcome.
And it's not gonna be fun at all when an investigation of the cause of death will lead to the medication mix-up caused by your view.
Until table PatientMedicationCouse has a column PatientTherapyCourseID with a FK referencing PK column in PatientTherapyCourse table - don't touch it under any circumstances.
It's better to be looking for a new job than to serve a term for a manslaughter.
_____________
Code for TallyGenerator -
I agree with what Sergiy stated. There's a pretty easy solution to this request (I just wrote it and tested it) but found a similar horrible problem to that which Sergiy identified and for the same reason he identified. Being interested in not killing people and in protected you and the others from certain jail terms, I can't ethically post the solution. The database design needs to be changed before anyone posts a solution for this.
I hope that everyone heeds the same warning. This database design could kill people. Don't post a solution!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hum..
just for record I have given vilyana a working bit of code prior to the posting of the code above which I can't download at all as it gives an error so I can't even see what issues exist on the design.
Does anyone else have the same problem downloading the last link?
-
vilyana (9/18/2016)
Hi, I've attached the DDL, sample data, the View ad a code snippet.cant download......
The http://www.sqlservercentral.com page isn’t workinghttp://www.sqlservercentral.com sent an invalid response.
ERR_RESPONSE_HEADERS_MULTIPLE_CONTENT_DISPOSITION
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
frederico_fonseca (9/19/2016)
Hum..just for record I have given vilyana a working bit of code prior to the posting of the code above which I can't download at all as it gives an error so I can't even see what issues exist on the design.
Does anyone else have the same problem downloading the last link?
Thanks a lot for the code! It generates the same results and is A LOT faster than mine.
I've modified it to work on the schema I attached earlier and adding a .txt file here.
Really appreciate the help!!
Vilyana
ps: adding the DDL & sample data again (not sure what others couldn't open it)
-
Sergiy (9/19/2016)
It seems the design is faulty.PatientMedicationCourse must be allocated to PatientTherapyCourse, not directly to PatientTreatment.
Medications prescribed for one Therapy Course must not be mixed with medications for another one.
Relying on "First" and "Second" based on ID's is all fun and games until a patient dies because medications for the 1st course were recorded in the database after medications for the 2nd one.
In such case medications for 1st course will be given during 2nd one, and another way around.
With possibly very grim outcome.
And it's not gonna be fun at all when an investigation of the cause of death will lead to the medication mix-up caused by your view.
Until table PatientMedicationCouse has a column PatientTherapyCourseID with a FK referencing PK column in PatientTherapyCourse table - don't touch it under any circumstances.
It's better to be looking for a new job than to serve a term for a manslaughter.
Hi Sergiy,
Thank you for responding and for noticing the odd relationships.
For this particular business case the schema is correct - it doesn't associate 'Therapy' with 'Medication Course' because of the types of Therapies included (btw I had the same question when I first saw it). Also, as I mentioned, this is a very small subset of the DB tables - seeing the rest would make it clear, and the ERD in this post is meant to be a reference only for this question.
Thanks again.
Vilyana
-
Many thanks to everyone who responded!
Vilyana
-
vilyana (9/19/2016)
Sergiy (9/19/2016)
It seems the design is faulty.PatientMedicationCourse must be allocated to PatientTherapyCourse, not directly to PatientTreatment.
Medications prescribed for one Therapy Course must not be mixed with medications for another one.
Relying on "First" and "Second" based on ID's is all fun and games until a patient dies because medications for the 1st course were recorded in the database after medications for the 2nd one.
In such case medications for 1st course will be given during 2nd one, and another way around.
With possibly very grim outcome.
And it's not gonna be fun at all when an investigation of the cause of death will lead to the medication mix-up caused by your view.
Until table PatientMedicationCouse has a column PatientTherapyCourseID with a FK referencing PK column in PatientTherapyCourse table - don't touch it under any circumstances.
It's better to be looking for a new job than to serve a term for a manslaughter.
Hi Sergiy,
Thank you for responding and for noticing the odd relationships.
For this particular business case the schema is correct - it doesn't associate 'Therapy' with 'Medication Course' because of the types of Therapies included (btw I had the same question when I first saw it). Also, as I mentioned, this is a very small subset of the DB tables - seeing the rest would make it clear, and the ERD in this post is meant to be a reference only for this question.
Thanks again.
Vilyana
But, if it's an accurate subset, the "safety" issues that Sergiy first mentioned and that I later confirmed are still an issue. Someone needs to change that "subset" so that they don't kill someone. And, you have to know that if something like that happens, it's all going to flow down hill and, like it or not, you're down hill.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
vilyana (9/19/2016)
Sergiy (9/19/2016)
It seems the design is faulty.PatientMedicationCourse must be allocated to PatientTherapyCourse, not directly to PatientTreatment.
Medications prescribed for one Therapy Course must not be mixed with medications for another one.
Relying on "First" and "Second" based on ID's is all fun and games until a patient dies because medications for the 1st course were recorded in the database after medications for the 2nd one.
In such case medications for 1st course will be given during 2nd one, and another way around.
With possibly very grim outcome.
And it's not gonna be fun at all when an investigation of the cause of death will lead to the medication mix-up caused by your view.
Until table PatientMedicationCouse has a column PatientTherapyCourseID with a FK referencing PK column in PatientTherapyCourse table - don't touch it under any circumstances.
It's better to be looking for a new job than to serve a term for a manslaughter.
Hi Sergiy,
Thank you for responding and for noticing the odd relationships.
For this particular business case the schema is correct - it doesn't associate 'Therapy' with 'Medication Course' because of the types of Therapies included (btw I had the same question when I first saw it). Also, as I mentioned, this is a very small subset of the DB tables - seeing the rest would make it clear, and the ERD in this post is meant to be a reference only for this question.
Thanks again.
Vilyana
Schema is clearly incorrect.
There is nothing what binds any medication course to any particular treatment course.
So, if you have 2 treatment courses and 2 medication courses then the patient takes medications 4 times: 2 courses during treatment course 1 and 2 medication courses during treatment course 2.
Which is obviously wrong.
And you know it, because you're trying to connect "first treatment to first medication". But you definition of "first" is based on ID's which are internal identities and do not carry any business logic.
Changing internal processing sequence will cause wrong medication to be allocated to a treatment, with unpredictable circumstances.
Then you'll have plenty of time to explain why your small subset of tables was made in this particular way- to the judge.
_____________
Code for TallyGenerator -
Thank you for the concern.
The ERD and the View columns in the image are exactly what they need to be to illustrate the starting point and desired result (and this is all they are intended to be). Every other piece of info is removed to simplify as much as possible and focus on the actual question which @frederico_fonseca provided a solution for (clearly faster and better than mine - I used the pattern to write the full solution on the real tables) . And yes, 0 to N Treatments, 0 to N Therapies for each treatment, 0 to N Med Courses for each treatment are possible (as shown in the erd), but only the first 2 of each based on ID (not date) are required in this particular view.
The only problem with the ERD is that the column and table names are not obscured with a generic naming convention so there won't be anyone picturing over-medicated (or worse) patients without knowing anything about the context, purpose or the actual system. I wonder if I can still finish the last semester of grad school and work full time from jail? Ugh, it would be really unfortunate getting locked up for limiting the info on a sample diagram and column names in a post 🙂 I promise to use Tbl1, Col1,.., ColN next time I ask for help with improving my SQL code.
I've marked the working solution to my question. Now I’ll be going back to assisting people who need care.
Kind regards,
Vilyana
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply