Passthrough Query Performance Slow Using Split Database

[accessNator]

Prior to splitting the database, my PassThrough query performance was acceptable.
After splitting the database, my passthrough query has become sluggish and slow where my users are not very happy.

When I run a test prior to splitting my database, it would take about 1-5 seconds to retrieve the records using a passthrough.
But after splitting, now that passthrough query will take anywhere from 3 minutes - 10 minutes.

Can anyone shed some light on this? I am using Access 2007.

Here is my code:

Private Sub CompanyHistoryByPeriodTransactions(ContainerSubFormLabel As Label, ContainerSubForm As SubForm, ContainerSubFormObject As String, passQuery As String)
 
Dim startMonth As String
Dim endMonth As String

startMonth = Me.cboSelectMonth & "/1/" & cboSelectYear
endMonth = DateAdd("d", -1, DateAdd("m", 1, startMonth))
 
    ' Create Sql WHERE String
    Select Case Me.FrmOptionWorksheetStatus
        
        Case 1 ' Pending
            strSQL1Where = "WHERE FK_WSC_RefId = 1"
        Case 2 ' Accepted
            strSQL1Where = "WHERE FK_WSC_RefId = 2 and isApproved_datestamp >= '" & startMonth & "' and isApproved_datestamp <= '" & endMonth & "'"
        Case 3 ' Rejected
            strSQL1Where = "WHERE FK_WSC_RefId > 2 and isApproved_datestamp >= '" & startMonth & "' and isApproved_datestamp <= '" & endMonth & "'"
    End Select
    
    With ContainerSubForm
    .SourceObject = "sfrmOnlineSubmissionData"
    End With
    
     Dim Table1 As String
    Table1 = "tempKUSFWorksheets"
                 
    Dim strSQL1 As String
      
    ' Create Sql String From Table1
     strSQL1 = "SELECT TKW_RefId, FK_WSC_RefId, isApproved, isApproved_datestamp, cast(right(cid,4) as int) as cid1, "
     strSQL1 = strSQL1 & "plan_year, cut_off_date_period, currentdate, report_month, period_start, period_end, period_length, "
     strSQL1 = strSQL1 & "report_basic_id, revision, "
     strSQL1 = strSQL1 & "flow_thru_rev, "
     strSQL1 = strSQL1 & "local_exch_serv, wpm_monthly_charge, wpm_monthly_use, voip, intrastate_swithced_toll, toll_private_line, alt_access_dir, alt_payphone, misc_charges, total_intrastate_retail_rev, "
     strSQL1 = strSQL1 & "uncollectibles, "
     strSQL1 = strSQL1 & "net_intrastate_revenue, "
     strSQL1 = strSQL1 & "assessment_rate, lec_num_access_line, gross_assessment, "
     strSQL1 = strSQL1 & "support_payable, "
     strSQL1 = strSQL1 & "lifeline_num_lines1, lifeline_discount1, lifeline_num_lines2, lifeline_discount2, lifeline_support, "
     strSQL1 = strSQL1 & "total_assessment, "
     strSQL1 = strSQL1 & "assessment_transferred_account, assessment_transferred_amount, "
     strSQL1 = strSQL1 & "net_kusf_assessment_payment, "
     strSQL1 = strSQL1 & "late_penalty, "
     strSQL1 = strSQL1 & "signature_name, signature_title, unique_worksheet_id, submission_db_datestamp "
     strSQL1 = strSQL1 & "FROM " & Table1 & " "
     strSQL1 = strSQL1 & strSQL1Where & ";"
MsgBox "1" & Now()
' Using Pass Through Query
CreateSPT passQuery, strSQL1
MsgBox "2" & Now()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rst1 As DAO.Recordset

    Set rst1 = db.OpenRecordset(passQuery, dbOpenSnapshot)

   Dim FindRecordCount As Double
   If rst1.EOF Then
      FindRecordCount = 0
   Else
      rst1.MoveLast
      FindRecordCount = rst1.RecordCount
   End If

    If rst1.RecordCount = 0 Then
    Me.Repaint
    Me.txtStatus.ForeColor = vbBlue
    Me.txtStatus.Value = "No Records Found"
    
    ShowHideSubForm ContainerSubForm, ContainerSubFormObject, False
    
    Set rst1 = Nothing
    Set cn = Nothing
   
    Exit Sub
   
    End If

 Application.RefreshDatabaseWindow ' Refresh Objects list in Access

GetTotalRecords ContainerSubFormLabel, ContainerSubForm, ContainerSubFormObject, rst1.RecordCount, 380
ShowHideSubForm ContainerSubForm, ContainerSubFormObject, False

