Querying EnsLib.HL7.Message via ADO/ODBC

Primary tabs

While I can query the HL7 message class EnsLib.HL7.Message (EnsLib_HL7.Message for SQL) to my heart's content in the SQL Shell or the Management Portal's SQL page, I can't seem to SELECT anything other than ID/%Id from an ADO/ODBC client. Properties such as TimeCreated, Name, MessageTypeCategory, etc. all seem to prevent the query from ever completing EXCEPT when I provide the ID as part of the WHERE criteria.

This works fine in the Management Portal and Shell:

SELECT head.ID As HeadId, body.ID As BodyId, body.Name as BodyName
FROM Ens.MessageHeader head
INNER JOIN EnsLib_HL7.Message body
ON head.MessageBodyId = body.ID
WHERE Body.Name = 'ADT_A04'

It fails, however, when I attempt the same query over an ADO or ODBC connection.

Interestingly, this succeeds:

SELECT head.ID As HeadId,body.ID As BodyId, body.Name as BodyName
FROM Ens.MessageHeader head
INNER JOIN EnsLib_HL7.Message body
ON head.MessageBodyId = body.ID
WHERE body.ID = 5745

Is there some special juju that I need to incorporate into my client queries to get the same behavior as the Management Portal and Shell?

 

  • 0
  • 0
  • 71
  • 3
  • 1

Answers

Jeffrey,

The scenario that I can hypothesize that would produce these results are around the client timing out before the query finishes executing on the server. 

Query 1 fails because you have to do a join on the two large tables and your only where clause is against a property that is not indexed.

Query 2 succeeds because object IDs are "indices by nature".

Query 3(from your comment) succeeds because you are filtering out hundreds, thousands, maybe millions of Ens.MessageHeader objects in your join by using a where clause against and indexed MessageBodyClassName property.

 

I would recommend reaching out to InterSystems WRC to review the query cost and overall performance to confirm or dive deeper into the failures.

Thanks for the response, fellow Jeffrey!

The tables I'm currently working with are tiny, literally no more than a couple dozen message header and body records. The query in the IRIS SQL shell (and management portal SQL facility) returns results immediately.

I think we have some sort of obscure bug here. This is with IRIS for Health CE 2019.1 running on Ubuntu (not a docker image), and the client is Windows 10 using IRIS ADO.NET support. Same result when using the ODBC drivers, though.

I think you're right on reaching out to WRC ... thanks again.

Hypothesis debunked.  smiley

I'm sure there are logs somewhere in Windows or even the driver that can help identify what is at the root of the failure cases.  Our WRC can definitely help you with that.

Comments

This works in a client also ...

SELECT head.ID As HeadId, body.ID As BodyId, body.Name as BodyName
FROM Ens.MessageHeader head
INNER JOIN EnsLib_HL7.Message body
ON head.MessageBodyId = body.%ID
WHERE head.ID > 0 AND head.MessageBodyClassName = 'EnsLib.HL7.Message' AND body.Name = 'ADT_A04'


There's something fundamental here I'm missing.