Skip to main content

I created a very simple K2 Studio workflow that uses a For Each to pull 1300 records from a SQL Server database table just to insert the columns from that table into an Oracle table on another server databae. It took 45 minutes to load that few records. Why is the For Each so slow to process? I submitted this as a ticket with K2 and there response was:

 

"This time frame is expected because when records are being loaded, first it connects to the SQL server, takes the value, and then has to connect to the Oracle server until it can finally completes the transfer. Additionally, each time a record is transferred the connection closes and must reopen again for the next record. Thus, for all your records the servers have to undergo this process 1300 times, accounting for the 45 minute wait you are seeing. If you would like to read more about this, please check out the following documentation: https://docs.microsoft.com/en-us/sql/ssma/oracle/connecting-to-oracle-database-oracletosql?view=sql-server-2017 "

 

I use other ETL integration tools like SnapLogic for this exact same thing and it pulls data out of a SQL server DB and loads it into an Oracle database in a matter of seconds. I am not a database expert but this raises an eyebrow for me. Its got to be the way the For Each is architected or whatever. I am trying to make a case for the continued use of K2 for certain projects here but I cant make an argument for it if it performs this poorly. Is there another alternative method to do this without using the For Each? How does K2 5 now handle the use of For Each (because my understanding is K2 Studio is going away, correct?)? Does it still perform this poorly? I am curious the thoughts and advice of the community and how they handle this type of work action using K2.

 

Thanks,

Steve

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.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings