Performance of workflow activities vs JS

[Dragos_Dunarean]

Hello,

I have a question regarding of the performance inside an workflow.

Let's take for example that I want to do this:

1. Take all the records inside an schema

2. Using 2-3 attributes (some might even be from linked schemas) to do some calculation which later would be saved

3. Compare the calculated values with the old ones (from step 1)

4. If the values are different, update the entries else do nothing

The number of records being one that's very high...

I want to know which way is faster performance wise, to use an query activity, enrichment and then update the data, or do everything inside an JS activity using queryDef and xtkSession.WriteCollection() ...

From what I dug into the files of ACC, I found that WriteCollection and queryDef basically does an SOAP request which is later interpreted by nlserver and the same thing applies to creating new entries though the ACC form interfaces, but I don't know about the actual activities inside an workflow.

Can somebody help me by clarifying which is faster? An workflow activity or JS function?

[ramon_bisswanger]

DB activity should normally always be the faster way.

WriteCollection may trigger a lot of small SQL calls to the database which is slow.

Also for JS, you woukd have tondo all in loops / chunks if you have a lot of data as the query activity might not fetch all data in 1 go.

[Dragos_Dunarean]

What other small SQL would there be for WriteCollection? compared to using activities which uses temporary tables, i think the activities would have more of them because it has to create, insert for the result of an activity and then query that table for the next activity to have data.

From how I see it, working only in JS has the advantage of having to get the data only once and then use the memory to deal with data, while using activities there might be a possibility of doing all the calculations in native code in nlserver, but I saw absolutely no hint of it or anything specified in documentation.

And yes, doing a queryDef in JS, it has a limit of 10,000 rows, but the limitation can be easily avoided with a do {} while ()

[ramon_bisswanger]

You may do a DB trace on WriteCollection.

If I remember correctly, this is not doing one insert into a temp table but rather looping over each entry of the collection. For each, it would chdck for changes and potentially do another 1 record UPDATE/INSERT statement.

[jonasn92134656]

Also consider the memory usage limitations of JS, which by default are set pretty low.

Depending on what you want to do, the "fastest" would likely be to define your own sql statements and execute on the db, either through SQL activities or triggered by JS. When enabling SQL logging and doing your stuff, it's common to encounter instances where AC doesn't do stuff optimally.

Regards,

Jonas

[Jonathon_wodnicki]

Hi,

Use sql to do everything on the db if performance is a concern..

If local execution is required, e.g. api calls, use File loading activity with temp table rename to bulk load computed records.

Thanks,

-Jon