You are viewing limited content. For full access, please sign in.

Question

Question

What would be running this SQL query within Forms?

asked on September 19, 2023

We have been trying to narrow down some SQL deadlocks recently where the below query is the culprit. Does anyone know what would be running this SQL query within Forms? My first guess is a user running a report, but I need to know for sure.

(@p__linq__0 int)SELECT 
    [Project2].[step_id] AS [step_id], 
    [Project2].[C1] AS [C1], 
    [Project2].[history_id] AS [history_id], 
    [Project2].[instance_id] AS [instance_id], 
    [Project2].[process_id] AS [process_id], 
    [Project2].[step_id1] AS [step_id1], 
    [Project2].[start_date] AS [start_date], 
    [Project2].[finish_date] AS [finish_date], 
    [Project2].[status] AS [status], 
    [Project2].[submission_id] AS [submission_id], 
    [Project2].[external_op] AS [external_op], 
    [Project2].[external_op_data1] AS [external_op_data1], 
    [Project2].[archived_worker_instnc_to_resume] AS [archived_worker_instnc_to_resume], 
    [Project2].[team_id] AS [team_id], 
    [Project2].[owner_snapshot_id] AS [owner_snapshot_id], 
    [Project2].[assigned_resume_id] AS [assigned_resume_id], 
    [Project2].[assigned_comment] AS [assigned_comment], 
    [Project2].[target_snapshot_id] AS [target_snapshot_id], 
    [Project2].[due_date] AS [due_date], 
    [Project2].[current_priority] AS [current_priority], 
    [Project2].[task_first_assigned_date] AS [task_first_assigned_date], 
    [Project2].[sent_to_prediction_server] AS [sent_to_prediction_server], 
    [Project2].[approver_id] AS [approver_id], 
    [Project2].[reminder_id] AS [reminder_id]
    FROM ( SELECT 
        [Distinct1].[step_id] AS [step_id], 
        [Join4].[history_id] AS [history_id], 
        [Join4].[instance_id1] AS [instance_id], 
        [Join4].[process_id1] AS [process_id], 
        [Join4].[step_id1] AS [step_id1], 
        [Join4].[start_date] AS [start_date], 
        [Join4].[finish_date] AS [finish_date], 
        [Join4].[status1] AS [status], 
        [Join4].[submission_id] AS [submission_id], 
        [Join4].[external_op] AS [external_op], 
        [Join4].[external_op_data1] AS [external_op_data1], 
        [Join4].[archived_worker_instnc_to_resume] AS [archived_worker_instnc_to_resume], 
        [Join4].[team_id] AS [team_id], 
        [Join4].[owner_snapshot_id] AS [owner_snapshot_id], 
        [Join4].[assigned_resume_id] AS [assigned_resume_id], 
        [Join4].[assigned_comment] AS [assigned_comment], 
        [Join4].[target_snapshot_id] AS [target_snapshot_id], 
        [Join4].[due_date] AS [due_date], 
        [Join4].[current_priority] AS [current_priority], 
        [Join4].[task_first_assigned_date] AS [task_first_assigned_date], 
        [Join4].[sent_to_prediction_server] AS [sent_to_prediction_server], 
        [Join4].[approver_id] AS [approver_id], 
        [Join4].[reminder_id] AS [reminder_id], 
        CASE WHEN ([Join4].[history_id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM   (SELECT DISTINCT 
            [Extent1].[step_id] AS [step_id]
            FROM   [dbo].[cf_bp_worker_instance_history] AS [Extent1]
            INNER JOIN [dbo].[cf_bp_worker_instances] AS [Extent2] ON [Extent1].[instance_id] = [Extent2].[instance_id]
            INNER JOIN [dbo].[cf_bp_steps] AS [Extent3] ON ([Extent1].[step_id] = [Extent3].[step_id]) AND ([Extent1].[process_id] = [Extent3].[process_id])
            WHERE ([Extent2].[bp_instance_id] = @p__linq__0) AND ('userTask' = [Extent3].[step_type]) AND (0 = [Extent3].[is_deleted]) ) AS [Distinct1]
        LEFT OUTER JOIN  (SELECT [Extent4].[history_id] AS [history_id], [Extent4].[instance_id] AS [instance_id1], [Extent4].[process_id] AS [process_id1], [Extent4].[step_id] AS [step_id1], [Extent4].[start_date] AS [start_date], [Extent4].[finish_date] AS [finish_date], [Extent4].[status] AS [status1], [Extent4].[submission_id] AS [submission_id], [Extent4].[external_op] AS [external_op], [Extent4].[external_op_data1] AS [external_op_data1], [Extent4].[archived_worker_instnc_to_resume] AS [archived_worker_instnc_to_resume], [Extent4].[team_id] AS [team_id], [Extent4].[owner_snapshot_id] AS [owner_snapshot_id], [Extent4].[assigned_resume_id] AS [assigned_resume_id], [Extent4].[assigned_comment] AS [assigned_comment], [Extent4].[target_snapshot_id] AS [target_snapshot_id], [Extent4].[due_date] AS [due_date], [Extent4].[current_priority] AS [current_priority], [Extent4].[task_first_assigned_date] AS [task_first_assigned_date], [Extent4].[sent_to_prediction_server] AS [sent_to_prediction_server], [Extent4].[approver_id] AS [approver_id], [Extent4].[reminder_id] AS [reminder_id], [Extent5].[bp_instance_id] AS [bp_instance_id], [Extent6].[step_type] AS [step_type], [Extent6].[is_deleted] AS [is_deleted]
            FROM   [dbo].[cf_bp_worker_instance_history] AS [Extent4]
            INNER JOIN [dbo].[cf_bp_worker_instances] AS [Extent5] ON [Extent4].[instance_id] = [Extent5].[instance_id]
            INNER JOIN [dbo].[cf_bp_steps] AS [Extent6] ON ([Extent4].[step_id] = [Extent6].[step_id]) AND ([Extent4].[process_id] = [Extent6].[process_id]) ) AS [Join4] ON ([Distinct1].[step_id] = [Join4].[step_id1]) AND ([Join4].[bp_instance_id] = @p__linq__0) AND ('userTask' = [Join4].[step_type]) AND (0 = [Join4].[is_deleted])
    )  AS [Project2]
    ORDER BY [Project2].[step_id] ASC, [Project2].[C1] ASC

 

0 0

Replies

replied on September 19, 2023

Hi Blake,

This seems to be the code to generate user task tokens for a certain instance.

Can you tell us which version of Forms you are using so we could confirm?

0 0
replied on September 20, 2023

We are using Forms version 11.0.2212.30987.

0 0
replied on September 20, 2023

We have several deadlocks per day that involve that same type of query. The other competing query varies. Sometimes when that happens, it causes issues between Forms and Workflow (we have a lot of communication between the two) where we get SQL timeout errors and causes hundreds of Forms instances to suspend or terminate. It also causes issues with Workflow instances being able to execute correctly and send data back to Forms. The above case seems to happen randomly, but these queries are always shown in SQL monitoring application when it does.

0 0
replied on September 20, 2023

So I can confirm that the query was for generating user task tokens, which was a feature added in 11U3.

We have identified a performance issue that tokens were being generated too frequently. In case of a workflow callback, it would generate the tokens at least twice. We have some improvement on this, which is targeting 11U5 by the end of this year.

But we haven't got feedback on single query being timed out.

So for your case, have you tried rebuilding indexes on the related tables? That may help improve the query performance.

0 0
replied on September 21, 2023

I do know that indexes are rebuilt on a frequent schedule. I would need to check with our dba's to find out how frequent.

I look forward to the 11U5 update to see if it helps address how often we are seeing the deadlocks.

Can you give more detail as to what this feature does?

0 0
replied on September 24, 2023

In token picker you would find user task related tokens like this:

That query was used to get the user task info, so the tokens could be parsed.

0 0
replied on September 25, 2023

Would we be able to get a hotfix for this query to see if it resolves the issues we are seeing?

0 0
replied on September 27, 2023

If you meant performance improvement I mentioned about, that is part of a large refactor item so we are not able to provide it as a hotfix. And it does not change the query itself, just reduces the frequency of calling it.

If you would like to get a hotfix on the query, would you be able to open a support case and provide us your database backup? Then we could identify the bottleneck and see how we can improve it.

0 0
replied on September 28, 2023

Yes, I am currently requesting a backup of our Forms database and will open a support ticket with our Solution Provider once done. Just a heads up, it is around 130GB.

0 0
replied on October 3, 2023

A support ticket with our solution provider has been open and they opened a Laserfiche Support Case #234442.

0 0
replied on October 18, 2023

Sorry for the late reponse, I just checked the ticket but I didn't see a Forms database uploaded yet. Instead a LFDS database was uploaded. Can you please check with SP what's the current status of this ticket?

0 0
replied on October 18, 2023

I apologize. I uploaded the wrong file. Too many support cases open. I am uploading the Forms database now as file name 234442-3.zip. It should be finished uploading in about 30 minutes.

0 0
replied on October 20, 2023

The support case is not updated yet and I was not able to check ftp uploaded files directly. Can you please ask the support to open a ticket to development and pass the file to us? Thanks.

0 0
replied on October 20, 2023

I have asked our SP to update the LF Support case.

0 0
replied on November 6, 2023

As an update, Laserfiche was able to reproduce the deadlock issue and gave us a SQL query to run that has significantly reduced the number of deadlocks we were seeing. They are also working on a fix to prevent the issue from happening in the future.

1 0
You are not allowed to follow up in this post.

Sign in to reply to this post.