Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

Performance of workflow activities vs JS

Avatar

Avatar
Boost 1
Level 1
Dragos_Dunarean
Level 1

Like

1 like

Total Posts

2 posts

Correct Reply

0 solutions
Top badges earned
Boost 1
View profile

Avatar
Boost 1
Level 1
Dragos_Dunarean
Level 1

Like

1 like

Total Posts

2 posts

Correct Reply

0 solutions
Top badges earned
Boost 1
View profile
Dragos_Dunarean
Level 1

04-04-2019

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?

Replies

Avatar

Avatar
Give Back 5
Employee
ramon_bisswange
Employee

Likes

243 likes

Total Posts

353 posts

Correct Reply

182 solutions
Top badges earned
Give Back 5
Give Back 3
Give Back 25
Give Back 10
Give Back
View profile

Avatar
Give Back 5
Employee
ramon_bisswange
Employee

Likes

243 likes

Total Posts

353 posts

Correct Reply

182 solutions
Top badges earned
Give Back 5
Give Back 3
Give Back 25
Give Back 10
Give Back
View profile
ramon_bisswange
Employee

04-04-2019

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.

Avatar

Avatar
Boost 1
Level 1
Dragos_Dunarean
Level 1

Like

1 like

Total Posts

2 posts

Correct Reply

0 solutions
Top badges earned
Boost 1
View profile

Avatar
Boost 1
Level 1
Dragos_Dunarean
Level 1

Like

1 like

Total Posts

2 posts

Correct Reply

0 solutions
Top badges earned
Boost 1
View profile
Dragos_Dunarean
Level 1

04-04-2019

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 ()

Avatar

Avatar
Give Back 5
Employee
ramon_bisswange
Employee

Likes

243 likes

Total Posts

353 posts

Correct Reply

182 solutions
Top badges earned
Give Back 5
Give Back 3
Give Back 25
Give Back 10
Give Back
View profile

Avatar
Give Back 5
Employee
ramon_bisswange
Employee

Likes

243 likes

Total Posts

353 posts

Correct Reply

182 solutions
Top badges earned
Give Back 5
Give Back 3
Give Back 25
Give Back 10
Give Back
View profile
ramon_bisswange
Employee

04-04-2019

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.

Avatar

Avatar
Validate 1
Level 3
jonasn92134656
Level 3

Likes

28 likes

Total Posts

38 posts

Correct Reply

8 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile

Avatar
Validate 1
Level 3
jonasn92134656
Level 3

Likes

28 likes

Total Posts

38 posts

Correct Reply

8 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile
jonasn92134656
Level 3

05-04-2019

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

Avatar

Avatar
Establish
MVP
wodnicki
MVP

Likes

960 likes

Total Posts

1,090 posts

Correct Reply

509 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile

Avatar
Establish
MVP
wodnicki
MVP

Likes

960 likes

Total Posts

1,090 posts

Correct Reply

509 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile
wodnicki
MVP

05-04-2019

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