Hi All,
First, the term explanation, so you know what I am talking about:

1. HL7 - file that contains patient information; tab delimited.
2. CloverLeaf - interface engine that translates HL7 files into different
formats.

For those of you who have some experience in single- and multi- threading
application processing, here is the situation:

1. Multiple HL7 files come into CloverLeaf from different sources/systems
2. HL7 files are translated into XML files
3. XML files are copied to a file server
4. XML files picked up by application/service and sent to SQL server for
processing <---- We care about this step

Now, potentially, two (or more) XML files can contain different information
for the same record, to be updated in the database.

For example, both files need to update Person's address whose PersonID="5".
The address in the first file is "Apple St. #1", and the address in the
second file is "Orange St. #2". Remember, both files are legitimate.

So, in single-threaded processing, the XML file that was detected first, is
processed first. The second file is processed ONLY after the first file
processing is finished. This eliminates the conflict/error of trying to
update the same record twice at the same time.

In multi-threaded processing, there is a huge possibility this error would
occur, no question about it.

Single-threaded/Sequential processing = slow, but safe.
Multi-threaded/Simultaneous processing = fast, risky.

Having all the XML file processing done on the SQL server, how would you
handle this and why?

Thanks in advance.

Goran Djuranovic

Hello Goran,

If you already have the data in XML, I'd expect it would pretty easy to break
each address change into individual changes. If so, you could probably format
them as Service Broker messages.  Since Service Broker allows for in-order,
one-at-a-time delivery over conversations and conversation groups, you could
batch the changes by patient ID as a conversation group ID (or patientID
+ UniqueIdentifier for maximum uniqueness).

This should give you serial processing per-patient, but -- potentially --
parallel processing of all the patients.

We talk a good amount about service broker in our classes, however, if you
need an immediate shortcut, give Roger Wolter's excellent book on broker
a read.

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

I second the recommendation to use Service Broker to schedule the
processing. As to conflicts, note that the concurrency control mechanisms
will serialize your changes.

Best regards
Michael

Show quoteHide quote

Query: Using dynamic XQuery expressions (urgent)
Getting Empty Tags Returned From The Server
Return Value from SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class.Execute
Confirm methodology or advise how to generate performance reports for eCommerce facility.
xml-dml insert element with an attribute containig sql:variable
Add perent element
dynamic XML using for XML explicit
trouble with annotated xsd mapping schemas
sending xml data from stored procedure
Query Notification