First to understand what we mean by latency in the context of what we did. It has to do with the network speed of your connection to your MS SQL Server and how fast SQL Commands can traverse the TCP/IP connection to SQL Server. If the SQL Server is "close" to the SQL Engine software (IE on the same computer or nearby on your LAN) latency is usually very low (a millisecond or two per command). If the SQL Server is remote on a WAN (or cloud server) latency can be higher. In the case of our client who got us looking at this with a SQL Server in Auckland vs a SQL Server in Wellington the latency was anywhere from 10-15 milliseconds, still quite fast BUT then we get to how our SQL Transactions were constructed.

HOW IT WORKED:

To import a simple HL7 message of say 20 HL7 segments into a CORE SQL Schema would likely require 45-50 different INSERT statements (double that if the message was a 'reloaded' message). The CORE SQL Engine would operate as follows:

BEGIN TRANSACTION - 1 Server round trip

Create a SQL Command for each insert statement and execute it. 1 Server round trip for each.

END TRANSACTION (COMMIT) - final Server round trip instructing SQL Server to commit the transaction.

RESULT: In environments with low latency this would be very fast. There would be the amount of time that it takes MS SQL Server to actually execute each insert statement PLUS an extra 50-100 milliseconds allowed for network latency. With HIGH latency (like our Wellington server) the SQL Server execution time was very low (a few hundredths of a second) but the latency factored in would add an addition 400-600 milliseconds (that's half a second).

HOW IT WORKS NOW (Version 2.7.1):

Using the same simple HL7 message as an example, we have 45-50 different INSERT statements we need to process to get the message into the database. What we are doing NOW is concatenating all of these INSERT commands into ONE (or more than one for VERY large messages) SQL command.

BEGIN TRANSACTION - 1 Server round trip

Execute our now SINGLE command. 1 Server round trip.

END TRANSACTION (COMMIT) - final Server round trip instructing SQL Server to commit the transaction.

FINAL RESULT: In our example case, total time to import a simple HL7 message on a server with high latency reduced from a half second or more down to 100-150 milliseconds.

We have introduced the ability to exert some control over the Text Encoding of your HL7 messages before they are brought into your database. HL7 messages were designed to be ASCII only, meaning that a messages was intended to only contain straight ASCII characters (IE only the first 128 characters of any ASCII table). However, you may find that you need to accept HL7 messages which include some of the extended ASCII characters like (•, ™, or €). While not "illegal" under HL7 standard rules this is discouraged since HL7 provides creators with a mechanism for including just about any character by escaping it properly.

Importing HL7 messages that contain special characters into your database schema can be tricky because it can depend on your Database Collation and the ENCODING of the value being inserted. We have the solution to this now. See the Part 2 Instructions in Creating Profiles for how this works.

Creating a Schema Profile: Part 2

Starting with Version 2.7 we have added an additional column to the primary table in your CORE SQL Schema(s). This is the MachineName column which will be a Varchar(50). When present and a message is imported it will populate this column with the computer name of the box running the CORE SQL Engine software. If the computer name is > 50 characters it will be truncated. The column is created anytime you Create A New Schema.

If you are just upgrading your software and don't want to recreate your schema you can manually add the column to your <prefix>_HL7Data Column like so - Assuming that your Schema Prefix is ABC you can run this SQL statement:

Alter Table ABC_HL7Data Add MachineName Varchar(50) Null;

When you restart your process(es) you will see the results in SQL Server like so:

SQL Management Studio

What is the point of adding this column? We have had requests from several customers who are running multiple instances of CORE SQL Engine software and using a load balancer to direct traffic. They brought up to us that would be great if they could tell which machine in their cluster actually imported the message to help with troubleshooting, etc. We certainly thought that the idea had merit and so put it into this release!

This new column is NOT required for processing. The software just detects if it is present and then populates it. If you don't want this column in your table you can always remove it like so - still assuming that your Schema Prefix is ABC:

ALTER TABLE ABC_HL7Data DROP COLUMN MachineName;