Issue with Execute Stored Procedure Smart Service

Seems like the SP call is not happening.Please try with fn!excecuteStoreProcedure and Execute the SP in DB to check its functionality. Can you please give some more details of SmartService configuration?

Can you performance tune the relevant procedure at all, or break up the functionality into multiple procedures? This error message is generally indicative of performance issues.

Sounds like you are running a report (joining and aggregating data or moving some information from one table to another, or you have lot of information. I really suggest to explore other implementation options

1) when talking of PL/SQL in oracle there are lot of tricks to improve the performance and speed.
www.dba-oracle.com/art_proc.htm

2) the same that Coltonb is suggesting, break up the code and the queries. then you can monitor each step

3) in case you are executing one query which takes long time to execute then you can think about making that execution asynchronous, for example you can trigger a process and receive a message or simply wait/poll for a flag in another table which tells you that the process have finished, and run the process directly in the DB with and scheduled job (DBMS_SCHEDULER.CREATE_JOB)

4) one more option is that you are getting lot of results(this conversion most of the times is time consuming) from that Store procedure why don't you bulk that to a table and then query the table for the results? this mostly ends up in the same that the third option.

I can say that, since you are in oracle and most of the processing is happening in the DB it is not that worth to make Appian wait for the response.

Please let us know your use case maybe someone else will have more ideas.

Jose

Hi Josep,

Thank for your response.

As far as stored procedure is concerned we are working on fine tuning the same, my main issue is that we are dealing with thousands of records which is getting parsed, insert and update in different tables using SP, now once I called the SP using "Execute Stored Procedure" smart service as per the behaviour of procedure it will not return the output till execution is completed and as per behaviour of smart service it will wait for the out parameter from procedure which is taking more time.

Solution what we have opted for:
- Calling the stored procedure in batches.

I have one question related to this smart service:
- What is the time out period for this node, I can see this is not getting failed even when procedure is taking approx 27 minutes but when I am executing the same again it is getting hanged and throwing error after I hour.

- Is there any way in Cloud Environment I can trace the working of this smart service, connection with DB etc?

Thanks coltonb,

We are looking into fine tuning the procedure

Hello Prakhar,

I am glad you decided to to divide your processing, then try to do it in parallel splitting the logic or something, appian can help you a lot with this, I insist with the asynch.

Regarding your questions , I haven't had to answer this question before but let me research on the same. but I can tell you the following.

You scenario is not easy to trace because after you call the smartservice it gives all the control to the Database so what you want to trace is the DB transaction. when I say trace the DB i think something like this:
asktom.oracle.com/.../f
Unless you implement some temporary flags you can monitor as much details as you want.

Regarding the timeout, well here there is a little more complex it always depends in multiple factors: for example
the JPA transaction timeout, the JDBC trnasaction timeout, the transaction timeout configured in the DB, and in some applications servers sometimes you have to be careful having the Engine timeout < JPA transaction timeout < than the DB .

Something I am sure is that the Plugin is logging to the Performancetracker.

Finally, I have seen scenarios like this, you have an open connection, the client is waiting for a response, the connection was intermittent or something happen then and the client keeps waiting, and when the server really finishes its not able to deliver the message.
I will try to find out what is the specific answer to your question. In case you get it before please share, this is something important to know.

Best Regards
Jose Perez