12

Background

I write a lot of large reports for and generally maintain a large health records DB (write SPs, functions, jobs, etc.). The original schema, and software that uses it, is from a different vendor, so I can't change much about it structurally. There are many records that require tracking such as labs, procedures, vaccines, etc. and they are scattered across dozens of tables, many of which are bloated and poorly indexed (I have been able to fix this somewhat).

The Problem

The issue is that because we have little control over the DB, and since it can change from any given update or patch, it makes writing and maintaining these reports difficult and tedious - especially when there's a large amount of overlap. All it takes is one patch and I'm stuck rewriting large portions of a dozen reports. Additionally, queries quickly become obfuscated and slow as the joins, nested selects, and applies pile up.

My "Solution"

My plan was to write all of these records to one "catch-all" table, and write triggers on the original tables to maintain the records in this aggregate table. Of course I'd need to ensure my triggers were intact after updates, but this would be much easier from a maintainability standpoint, and just referencing the data.

The table would be thin and long, storing only the data required, something like this:

CREATE TABLE dbo.HCM_Event_Log (
    id INT IDENTITY,
    type_id INT NULL,
    orig_id VARCHAR(36) NULL,
    patient_id UNIQUEIDENTIFIER NOT NULL,
    visit_id UNIQUEIDENTIFIER NULL,
    lookup_id VARCHAR(50) NULL,
    status VARCHAR(15) NULL,
    ordered_datetime DATETIME NULL,
    completed_datetime DATETIME NULL,
    CONSTRAINT PK_HCM_Event_Log PRIMARY KEY CLUSTERED (id)
)

Then I'd have various relational tables for things like the type_id and item groupings.

I'm starting to second guess this idea as several of these tables are written to quite a bit, the SPs and reports I'd be writing would reference the data a lot as well. So I'm concerned that this table would become a record locking and performance nightmare with so much I/O.

My Question

Is a bad or a good idea? I realize every situation is different in SQL Server (2008 r2 Standard Edition BTW), and the "sometimes" rule, but I'm really just looking for general advice.

I started to consider using a service broker, but I'd only be performing simple updates/inserts (See the alternative to the accepted answer). The data in many cases needs to be realtime, so using a backup DB wouldn't really work. Performance is already somewhat of an issue for us, but most of that is hardware related that'll be resolved soon.

  • 1
    Can you enforce planned outages? If not one of those updates could wipe out a trigger and you won't update your aggregates possibly leading to bad data. – Erik Aug 14 '15 at 4:36
  • You're considering putting all the information about labs, and about procedures, and about vaccines, and about patients into a single table? Bad idea. You may be able to use a star schema, if that suits the type of queries you're running. – Michael Green Aug 14 '15 at 12:16
  • 1
    Have you looked into creating some indexed views? These would put a logical layer between your code and the vendor's so you can just update the view if the vendor changes things underneath. Also, the indexed views would be prepopulated for you and provide good read performance. One of the bigger considerations in doing this is how much load it puts on the write operations of the vendor's database tables. However, this would likely be a cleaner and easier to maintain solution than using triggers, etc. – Micah Nikkel Aug 14 '15 at 21:00
  • Sorry for the late reply guys, thanks for the feedback. @Erik - Yes, we have planned updates, and I check to ensure all of my previous changes are still in place via a series of checklist scripts I run, so there won't be any surprises there and I'll keep CREATE scripts for all the triggers. – jreed121 Aug 17 '15 at 22:24
  • @MichaelGreen - I'll look into a star schema, but I'm curious why you think having all that data in one table is a bad idea? The application environment is completely isolated on a VPN, it's not accessible outside the network in anyway. If something goes wrong with the table, then it's not the end of the world because I could just write everything back to it. The table won't be used for mission-critical data, or at least it won't be the only, nor primary, place that data is stored. – jreed121 Aug 17 '15 at 22:28
8

If I understood you correctly,

  • you have a large third-party system,
  • you don't have much control over it,
  • you make complex reports that read data directly from this third-party database,
  • your queries depend on the internal structure of the third-party database.

I would approach it like this:

  • Set up my own separate database, which I have full control of.
  • Set up a sync process that reads data from relevant tables and columns from the third-party database and inserts/updates into mine.
  • Develop my complex reports based on the stable structure of my database.

In this case you can fine-tune structure and indexes of your database to improve performance of your reports, without affecting third-party system. Unless the original data structure changes dramatically, the logic of your queries for your reports would not change if third-party database changes. You would have to adjust only the sync process.

The sync process is effectively the conversion process - you convert data from third-party database into the structure that you need. Part of this conversion process could be fixing any normalization problems that original third-party database may have. Only this part of the system has to know and depend on the internal structure of third-party system. Your main reports and main queries would depend only on your database.

So, the main point is - separate and limit the part of your system that depends on internals of third-party system.

update

Regarding real-time requirement. BTW, I always thought that definition of "real-time" is "guaranteed response time", not "some small response time". It depends on your application, of course. In my practice it is enough if I sync two databases within a minute of detected change. If a user sees a report on screen and some underlying data changes, the report has to be somehow re-run to reflect this change. You can poll for changes or listen to some event/message, still the report query has to be executed again to show the latest changes.

You already intend to write triggers to capture changes in the original tables and write these changes to one generic table. So, capture changes as you intended, but write them to properly normalized tables, not a single one.

