Hi Steve,

I am curious how you design the workflow. Did you pull each of the 1300 records within the loop, meaning, did you make a call to your database to retrieve 1 record in each iteration? Or did you already retrieve the 1300 records using a SmartObject list method before the start of the loop?

HI steven_rossi,

Convert  SQL result to XML format and then send it to Oracle this will help you to send your data with single call and loop will not be required.

Thank you both for responding. I appreciate it. I attached a document to visually show you what I did to build it. BoringNerd, I think I am doing what you suggested first which is within my For Each I am calling the SMO list method to the SQL Server table and than doing an insert. 

Karan. I really like your idea. Pardon my ignorance, but would I essentially be doing this work within my databases? Meaning, would I assemble the XML on the SQL Server side using a procedure, then call the XML through K2, to pass to Oracle and use Oracle XML parse to insert through another procedure? Or is this something I am able to do using K2 directly? If so can you point me in the right resource direction?

Although I am all for using whatever technical means for handling something like this, I am trying to figure out the best way to do it using the K2 environment without a lot of back-end coding. We are only a little over a year using K2, evaluating the product and determining its ease of use in terms of less coding and letting the tool do the heavy lifting. 

Thanks for the help.

Steve

Hi,

I did a quick test with For-each event looping through a table with 10K records and executing ‘create’ method to another table, I can see how this becomes a problem for K2 server. Event report in K2 Workspace (Process Overview) grow with each transaction (7 to 8 pages for each activity instance.) A quick look at the K2 database shows that the process instance is at running state the whole time. This can be an issue if you try to start multiple process instances of the same workflow. K2 might run out of threads and it causes other things to slow down. In the long run, the size of the K2 database will significantly increase and you might have to deal with it too. I have to agree with Kran on this, you might want push this call to SQL side. Perhaps, using stored procedure to handle this.

Thanks again for the feedback. Does anyone know where the K2 system is planning on going with their product in terms of an ETL tool similiar to like SnapLogic? We have been using Snaplogic to handle many of our integrations and it can process this in a matter of seconds, which is the way I will be going. Also, I have no problem doing this type of task through the back-end database as well through a dblink between oracle and sql server. However, I am curious to know what direction K2 is going if at all in terms of handling this type or process more effectively without a lot of heavy coding (back end, etc)? 

Thanks.

Steve

Hi steven_rossi,

This  logic will reduce K2 workload as loop will not be required to insert the data  to oracle DB. 

A SELECT query returns results as a rowset. But  optionally we can retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query.

Example 

SELECT pid]
      ,REmployeeCode]
      ,REmployeeFirstName]
      ,REmployeeLastName]
FROM   dbo.employeedetails
FOR XML PATH

• Create smartobject for this procedure
• In workflow under Sqlserver Activity  call it using smartobject event and assign its output to  datafield
• Now in in inserttoOracle Activity pass this datfield  as parametr to oracle insert query
• In oracle script insert  parse xml and add details to table

Karan, sorry for my delayed response, but this is a fantastic work-around. I appreciate it.