Question

Which Workflow Data Activity do you think will run faster?

Workflow Version 12 Self-Hosted

Updated March 23

asked on March 5 • Show version history

Here is my next test in this series of testing things in Workflow (in case you missed the previous two you can find #1 here and #2 here). This test will be aimed at running different SQL queries using different activities. I will be using the same test data for each activity category (query, insert, update). This will just be testing the amount of time it takes to run the activity against a MS SQL database. In the case of the Insert and Update activities, they will iterate through X number of values. I will be using the ODBC Driver 18 for each test because that is the driver that we use for TLS 1.2 compatibility.

Query
1. Query Data - ODBC Driver 18
2. Custom Query - ODBC Driver 18

Insert
1. Insert Data - ODBC Driver 18
2. Custom Query - ODBC Driver 18

Update
1. Update Data - ODBC Driver 18
2. Custom Query - ODBC Driver 18

Which Workflow Data activity from each category do you think will run faster? Add a comment below with your guess. Feel free to add one guess per category (query, insert, update). I will post the results in a few days.

0 0

Replies

replied on March 5

Query Data

Insert Data

Update Data

0 0

replied on March 6

Same as your other experiment, I expect no difference because they end up sending the same query to the driver. Processing parameters in Custom Query shouldn't add any meaningful time.

If you are going to loop over data sets, might be worth measuring how much slower things would get when you run over more than 1000 rows.

Or race For Each + Insert Data against a For Each loop that builds a custom query with all the rows as a single INSERT statement and passes it to Custom Query.

0 0

View 3 previous replies

replied on March 6

You're jumping ahead Miruna, lol. That's the next test.

0 0

replied on March 6

When you get there, try For Each + Insert Data vs For Each+ Invoke Workflow with Insert Data too. The latter should be much faster overall than the former and get faster the more CPUs the Workflow Server has.

1 0

replied on March 6

I just added it to my list. Thanks for the suggestion.

0 0

replied on March 17

Hi Miruna, related to your last comment, would you expect faster processing writing to a CSV file using the "For Each + Invoke" method in regard to this test I did as well, or would it not apply to that situation?

0 0

replied on March 18

I'll give my 2 cents here knowing that Miruna may overrule me: no, it won't apply to that other situation where you are generating a single CSV file.

Her comment is in reference to doing parallel INSERT statements. If you use the invoke WF activity, the loops will likely run faster than the invoked inserts, meaning you'll have a number of the invoked WFs that do the insert running in parallel (by default, up to 4 instances per CPU core). So you can go faster by getting those inserts running in parallel rather than sequentially.

Regarding that CSV generation WF, I don't think you'd want to do that. MS SQL is built to handle numerous, simultaneous queries. CSV and Excel files can only do one update at a time, so any parallel writes would actually fail because the document is locked. I know this is the case for Excel; I'm not positive about CSV but am pretty sure it would be the same.

1 0

replied on March 23

What Pieter said. If you parallelize writing to the same CSV, you'll likely run into "file in use" errors.

1 0

You are not allowed to follow up in this post.

Question

Question

Which Workflow Data Activity do you think will run faster?

Replies

Sign in to reply to this post.