So, this is extreme case - conversion of third-party data structure into your internal data structure is performed in the triggers that fire on INSERT/UPDATE/DELETE of third-party tables. It can be tricky. The code of triggers would depend on internal structure of both systems. If conversion is non-trivial it may delay original INSERT/UPDATE/DELETE to the point of their failure. If there is a bug in your trigger it may affect original transaction to the point of their failure. If third-party system changes it may break your trigger, which would cause transactions of third-party system to fail.

Less extreme case. To make the code of your triggers simpler and less prone to errors write all captured changes to some staging/audit/diff table(s), set some flag/send a message that there are changes pending and launch the main conversion process that would go through these intermediary tables and perform conversion. The main thing here is that potentially heavy conversion process should happen outside the scope of the original transaction.

At a second glance it looks pretty much like your original suggestion in the question. But, the difference is: the capture-all tables hold data only temporarily; the amount of data is small - just what has changed; it doesn't have to be a single table; eventually the data will be stored in separate properly normalized permanent tables, which you have full control of, which are independent from third-party system and which you can tune for your queries.

  • If you going the batch transfer route, we've had success with Change Tracking (and Change Data Capture, depending on your needs) at quite high transaction counts (100K per day). It's simpler than implementing your own staging/ audit/ diff tables and can be deployed without application code change or triggers. – Michael Green Aug 18 '15 at 11:14
  • Be it triggers or CDC, the only way you'll really get close to real-time is streaming or queuing. Queue based is a good compromise for latency and cost effectiveness. Your time will be spent on methods to process the queue faster. leaving the majority of the work asynchronous from the application, and putting less load on the user transactions. In the past I've done this very thing against the Allscripts Sunrise EMR with a service that processed the queue with some parallel foreach C# calls. typical latency for new data to be processed and available in the warehouse was sub 30 sec – Brad D Aug 18 '15 at 15:44
  • I may have over stated "real-time," I'm not too concerned with milliseconds or even 5 seconds, but I have many queries that our staff rely on to drive workflow. If a client's had something done to them (procedure, immunization, etc.) we'll need to show that in a short period. The conversions are trivial, and/or not even conversions. I'm not overly concerned with change to the vendor tables, as they don't change that often, and I have to do it now anyways, but my thought was that it's easier to update/recreate one trigger than dozen of reports/queries/SPs. I run checks after every update. – jreed121 Aug 19 '15 at 18:40
  • @jreed121, I also think that it is easier to update trigger(s) than reports. You'll likely have a trigger on each source table to capture the changes, so it is likely to be more than one trigger. Still, do not try to write all these captured changes into one huge denormalized table. Write them to a properly normalized set of tables. Your reports should be based on these normalized tables that you control and should not depend on original tables that may change. – Vladimir Baranov Aug 20 '15 at 2:53
3

By all means put it into a standardised set of tables so that you can tweak the import stage rather than having to change complex report(s) and queries. But the data should still be normalised which will require multiples tables (but with good indexes).

As others have mentioned, don't use triggers, sync in batches.

Don't worry about lots of joins, when data is normalised and indexed properly these do not add any significant cost or management burden.

The time to denormalise into something like a data warehouse is when you need to be able to make lots of different types of query on the data that you can't predict. It has it's own disadvantages and overheads and should be used where appropriate, not as a go-to thing.

3

I worked with a very similar situation like this in the past in a 24x7 manufacturing company and finally decided to use transactional replication. It is possible to configure DDL to be replicated such that you can push out whatever the patches change to the subscriber. Obviously there are pros and cons to everything and you need to weigh them to determine what you can support against what works best for the company.

On the positive side:

  1. "Real-time" is limited only to network and transaction commit performance on the subscriber. In my experience with moderately high TPS system, we were replicated to within less than 10 seconds of "real-time" data.
  2. Separation of workloads. You're currently running a mixed workload on one server. If you can separate out these two concerns, then you may be able to get the performance benefits on both systems of having removed one workload from the equation
  3. Control. You'll be able to make indexing/stats/maintenance modifications to suit your reporting workload.

There are cons, though:

  1. Cost. Another license and more hardware (virtual, or otherwise).
  2. Replication. It works great once it's been setup properly, but it can be a hassle to get to that point.
  3. Maintenance. If you make any deleterious changes to the structures (e.g. drop an index), they will return when the snapshot is applied (after the publication has changed or when the articles have changed).
2

My plan was to write all of these records to one "catch-all" table, and write triggers on the original tables to maintain the records in this aggregate table.

Triggers have so many problems you should avoid them:

  • An error in a trigger can cause the original transaction to abort
  • Triggers that correctly handle multi-row operations are hard to write
  • Triggers can confuse client applications by modifying the returned rowset (for example, a trigger overrides the number of affected rows)
  • When one trigger triggers another, the results are hard to predict

A better option is a job that periodically copies the data to a new table. Your reports can run of the copy. A job that copies rows is easy to write and maintain, and it there is no risk that it will affect the operation of the third party application.

  • 1. The triggers would be simple, so thrown errors would be minimal if existent at all. 2. The trigger itself wouldn't be handling multiple rows (IE one row updated in the table with the trigger wouldn't cause multiple rows to be updated elsewhere), but multiple rows could be inserted/updated/deleted at once in the source table - is this what you mean? 3. couldn't this be handled with NOCOUNT? 4. There wouldn't be any triggers on the destination table, and I could ensure the same for the others. – jreed121 Aug 19 '15 at 18:48
  • Like you say, it is theoretically possible to make triggers work. It's just that in practice they never do. – Andomar Aug 20 '15 at 4:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.