DR+SQL Tip: Using UNION to Best Effect

MEDITECH migrations platform bring many challenges; one of them being that you may find yourself with a new LIVExDB in the picture and once you’re past the go-live you find that data is flowing only into the new LIVExDB and not into the old one anymore.

This is particularly noticeable when you have a migration from (for example) MAGIC to MT 6.1. This gets you a database scenario like:

livemdb (the old MAGIC environment data)
livefdb (data from the new MAT modules, post go-live)
livendb (data from the new C/S NPR modules, post go-live)

If you have DR-based reports in play already, when this change occurs (say on June 1st, 2015, as an example), then they are going to stop having new data in them on the magical migration day.

Then you need to update your reports – but how?

You could build completely new reports pointing at the new livefdb and livendb databases, but then your end-users will have to run two separate reports to get data about any patients admitted before the go-live and discharged after… and any kind of historical record-of-service reports will also have to be run separately.

Not so very convenient for your end-users!

SQL provides two solutions, however, and we will look at one of them here: using the UNION ALL keyword.

All In One

UNION ALL lets us run two (or more) separate queries against the DR server and combine the results; if and only if, the number of columns in the two queries matches and the datatypes of each column match. This lets us do something like:

-- First from Magic

SELECT MAV.UnitNumber
,MAV.VisitID
,MAV.AccountNumber
,COALESCE(MAV.ServiceDateTime,MAD.AdmitDateTime) AS ArrivalDateTime
,MAV.Status
FROM livemdb.dbo.AdmVisits MAV

LEFT JOIN livemdb.dbo.AdmittingData MAD ON ( MAD.VisitID = MAV.VisitID AND MAD.SourceID = MAV.SourceID )

WHERE COALESCE(MAV.ServiceDateTime,MAD.AdmitDateTime)
BETWEEN CONVERT(DATETIME,'2015-01-01 00:00:00',120)
AND CONVERT(DATETIME,'2015-12-31',120)
UNION ALL

-- Then from C/S

SELECT CAV.UnitNumber
,CAV.VisitID
,CAV.AccountNumber
,COALESCE(CAV.ServiceDateTime,CAD.AdmitDateTime) AS ArrivalDateTime
,CAV.Status
FROM livendb.dbo.AdmVisits CAV

LEFT JOIN livendb.dbo.AdmittingData CAD ON ( CAD.VisitID = CAV.VisitID AND CAD.SourceID = CAV.SourceID )

WHERE COALESCE(CAV.ServiceDateTime,CAD.AdmitDateTime)
BETWEEN CONVERT(DATETIME,'2015-01-01 00:00:00',120)
AND CONVERT(DATETIME,'2015-12-31',120)

When we launch this query the SQL engine splits the work into two parallel queries and executes them simultaneously. So performance can be quite good. MEDITECH’s data structure is challenging to speed, however, because we have to look at two (or more) different fields in different tables to get the ArrivalDateTime.

However, this will get us visits on either side of the gap at go-live and since the fields line up in number and type, the report we build on top of this won’t know the difference. And neither will the user!

Dangers of Union

One gotcha to watch out for with UNION, however, is that since each section runs in parallel with one another; if each part is hitting the same tables in the same database, sometimes they block each other – and then performance falls off a cliff while each waits for the other to release database page locks. And this is bad.

So our rule of thumb is to avoid using UNION with queries accessing the same tables in the same database. In our example, we don’t have this issue – the table names may be the same, but they are in entirely different databases.

In the version of UNION that we’ve looked at so far, we have the "ALL" keyword added – this just combines the results of the two queries into a single result-set.

Union to Unique

But there is also just plain old UNION, which compares the two result-sets and discards any duplicates, producing a single set of unique rows. That is sometimes useful, but that requires more overhead to compare two sets and produce a third to return to you.

Alternatives

If you find that you need to run two or more sets of queries against the same sets of tables and combine the results; you don’t want a UNION at all. That will get you the performance issues we’ve just mentioned. In this scenario you:

1. Create a temp table with a common structure.
2. Run each query in sequence, INSERT-ing the results into the temp table.
3. Return the whole contents of the temp table to your report or extract.

(end)

Visit our report library at http://www.iatric.com/Information/NPRReportLibrarySearch.aspx.

You can find additional Report Writing Tips on our website at http://www.iatric.com/Information/NPRTips.aspx, as well as information about our on-site Report Writer Training and Report Writing Services.

To subscribe for email notifications for new Report Writing classes, please follow this link:

http://www.iatric.com/Information/Classes.aspx.

For more information, please contactor our NPR report writing team at reportwriting@iatric.com.