Question

Which Data Source in Workflow Query Activities do you think will run faster?

Workflow Version 12 Self-Hosted

Updated March 5

asked on February 27 • Show version history

To continue the fun with testing things in Workflow (in case you missed the last one you can find it here), this test will be aimed at the different options when configuring a data source. I will be using the same test data to test all the options below. This will just be testing the amount of time it takes to query the data from a MS SQL database. I will run the same query 5 times per option and take the average.

1a. Direct Connection - ODBC

1b. Direct Connection - Direct

2a. Windows ODBC - ODBC Driver 18 for SQL Server

2b. Windows ODBC - SQL Server

3. Expert (Connection strings)

Which Data Source in Workflow do you think will run faster? Add a comment below with your guess. I will post the results in a few days.

0 0

Answer

SELECTED ANSWER

replied on March 5

Here are the results! As Miruna predicted, there was not any meaningful difference between times. The test was done by querying 500,000 records in a single database table using a Custom Query activity.

1a. Direct - ODBC: 1m 2s
1b. Direct - Direct: 1m 1s
2a. Windows ODBC - ODBC Driver 18 - 1m 2s
2b. Windows ODBC - SQL Server - 1m 6s
3. Expert (Connection strings) - 1m 5s

4 0

replied on March 5

This is good to know. I am sure I have obsessed over what would have been faster. Pretty cool has they are so similar!

0 0

replied on March 5

I think I have done the same at some point.

0 0

Replies

replied on February 27

I am going to go with option 1b (Direct Connection - Direct).

Thoughts (with no research!);

I would assume that any option calling an ODBC driver is going to add overhead.
I am also going to assume that option 3 (Connection Strings) would end up parsing back to one of the other connection types.

3 0

replied on February 28

I like that option also. I think that we wouldn't see a major difference unless we were retrieving a large recordset.

I also think the datacenter region might help (or hurt).

0 0

replied on March 2

There should be no meaningful difference, they all end up calling into the driver with a connection string. ODBC Driver for SQL v18 might be marginally more performant because it's newer.

Are you profiling SQL while you're running tests to rule out differences in the execution of the SQL query?

Windows ODBC is the preferred option because it makes the configuration transparent to Workflow and makes it easier to modify without having to change workflows.

2 0

replied on March 2

Hi Miruna. I wouldn't expect too much of a difference either, just having fun to see what the results are.

I won't be profiling SQL for these tests, just keeping it simple.

0 0

replied on March 4

Last chance to get your guesses in.

0 0

replied on March 4

1b is my guess.

0 0

replied on March 4

I agree with the others, 1b.

0 0

replied on March 4

I'll go with the consensus pick of 1b.

0 0

You are not allowed to follow up in this post.

Question

Question

Which Data Source in Workflow Query Activities do you think will run faster?

Answer

Replies

Sign in to reply to this post.