Process BI Analytics Job Timeout - V15

[SteveSwaringen]

Please go through all the tasks below

• Check this box only after you have successfully completed both the above tasks

Please provide a brief description of the problem. Please do not forget to attach the relevant screenshots from your side.

The Process BI Analytics Job consistently fails after 30 seconds (regardless of the setting of Command Timeout) after updating to V15 (V15.1).

Expected Behavior

Job should run to completion.

Actual Behavior

Job consistently fails with a timeout error.

Steps to Reproduce

Run the Process BI Analytics job on a large database with Process Person BI Analytics enabled.

Rock Version

15.1

Client Culture Setting

en-US

[SteveSwaringen]

I believe this issue was introduced by this commit 6cbc5b2, though the commit itself is not at fault. In an effort to break this complex task into sections for more efficient execution, it added the need for a preliminary step of counting the number of rows that might need to be updated. This counting operation uses a different method on DbService (ExecuteScalar(), vs. ExecuteCommand()) and that method doesn't take a timeout parameter to allow more than the default 30 seconds for the command to complete. (It currently takes almost two minutes in our database.)

I think I have a fix for this that I'm planning to test tonight. If that goes well, I'll submit a pull request tomorrow. The change will involve changing the signature of ExecuteScalar() to follow the design pattern of ExecuteCommand() by adding an optional commandTimeout parameter and then modifying the ProcessBIAnalytics.cs job to pass _commandTimeout to the ExecuteScalar() call.

[JimMichael]

I’ll add that I saw the same after upgrading from v14.3 through 15.0/15.1 and finally to 16.0 alpha.

What was odd is that the BI job failed almost immediately for exactly 7 days and then magically started working again without any changes, and has run successfully (2 weeks now) ever since.

Note this is on a test instance which is a copy of production.