' Populate Subform with Recordsource
Forms!frmOnlineSubmission!sfrmContainerOnlineSubmissionData.Form.RecordSource = passQuery
' Set Subform visible property, IMPORTANT: this must follow after setting RecordSource of subform
Forms!frmOnlineSubmission!sfrmContainerOnlineSubmissionData.Visible = True

    Set rst1 = Nothing
    Set cn = Nothing
    Set db = Nothing
End Sub

Function CreateSPT(SPTQueryName As String, strSQL As String)

'Purpose:   Create a parameter query or action query using ADOX.

    Dim cat As Object
    Dim cmd As Object

    passConnServer = ConnServer

    On Error GoTo ErrorHandler

    Set cat = CreateObject("ADOX.Catalog")
    Set cmd = CreateObject("ADODB.Command")

    'Initialize.
    cat.ActiveConnection = CurrentProject.Connection
    Set cmd.ActiveConnection = cat.ActiveConnection

    cmd.CommandText = strSQL
    cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
    cmd.Properties _
     ("Jet OLEDB:Pass Through Query Connect String") = SetConnectionString(passConnServer) '1 - REMOTE SERVER AT DISCOUNT.ASP, 2 - LOCAL SERVER ON GVNW-LAB-1

    'Append the Command to the Procedures collection of the catalog.
    cat.Procedures.Append SPTQueryName, cmd
    'Clean up.
    Set cat = Nothing
    Set cmd = Nothing

    Exit Function

ErrorHandler:
      ' If Query already exists, delete it.
      cat.Procedures.Delete SPTQueryName
      Resume
End Function

In my sub CompanyHistoryByPeriodTransactions, I added a couple of message boxes to gauge how long it took to/from the passthrough query. This is where I see my slow down occurring.

Thoughts?

 

[Pat Hartman]

Splitting the database has nothing to do with the slowdown. The BE is SQL Server or whatever and that didn't change.

What happens when you change this to a select query on a linked table? I don't see any reason that it needs to be a pass-through query.

 

[accessNator]

Splitting the database has nothing to do with the slowdown. The BE is SQL Server or whatever and that didn't change.

What happens when you change this to a select query on a linked table? I don't see any reason that it needs to be a pass-through query.

Pat, my passthrough query, queries a remote server not locally on our network.
Shouldn't I use a passthrough so I can use my t-sql statements on a table that resides on a remote server?

I guess, I am wondering prior to the split, passthrough is fine but after the split, it is slow.

 

[Pat Hartman]

