topic Re: Batch job run long time on VIYA3.5 in Fraud, AML and Security Intelligence

Batch job run long time on VIYA3.5

cthnpe — Thu, 30 May 2024 07:22:43 GMT

We encountered an issue where a job inserting data into the SQL Server stops and does not continue. This issue occurs every 3-4 days. When we rerun the job, it can insert the data and continue working normally.

For example, running a JOB normally takes 20 minutes to insert data. However, we found that running a JOB takes a long time and is not a complete job. No queries are running when we check the database (MS SQL Server).

Noted: We are using passthrough to query the data from the database.

Re: Batch job run long time on VIYA3.5

Kurt_Bremser — Thu, 30 May 2024 08:34:30 GMT

What does the log say? What are the differences between successful and failed runs?

Re: Batch job run long time on VIYA3.5

SASKiwi — Thu, 30 May 2024 20:18:48 GMT

Is it always the SQL Server insert data step that is slow? If so then it would be worth tuning the SAS options DBCOMMIT and INSERTBUFF. If your job doesn't use these options then default values will be used and in my experience these are not optimal. I suggest you post your code so we can see what is happening.

Re: Batch job run long time on VIYA3.5

cthnpe — Mon, 10 Jun 2024 08:21:51 GMT

The attached log file shows that the file 20240528_064309_GROUP_09_02_AGP_PREP01 did not run successfully, as the last line does not contain an error message. On the other hand, the file 20240606_064001_GROUP_09_02_AGP_PREP01 ran to completion. From the log of the unsuccessful run, we checked with the database, and it was found that no processes occurred on that side.

Re: Batch job run long time on VIYA3.5

cthnpe — Mon, 10 Jun 2024 08:24:48 GMT

From the log of the unsuccessful run, we checked with the database and found that no processes occurred on that side. I attached my code in attachment.

Re: Batch job run long time on VIYA3.5

cthnpe — Fri, 19 Jul 2024 04:04:25 GMT

I shared the code as attached

Re: Batch job run long time on VIYA3.5

Kurt_Bremser — Fri, 19 Jul 2024 06:59:27 GMT

In the successful run, the SELECT DISTINCT .... ran for less than half a minute, in the unsuccessful it took almost 30 minutes. I would start looking there.