SQL and Retrieving Interface Messages

[Desertolson]

Our hospital has many different HL7 2.x interfaces. I would like to be able to use SQL to retrieve the messages for a given Business Service, Business Process, or Business Operation and put them to a file.

Is this possible? How do you find the tables to use?

Randy

[Marc Lang]

I did something similar recently.

They are all in EnsLib.HL7.Message

You can extract them to file doing something like this (sorry, I've copy and pasted this and brackets might not be right, but it's ballpark)

Set tSC=$$$OK
   New SQLCODE,%msg
  &sql(DECLARE C1 CURSOR FOR 
   SELECT ID INTO :tID FROM EnsLib_HL7.Message WHERE Name = 'ORU_R01' and TimeCreated > '20130306' and ID > 16904087)

  &sql(OPEN C1)  If 'SQLCODE {
   For { &sql(FETCH C1)  Quit:SQLCODE
    Set hl7=##class(EnsLib.HL7.Message).%OpenId(tID,,.tSC) Quit:$$$ISERR(tSC)
    //Write !,"ID "_tID_":",!
    Set tSC= hl7.OutputToFile("S:/hl7.txt",0,hl7.Separators_$C(13,10)) Quit:$$$ISERR(tSC)
    
 
   } Set tCode=SQLCODE &sql(CLOSE C1) Set:'SQLCODE SQLCODE=tCode
  }
  Set:SQLCODE'=100&&SQLCODE tSC=$$$ADDSC(tSC,$$$ERROR($$$EnsErrGeneral,"SQL Error fetching messages:"_SQLCODE_"/"_$G(%msg)))
  
  

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.
To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Ted Peck]

Here's a similar method to what I suggested for a similar question Marc Lang asked recently. You should be able to change the WHERE clause to select whatever criteria you need, and change the inner OutputToDevice() call to OutputToFile() to put the resulting messages wherever you want them.

ClassMethod ExportSource() As %Status
{

  Set tSC=$$$OK
  New SQLCODE,%msg
  &sql(DECLARE C1 CURSOR FOR 

   SELECT MessageBodyId INTO :tID FROM Ens.MessageHeader WHERE MessageBodyClassName='EnsLib.HL7.Message' AND SourceConfigName = 'myService')

  &sql(OPEN C1)  If 'SQLCODE {
   For { &sql(FETCH C1)  Quit:SQLCODE

    Set tMsg=##class(EnsLib.HL7.Message).%OpenId(tID,,.tSC) Quit:$$$ISERR(tSC)
    Write !,"ID "_tID_":",!
    Set tSC= tMsg.OutputToDevice(,tMsg.Separators_$C(13,10)) Quit:$$$ISERR(tSC)

   } Set tCode=SQLCODE &sql(CLOSE C1) Set:'SQLCODE SQLCODE=tCode
  }
  Set:SQLCODE'=100&&SQLCODE tSC=$$$ADDSC(tSC,$$$ERROR($$$EnsErrGeneral,"SQL Error fetching messages:"_SQLCODE_"/"_$G(%msg)))

  Quit tSC

[Dale du Preez]

For the sake of completeness, I also want to add my 2c here about using the %SQL.Statement class to keep the error checking a little bit simpler.

Ted's code below could also be written as follows:

ClassMethod ExportSource() As %Status
{
  Set tSC=$$$OK

  Set tStatement = ##class(%SQL.Statement).%New()
  Set tSC = tStatement.%Prepare("SELECT MessageBodyID As ID FROM Ens.MessageHeader WHERE MessageBodyClassName = 'EnsLib.HL7.Message' AND SourceConfigName = ?")
  If $$$ISERR(tSC) Quit tSC
  Set tRS = tStatement.%Execute("myService")
  While tRS.%Next() {
    Set tID = tRS.%Get("ID")
    Set tMsg = ##class(EnsLib.HL7.Message).%OpenId(tID,,.tSC)
    If $$$ISERR(tSC) Quit

    Write !,"ID "_tID_":",!
    Set tSC = tMsg.OutputToDevice(,tMsg.Separators_$C(13,10))

    If $$$ISERR(tSC) Quit
   }
  If $$$ISERR(tSC) Quit tSC
  }
  Quit tSC

[LKO]

