Timing of Database look up

Question

Question

Scott Roth · Apr 12, 2019

We use a lot of external MS SQL calls to look up numerous things when it comes to HL7. I am running into an issue where the outbound calls are queued but the message is sent onto the next process before it gets a response. How do I get it to wait till the response gets back from the MS SQL call before sending it onto the next process?

Below is a screen shot of what I am trying to explain...Select Research Study executes at 8:33 the message is sent to the next process at 8:34, but the response of the MSSQL call doesn't come till 9:31.

Discussion (8)2

Log in or sign up to continue

Eduard Lebedyuk · Apr 13, 2019

After you sent ASYNC call in BP add sync activity next. Process would wait for ASYNC call to complete before going further.

0 0

score 1 · Answer 1 · 2019-04-12T19:45:18-04:00

Hi

You have to use the SendRequestSync() method for sending the request to your business operation from your business process.

Using the SendRequestSync() the business process will not process the next message until a response is received. Your messages will be queuing in the business process.

Additionally, I would suggest to check why the database response takes so long.

Regards

Stelios

score 0 · Answer 2 · 2019-04-16T11:21:05-04:00

so how would the code block look like for the following... How do I return the EnsLib.SQL.Snapshot?

Thanks

Scott

score 0 · Answer 3 · 2019-04-16T10:01:09-04:00

Scott Roth Apr 16, 2019 to Eduard Lebedyuk

Can you explain further, I guess I am not following?

Thanks

Scott

0 0

score 0 · Answer 4 · 2019-04-12T22:56:29-04:00

Hi Scott,

I've accomplished this in the past by calling the SQL Operation with the SendRequestSync() method in a <code> action rather than using the <call> action. The default is to wait until the response is received.

This will be problematic if you must maintain FIFO (thus setting the Pool Size to 1), since the BPL will stop processing until the response is received. And increasing the pool size could have undesirable side effects other than loss of FIFO ... so you're likely to see horrible message latency at times given the metrics you're reporting.

EDIT: Stelios of course beat me to it ... I stepped away from the keyboard before hitting the Publish button and only realized it some hours later

score 0 · Answer 5 · 2019-04-16T11:55:22-04:00

Probably something like this (I'm not sure of the full name of the osuwmc.PatientBilling... class, and you probably want to do some sort of exception handling or at least error checking):

set callrequest = ##class(osuwmc.PatientBilling.DataStructures.SelectResearchID).%New()

set callresponse = ##class(EnsLib.SQL.Snapshot).%New()

set callrequest.ResearchID = context.RschMRN

do process.SendRequestSync("PatientBillingDBPoll", callrequest, .callresponse)

set context.ClarityRS = callresponse

The default behavior of SendRequestSync is to never timeout, but you can always include a -1 as the 4th parameter to be a bit more descriptive.

score 0 · Answer 6 · 2019-04-16T11:08:51-04:00

You have ASYNC calls to MS SQL operation in your Business process, right?

Add sync elements immediately after them.

score 0 · Answer 7 · 2019-04-16T11:19:15-04:00

It's explained pretty well in the documentation. It allows the synchronization of the call to be retried repeatedly when the timeout is reached. You specify the <call> to sync, the timeout, and whether resync (retry) is allowed. I'm not sure if there's any benefit to using it over the SendRequestSync() method, though.