Efficient Database Access

When you start using the Translator with databases the “obvious” thing to do is to read and process the data in a From Translator component. We do not recommend this approach.

Our recommended best practice is structure the code to do as little work as possible in the From Translator. A powerful technique is to do most of the real work in a Filter Translator or To Translator.

How It Works

It’s easiest to think of a concrete example of feeding from a database.

For example, you can set up a From Translator component to poll a Patient table. The logic can be very simple:

1. In the From Translator simply push Patient IDs into the queue
2. Then in the Filter or To Translator component use those IDs to drive more elaborate logic:
   1. Pull an ID off the queue
   2. SELECT the data from the database to flesh out the message
   3. Perform business logic/processing
   4. Finally push the processed message onto the queue

The benefit of this is that the the polling code is completely separated from the business logic.

Any Exceptions to the Rule?

One exception to this is when you have a very slow source database. In this case it may be best to minimize database access by querying all the data in a From Translator.

You can structure the work flow like this:

1. Query the all the data you need to build up the message in the From Translator component
2. Then serialize that data into a convenient format like XML or JSON and enqueue it:
   • Serialize as XML
   • Serialize as JSON
3. Read the data from the queue in a Filter or To Translator and map it into the desired format
   1. Pull the message data off the queue
   2. Perform business logic/processing
   3. Finally push the processed message onto the queue

The benefit of doing things this way is that it minimizes queries to the “slow database”. The responsiveness of the Translator in step 3 will be snappy, since there are no slow blocking queries to the database involved. Step 1 may still be slow (because of the slow database), but it can be optimized to retrieve the data as efficiently as possible.

What’s Next?

We explained the principles of efficient database access. The next step is to look at these two practical examples:

• Polling a database and generating a CSV file, example from Kevin Senn
• Polling a database checking for flag fields to send out data, example from Ron Cheong

Please contact support at support@interfaceware.com if you need more help.