Application stays busy 30 seconds after running a Stored Procedure

[lone_rider15]

[FONT=&quot]Background: I have an application with back end SQL Server 2014 and front end in Access. In the attached screenshot I am on the “Begin Review” form. The record source of the form is a linked table. The record comes in review if the “Audit Status” field value is “Assigned”. The “Complete” button runs a pass through query to run a stored procedure. The stored procedure updates a record’s “Audit Status” to “Assigned”. The stored procedure gets executed within less than a second.

[/FONT][FONT=&quot]Problem: Every time I navigate to any places (for example – Dashboard) and come back to “Begin Review” and click on “Complete” button for the first time the stored procedure runs within less than a second but the application stays busy for more than 30 seconds. From the second click on “Complete” button until I navigate to any places again every time the process takes less than a second.

[/FONT][FONT=&quot]Looking for solution: Reduce or if possible eradicate the busy (loading) 30 seconds time.

[/FONT] The code behind the form follows:

Private Sub Form_Load()
Call AssignReview
End Sub

Private Sub AssignReview()
Dim rst As Object

Set rst = Me.RecordsetClone
Me.Requery
If rst.EOF Or rst.RecordCount <= 1 Then
DoCmd.SetWarnings False
Call ModifyqryPassMarsQcInquiryAssign
DoCmd.SetWarnings True
Me.Requery
End If
End Sub
Private Sub ModifyqryPassMarsQcInquiryAssign()
Dim strSQL As String
Dim qdf As DAO.QueryDef

strSQL = "EXEC spMarsQcQueue '" & Forms(frmLogin)!txtUserId & "'"
Set qdf = CurrentDb.QueryDefs("qryPassMarsQcInquiryAssign")
qdf.SQL = strSQL
qdf.Execute
Set qdf = Nothing
End Sub

'------------------------------------------------------------
' cmdComplete_Click
'------------------------------------------------------------
Private Sub cmdComplete_Click()
On Error GoTo cmdComplete_Click_Err
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
Call AssignReview
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If
cmdComplete_Click_Exit:
Exit Sub
cmdComplete_Click_Err:
MsgBox Error$
Resume cmdComplete_Click_Exit
End Sub

 

[Minty]

That sounds like waiting for records to load.
Is it a large dataset?
Do you by default load every record in the table, then filter or search for a record?
Ideally if possible you should load with a empty recordset and then populate the form with the record you want to use.

 

[lone_rider15]

Thank you for looking into this.

That sounds like waiting for records to load.
Is it a large dataset?

The record gets loaded within a second when the form loads. There are 100K records in the table. But only 3000 meet the criteria to get loaded.

Do you by default load every record in the table, then filter or search for a record?

The records are already in the table but I run an update query to change "AuditStatus" field value to "Assigned" and "AssignedTo" field value to the user. Only the "Assigned" marked records associated with a user will be in the record source of the form for the user. The update query is to update top 1 record from a list of 3000 records. The query runs within less then a second.

Ideally if possible you should load with a empty recordset and then populate the form with the record you want to use.

How do I do that?

 

[Cronk]

How do I do that?

Set the form's recordsource to
select * from YourTableOrQuery where false

 

[lone_rider15]

In production environment it worked fine however it is. I didn't have to change anything. Than you all for the help.