You can link to the table in the server and write standard Access queries against it (T-SQL won't work). Access attempts to "pass-through" every query you write against a linked ODBC server table. There is a slight bit of overhead since Jet/ACE is invoked first to reformat the Access SQL to T-SQL but after that, the query is sent to the server for processing. With a pass-through query, Jet/ACE doesn't get involved, the query is just passed. In either case the query executes on the server so unless there is some SQL element you need to use that is not supported by Access, there isn't any real benefit to using pass-through queries most of the time. The only pass-through queries I have ever used, were bulk delete queries. Because Access creates a transaction around action queries so it can back them out, deletes seem to be especially slow even for moderately sized tables.

 

[accessNator]

You can link to the table in the server and write standard Access queries against it (T-SQL won't work). Access attempts to "pass-through" every query you write against a linked ODBC server table. There is a slight bit of overhead since Jet/ACE is invoked first to reformat the Access SQL to T-SQL but after that, the query is sent to the server for processing. With a pass-through query, Jet/ACE doesn't get involved, the query is just passed. In either case the query executes on the server so unless there is some SQL element you need to use that is not supported by Access, there isn't any real benefit to using pass-through queries most of the time. The only pass-through queries I have ever used, were bulk delete queries. Because Access creates a transaction around action queries so it can back them out, deletes seem to be especially slow even for moderately sized tables.

Okay, I'll try linking instead and see what happens. I guess, and I must have read and misunderstood the thought process of using passthrough on remote database tables. I am not using specialized SQL syntax.

Thank you for the follow ups. But I know it still perplexes me the big difference when I run that passthrough using a non-split database and a split database when basically that is the difference.

 

[Pat Hartman]

Don't worry. There's lots of people who advise the use of pass-through queries because they don't understand that Access natively tries to "pass-through" every query.

 

[accessNator]

Don't worry. There's lots of people who advise the use of pass-through queries because they don't understand that Access natively tries to "pass-through" every query.

Now I have changed it where I have the remote table linked and this is my revised code:

Private Sub CompanyHistoryByPeriodTransactions(ContainerSubFormLabel As Label, ContainerSubForm As SubForm, ContainerSubFormObject As String, passQuery As String)
 
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rst1 As DAO.Recordset
    
    On Error Resume Next
    ' Delete Querie(s) If exist
    db.QueryDefs.Delete passQuery
    On Error GoTo 0
                 
    Dim qdf As DAO.QueryDef
    Dim strSQL1 As String
 
 
Dim startMonth As String
Dim endMonth As String

startMonth = Me.cboSelectMonth & "/1/" & cboSelectYear
endMonth = DateAdd("d", -1, DateAdd("m", 1, startMonth))
 
    ' Create Sql WHERE String
    Select Case Me.FrmOptionWorksheetStatus
        
        Case 1 ' Pending
            strSQL1Where = "WHERE FK_WSC_RefId = 1"
        Case 2 ' Accepted
            strSQL1Where = "WHERE FK_WSC_RefId = 2 and isApproved_datestamp >= '" & startMonth & "' and isApproved_datestamp <= '" & endMonth & "'"
        Case 3 ' Rejected
            strSQL1Where = "WHERE FK_WSC_RefId > 2 and isApproved_datestamp >= '" & startMonth & "' and isApproved_datestamp <= '" & endMonth & "'"
    End Select
    
    With ContainerSubForm
    .SourceObject = "sfrmOnlineSubmissionData"
    End With
    
     Dim Table1 As String
    Table1 = "dbo_tempKUSFWorksheets"
                 
        
    ' Create Sql String From Table1
     strSQL1 = "SELECT TKW_RefId, FK_WSC_RefId, isApproved, isApproved_datestamp, right(cid,4)as cid1, "
     strSQL1 = strSQL1 & "plan_year, cut_off_date_period, currentdate, report_month, period_start, period_end, period_length, "
     strSQL1 = strSQL1 & "report_basic_id, revision, "
     strSQL1 = strSQL1 & "flow_thru_rev, "
     strSQL1 = strSQL1 & "local_exch_serv, wpm_monthly_charge, wpm_monthly_use, voip, intrastate_swithced_toll, toll_private_line, alt_access_dir, alt_payphone, misc_charges, total_intrastate_retail_rev, "
     strSQL1 = strSQL1 & "uncollectibles, "
     strSQL1 = strSQL1 & "net_intrastate_revenue, "
     strSQL1 = strSQL1 & "assessment_rate, lec_num_access_line, gross_assessment, "
     strSQL1 = strSQL1 & "support_payable, "
     strSQL1 = strSQL1 & "lifeline_num_lines1, lifeline_discount1, lifeline_num_lines2, lifeline_discount2, lifeline_support, "
     strSQL1 = strSQL1 & "total_assessment, "
     strSQL1 = strSQL1 & "assessment_transferred_account, assessment_transferred_amount, "
     strSQL1 = strSQL1 & "net_kusf_assessment_payment, "
     strSQL1 = strSQL1 & "late_penalty, "
     strSQL1 = strSQL1 & "signature_name, signature_title, unique_worksheet_id, submission_db_datestamp "
     strSQL1 = strSQL1 & "FROM " & Table1 & " "
     strSQL1 = strSQL1 & strSQL1Where & ";"


   ' Initialize Query
    Set qdf1 = db.CreateQueryDef(passQuery, strSQL1)

    Set rst1 = qdf1.OpenRecordset(dbOpenSnapshot)

   Dim FindRecordCount As Double
   If rst1.EOF Then
      FindRecordCount = 0
   Else
      rst1.MoveLast
      FindRecordCount = rst1.RecordCount
   End If

    If rst1.RecordCount = 0 Then
    Me.Repaint
    Me.txtStatus.ForeColor = vbBlue
    Me.txtStatus.Value = "No Records Found"
    
    ShowHideSubForm ContainerSubForm, ContainerSubFormObject, False
    
    Set rst1 = Nothing
    Set cn = Nothing
   
    Exit Sub
   
    End If

 'Application.RefreshDatabaseWindow ' Refresh Objects list in Access

GetTotalRecords ContainerSubFormLabel, ContainerSubForm, ContainerSubFormObject, rst1.RecordCount, 380
ShowHideSubForm ContainerSubForm, ContainerSubFormObject, False

' Populate Subform with Recordsource
Forms!frmOnlineSubmission!sfrmContainerOnlineSubmissionData.Form.RecordSource = passQuery
' Set Subform visible property, IMPORTANT: this must follow after setting RecordSource of subform
Forms!frmOnlineSubmission!sfrmContainerOnlineSubmissionData.Visible = True

    Set rst1 = Nothing
    Set cn = Nothing
    Set db = Nothing
End Sub

Now the problem that I am running into is when the Subform is in view which now contains the records, the subform constantly refreshes on scroll bar movement or clicking within the subform.

You know what causes this?

 

[Pat Hartman]

I would do this with a saved querydef and NO code.

The where clause in the query would be:

Where (Forms!frmOnlineSubmission!FK_WSC_RefI = 1) OR (Forms!frmOnlineSubmission!FK_WSC_RefI >= 2 And isApproved_datestamp >= Forms!frmOnlineSubmission!startMonth and isApproved_datestamp <=  Forms!frmOnlineSubmission!endMonth)

So there is no need to incure the bloating and overhead of creating queries in code and changing the subform's RecordSource at runtime.

 

[Galaxiom]

Now the problem that I am running into is when the Subform is in view which now contains the records, the subform constantly refreshes on scroll bar movement or clicking within the subform.

I have experience this behaviour when the subform recordset is coming from queries linked to large SQL Server tables and using the LinkMaster/ChildFields of the subformcontrol. The subform was very slow to respond to moving the focussed record in the main form.

Performance had been quite OK with an Access BackEnd table with about nine million records giving a response in about a second. The increased to several seconds when I used a linked table on the SQL server with 26 million records and the visual effect unpleasant when scrolling past the edge of the subform.

I got spectacularly superior performance using a dynamic adOpenStatic ADODB recordset built by a Stored Procedure with parameters based on the current record in the main form. The recordset was created and loaded at the OnCurrent Event of the main form.

This arrangement returns the minimum number of records in the shortest possible time. Response is virtually instant even when returning a hundred or more records to the subform from a server on the LAN.

 

[accessNator]

I would do this with a saved querydef and NO code.

The where clause in the query would be:

Where (Forms!frmOnlineSubmission!FK_WSC_RefI = 1) OR (Forms!frmOnlineSubmission!FK_WSC_RefI >= 2 And isApproved_datestamp >= Forms!frmOnlineSubmission!startMonth and isApproved_datestamp <=  Forms!frmOnlineSubmission!endMonth)

So there is no need to incure the bloating and overhead of creating queries in code and changing the subform's RecordSource at runtime.

Hi Pat, I am not sure what you mean using NO code. Can you explain it to me in more detail? Are you referring building queries in code is not advisable?

Also, the Where SQL statement part has a variable I define in code. e.g. endMonth

I am not sure how I use this without code.

 

[accessNator]

I have experience this behaviour when the subform recordset is coming from queries linked to large SQL Server tables and using the LinkMaster/ChildFields of the subformcontrol. The subform was very slow to respond to moving the focussed record in the main form.

Performance had been quite OK with an Access BackEnd table with about nine million records giving a response in about a second. The increased to several seconds when I used a linked table on the SQL server with 26 million records and the visual effect unpleasant when scrolling past the edge of the subform.

I got spectacularly superior performance using a dynamic adOpenStatic ADODB recordset built by a Stored Procedure with parameters based on the current record in the main form. The recordset was created and loaded at the OnCurrent Event of the main form.

This arrangement returns the minimum number of records in the shortest possible time. Response is virtually instant even when returning a hundred or more records to the subform from a server on the LAN.

Hi Galaxiom, I appreciate your reply but it was a bit over my head on how you resolved the issue. But what I did end up doing and it appears to work is that instead of defining the recordsource of the subform in code (which refers to a remote link database table). I defined the recordsource in the subform instead.

I had no idea that made a difference but it did. There is soooo much to learn for me.

 

[Pat Hartman]

Are you referring building queries in code is not advisable?

No. It is the largest cause of database bloat. Queries should be saved as querydefs unless they are variable and so really do need to be created at runtime.

Where SQL statement part has a variable I define in code. e.g. endMonth

I thought those were form fields and that's how I referenced them in the where clause. You will probably need to change the form name and you can change the control names to match your names.

I am not sure how I use this without code

Create the query using the QBE, switch to SQL view and add a where clause like what I suggested. Test the query. Open the report or form where you want to show this data and replace the RecordSource with the name of the querydef. That's it! No code!

 

[accessNator]

No. It is the largest cause of database bloat. Queries should be saved as querydefs unless they are variable and so really do need to be created at runtime.
I thought those were form fields and that's how I referenced them in the where clause. You will probably need to change the form name and you can change the control names to match your names.
Create the query using the QBE, switch to SQL view and add a where clause like what I suggested. Test the query. Open the report or form where you want to show this data and replace the RecordSource with the name of the querydef. That's it! No code!

Thank you Pat, your response was detailed enough for me to understand now.
Cheers!