Given that a typical healthcare organization is going to process many thousands of messages per day, and given that messages will likely be retained for a period of time (e.g., 30 days), how long does it take to execute a method like this? And is there a noticeable impact to regular interface performance?

To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.

[David Loveluck]

I think a typical number would be millions of messages per day and you are very right to be concerned.

 

Dale’s example is very efficient and the time taken to select the records is far less than the time to export them. But simple changes in the SQL WHERE clause can change the query plan in unexpected ways and make a query expensive, so you have to try it out on a large test system before

 

Dave

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.

[LKO]

Any idea whether specific fields are indexed, which could speed up the query?
For example, if we wanted to locate the messages for a specific time period, and the timestamp of the message is indexed, I would think that would be a rather speedy, inexpensive query.

To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.

To post to this group, send email to Ensemble-i...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.

[David Loveluck]

You can see which fields of any class (including your own) are indexed from the Class Reference Documentation, also known as Documatic.

 

For those new to Ensemble, Documatic is the dynamic documentation of every class in the system. You can get there from Studio  by clicking Viewà Show Class Documentation.

 

Or from the Documentation page click on Class Reference in the top strip of the home page.

 

It shows you everything about a class - properties, methods, parameters, superclasses, etc. It is the probably the single most important reference place for anyone programming in Ensemble (or Cache or HealthShare)

 

Dave

 

From: Ensemble-in...@googlegroups.com [mailto:Ensemble-in...@googlegroups.com] On Behalf Of LKO
Sent: Wednesday, April 24, 2013 9:28 AM
To: Ensemble-in...@googlegroups.com
Subject: Re: [InterSystems-EnsHlth] SQL and Retrieving Interface Messages

 

Any idea whether specific fields are indexed, which could speed up the query?

For example, if we wanted to locate the messages for a specific time period, and the timestamp of the message is indexed, I would think that would be a rather speedy, inexpensive query.

On Tuesday, April 23, 2013 2:47:33 PM UTC-5, David Loveluck wrote:

I think a typical number would be millions of messages per day and you are very right to be concerned.

 

Dale’s example is very efficient and the time taken to select the records is far less than the time to export them. But simple changes in the SQL WHERE clause can change the query plan in unexpected ways and make a query expensive, so you have to try it out on a large test system before

 

Dave

 

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-i...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.

[LKO]

Thanks for the tip. I found the info.

Does an SQL query automatically use the index if a field specified in the WHERE clause is one of those that is indexed? Or is specific syntax required to have the index be used?

To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-i...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-i...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.

[David Loveluck]

The query optimizer selects whatever index or indices ‘thinks’ will give the best performance. In the SQL query page of the management portal, you can enter a query and ask it to show you the query plan to see what indices it will use.

 

We don’t want to add too many indices, but the ones we have today seem to satisfy most queries fairly well, although this is a continual process.

 

The SQL generated behind the message browser has been highly tuned to avoid unnecessarily long running queries. If you add custom search tables, it is fairly easy to satisfy most requirements without exporting to  flat files. What is your use case that isn’t satisfied by the built in search capabilities?

 

dave

 

From: Ensemble-in...@googlegroups.com [mailto:Ensemble-in...@googlegroups.com] On Behalf Of LKO
Sent: Wednesday, April 24, 2013 2:44 PM
To: Ensemble-in...@googlegroups.com
Subject: Re: [InterSystems-EnsHlth] SQL and Retrieving Interface Messages

 

Thanks for the tip. I found the info.

Does an SQL query automatically use the index if a field specified in the WHERE clause is one of those that is indexed? Or is specific syntax required to have the index be used?

On Wednesday, April 24, 2013 8:47:13 AM UTC-5, David Loveluck wrote:

You can see which fields of any class (including your own) are indexed from the Class Reference Documentation, also known as Documatic.

 

For those new to Ensemble, Documatic is the dynamic documentation of every class in the system. You can get there from Studio  by clicking Viewà Show Class Documentation.

 

Or from the Documentation page click on Class Reference in the top strip of the home page.

 

It shows you everything about a class - properties, methods, parameters, superclasses, etc. It is the probably the single most important reference place for anyone programming in Ensemble (or Cache or HealthShare)

 

Dave

 

