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