From: Ensemble-in...@googlegroups.com [mailto:Ensemble-in...@googlegroups.com] On Behalf Of LKO
Sent: Wednesday, April 24, 2013 9:28 AM
To: Ensemble-in...@googlegroups.com
Subject: Re: [InterSystems-EnsHlth] SQL and Retrieving Interface Messages

 

Any idea whether specific fields are indexed, which could speed up the query?
For example, if we wanted to locate the messages for a specific time period, and the timestamp of the message is indexed, I would think that would be a rather speedy, inexpensive query.

On Tuesday, April 23, 2013 2:47:33 PM UTC-5, David Loveluck wrote:

I think a typical number would be millions of messages per day and you are very right to be concerned.

 

Dale’s example is very efficient and the time taken to select the records is far less than the time to export them. But simple changes in the SQL WHERE clause can change the query plan in unexpected ways and make a query expensive, so you have to try it out on a large test system before

 

Dave

 

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-i...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-i...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.

[LKO]

We want to create a directory structure (at the OS level) of messages processed. We did that with our previous interface engine, and it was very helpful in troubleshooting, analysis, creating test files, and so on. Let's say we want a file of all registration messages for a specific location. If I've got files of messages in the file system, I can just do a quick grep redirected to a file name and I'm good to go. Doing the same thing with the Message Viewer and Management Portal would be much more involved and take more time. We came to rely on those files quite heavily with the old engine, and became very proficient at a variety of activities that you can perform at the OS level. We're trying to replicate the same concept with Ensemble.

To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-i...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-i...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-i...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en
---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healthcare+unsub...@googlegroups.com.

[Jeffrey Solomon]

I have hacked the subject line of this thread to ask about a somewhat related topic.

It seems that the underlying SQL statements that Ensemble builds to perform message searches when using the Message Viewer are just not right.  An example:

Ensemble 2012.1.2, AIX, 28 days of messages in this production, This works out to about 44,000,000 message headers.

Doing an SQL 'select count' from one of our inbound ADT business services yields about 2,350,000 message headers.

Going to the Ensemble Message Viewer and doing a simple query with the defaults, except changing the 'extended' criteria to only include the business service provides a fast (< 1 second) return.

Adding an extended criteria to find a particular MRN using the HL7 search table index also yields a result in under a second.

However:

• Then adding a start time date and stop time date of a more than a few hours causes the search to invariably time out.

It would sure seem that the query is not first creating an intermediate table with the records containing the correct MRN, and then applying the date range search.  If that is the case, isn't that wrong-headed?  There are times when we would like to use date ranges with indexed criteria to cut down on the records returned, but we frequently can't.

(Not having an indexed item to use in a search is a mine field that can be left alone. . . )

Any comments or suggestions are appreciated.


Jeff Solomon
Supervisor, Technical Team
Systems Integration
Information Technology
UC Davis Health System
Sacramento, CA
Phone: (916) 734-0735
jeffrey...@ucdmc.ucdavis.edu

 

 



From:        LKO <willy...@hotmail.com>
To:        Ensemble-in...@googlegroups.com,
Date:        04/24/2013 12:46 PM
Subject:        Re: [InterSystems-EnsHlth] SQL and Retrieving Interface Messages
Sent by:        Ensemble-in...@googlegroups.com

---

We want to create a directory structure (at the OS level) of messages processed. We did that with our previous interface engine, and it was very helpful in troubleshooting, analysis, creating test files, and so on. Let's say we want a file of all registration messages for a specific location. If I've got files of messages in the file system, I can just do a quick grep redirected to a file name and I'm good to go. Doing the same thing with the Message Viewer and Management Portal would be much more involved and take more time. We came to rely on those files quite heavily with the old engine, and became very proficient at a variety of activities that you can perform at the OS level. We're trying to replicate the same concept with Ensemble.

On Wednesday, April 24, 2013 1:52:21 PM UTC-5, David Loveluck wrote:
The query optimizer selects whatever index or indices ‘thinks’ will give the best performance. In the SQL query page of the management portal, you can enter a query and ask it to show you the query plan to see what indices it will use.

We don’t want to add too many indices, but the ones we have today seem to satisfy most queries fairly well, although this is a continual process.

The SQL generated behind the message browser has been highly tuned to avoid unnecessarily long running queries. If you add custom search tables, it is fairly easy to satisfy most requirements without exporting to  flat files. What is your use case that isn’t satisfied by the built in search capabilities?

 

dave

 

From: Ensemble-in...@googlegroups.com [mailto:Ensemble-in...@googlegroups.com] On Behalf Of LKO
Sent: Wednesday, April 24, 2013 2:44 PM
To: Ensemble-in...@googlegroups.com
Subject: Re: [InterSystems-EnsHlth] SQL and Retrieving Interface Messages

Thanks for the tip. I found the info.

Does an SQL query automatically use the index if a field specified in the WHERE clause is one of those that is indexed? Or is specific syntax required to have the index be used?

On Wednesday, April 24, 2013 8:47:13 AM UTC-5, David Loveluck wrote:

You can see which fields of any class (including your own) are indexed from the Class Reference Documentation, also known as Documatic.

For those new to Ensemble, Documatic is the dynamic documentation of every class in the system. You can get there from Studio  by clicking Viewà Show Class Documentation.

Or from the Documentation page click on Class Reference in the top strip of the home page.

It shows you everything about a class - properties, methods, parameters, superclasses, etc. It is the probably the single most important reference place for anyone programming in Ensemble (or Cache or HealthShare)

 

Dave

 

From: Ensemble-in...@googlegroups.com [mailto:Ensemble-in...@googlegroups.com] On Behalf Of LKO
Sent: Wednesday, April 24, 2013 9:28 AM
To: Ensemble-in...@googlegroups.com
Subject: Re: [InterSystems-EnsHlth] SQL and Retrieving Interface Messages

Any idea whether specific fields are indexed, which could speed up the query?
For example, if we wanted to locate the messages for a specific time period, and the timestamp of the message is indexed, I would think that would be a rather speedy, inexpensive query.

On Tuesday, April 23, 2013 2:47:33 PM UTC-5, David Loveluck wrote:

I think a typical number would be millions of messages per day and you are very right to be concerned.

Dale’s example is very efficient and the time taken to select the records is far less than the time to export them. But simple changes in the SQL WHERE clause can change the query plan in unexpected ways and make a query expensive, so you have to try it out on a large test system before

 

Dave

 

 

Randy

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at

http://groups.google.com/group/Ensemble-in-Healthcare?hl=en

---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at

http://groups.google.com/group/Ensemble-in-Healthcare?hl=en

---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-i...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en

---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-i...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at

http://groups.google.com/group/Ensemble-in-Healthcare?hl=en

---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-i...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en

---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com

To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at

http://groups.google.com/group/Ensemble-in-Healthcare?hl=en

---
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare" group.

To unsubscribe from this group and stop receiving emails from it, send an email to Ensemble-in-Healt...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Lawrence Harris]

You could check the schemas and do a tune tables. Not sure if Ensemble does this automatically or not. Generally cache does not. 

Lawrence

[Ted Peck]

That's very interesting.  Normally we go to some lengths to try to ensure that time or ID constraints are applied first because they reliably reduce the search space. However in your example the total result set is small even without a time or ID constraint, just not as small as you'd like. Applying a time constraint after the original search would be fast, whereas apparently applying the original constraint to a temp table representing the time range is not fast.

We wrestle constantly with query optimization as you know.  Given the free-form nature of the search criteria offered by the Message Browser page, it's impossible to guarantee that every set of criteria can be satisfied in reasonable time, or that the SQL optimizer will make the best choice in every case.

Maybe it's time to redesign the user interface, or design a new "advanced" user interface that lets knowledgeable customers like yourself specify the order in which constraints should be applied instead of relying on the SQL optimizer code to use its best guess.

Or maybe the SQL group will come up with some enhancements that make it more likely queries will complete in acceptable time. For example one idea I heard tossed around was for the SQL engine to try multiple query plans in parallel and return the results of whichever one completes first.

Your further thoughts are welcome.
Thanks,
Ted

[Dale du Preez]

To expand on Ted's comment, we have actually made some recent changes to handle the specific case of combining time ranges with other criteria to try and avoid problems where criteria are applied in an order that builds a large, time-consuming, temp table. It may be worth contacting the WRC to work out whether the changes we've made might help in your case.

Thanks,
Dale