It takes too long to download data from server (1 Viewer)

[TipsyWolf]

Hey everyone!

This is my first time doing this, so setting it all up feels pretty advanced for me.

I had my split database ready to go using SMB protocol (local network), but my IT guy suggested moving the tables to a server instead. I knew that was a more scalable solution, so he set up the server, and I exported all my tables via ODBC. BTW, everything runs smoothly on my PC - no issues there.

I installed the driver, and it looks like this:

ISSUE:
One of my forms uses a table with just 90 records as its record source, but it takes a ridiculous 20 seconds (!) to load and display everything. Here’s the SQL it’s using:

SELECT TrainingAttendance.TrainingAttendanceID, Customers.CustomerName, CustomerSite.SiteTitle, Trainers.tFullName, TrainingTypes.TrainingName, TrainingAttendance.Date1, TrainingAttendance.Date2, [OverallAvgRate]/100 AS Rscore, [Score]/100 AS Qscore, TrainingAttendance.tblDateTime
FROM CustomerSite INNER JOIN (TrainingTypes INNER JOIN (Trainers INNER JOIN (Customers INNER JOIN ((qryEmpRscores_by_taID INNER JOIN TrainingAttendance ON qryEmpRscores_by_taID.TrainingAttendanceID_FK = TrainingAttendance.TrainingAttendanceID) INNER JOIN qryEmpQscores_by_taID ON TrainingAttendance.TrainingAttendanceID = qryEmpQscores_by_taID.TrainingAttendanceID_FK) ON Customers.CustomerID = TrainingAttendance.CustomerID_FK) ON Trainers.TrainerID = TrainingAttendance.TrainerID_FK) ON TrainingTypes.TrainingTypeID = TrainingAttendance.TrainingTypeID_FK) ON (Customers.CustomerID = CustomerSite.CustomerID_FK) AND (CustomerSite.CustomerSiteID = TrainingAttendance.CustomerSiteID_FK);

Now, I’ve got another form that needs to display data from a table with nearly 6,000 records, and it takes forever to load.

To me, it seems like something’s making simple tasks way more complex - like it might be repeating the same steps over and over in some kind of intense loop?

My IT guy says the server sends the data in less than a second, so that’s not the bottleneck.

Also, in my dashboard, I had two custom text boxes with Control Sources like this:

=DLookUp("ravg";"q_count_globals")/100

=DLookUp("qavg";"q_count_globals")/100

Just these two simple DLookups made the form load super slow, so I had to remove them.

Could the issue be with the queries? But I have other queries in the dashboard that work fine.
Or could it be the VBA code in these forms? Here’s the strSQL VBA I’m using for a form that takes 20 seconds to display just 90 records:

Option Compare Database
Option Explicit

Private Sub Form_Load()
    ' No initialization needed
    TranslateForm Me
End Sub
' ------ COMBO BOX EVENT HANDLERS ------
Private Sub cboCustomerName_AfterUpdate()
    ' Update SiteTitle combo based on selected customer
    Me.cboSiteTitle.rowSource = _
        "SELECT DISTINCT CustomerSite.SiteTitle " & _
        "FROM CustomerSite " & _
        "INNER JOIN Customers ON CustomerSite.CustomerID_FK = Customers.CustomerID " & _
        "WHERE Customers.CustomerName = '" & Replace(Me.cboCustomerName, "'", "''") & "' " & _
        "ORDER BY CustomerSite.SiteTitle;"
 
    Me.cboSiteTitle = Null ' Clear previous selection
    Me.cboSiteTitle.Requery
    ApplyFilters
End Sub

Private Sub cboSiteTitle_AfterUpdate()
    ApplyFilters
End Sub

Private Sub cboFullName_AfterUpdate()
    ApplyFilters
End Sub

Private Sub cboTrainingName_AfterUpdate()
    ApplyFilters
End Sub
' ------ FILTER FUNCTION ------
Private Sub ApplyFilters()
    Dim strFilter As String
    Dim strSQL As String
 
    ' Customer filter
    If Not IsNull(Me.cboCustomerName) Then
        strFilter = strFilter & " AND Customers.CustomerName = '" & Replace(Me.cboCustomerName, "'", "''") & "'"
    End If
 
    ' Site filter
    If Not IsNull(Me.cboSiteTitle) Then
        strFilter = strFilter & " AND CustomerSite.SiteTitle = '" & Replace(Me.cboSiteTitle, "'", "''") & "'"
    End If
 
    ' Trainer filter
    If Not IsNull(Me.cboFullName) Then
        strFilter = strFilter & " AND Trainers.tFullName = '" & Replace(Me.cboFullName, "'", "''") & "'"
    End If
 
    ' Training filter
    If Not IsNull(Me.cboTrainingName) Then
        strFilter = strFilter & " AND TrainingTypes.TrainingName = '" & Replace(Me.cboTrainingName, "'", "''") & "'"
    End If
 
    ' Date Range filter (for date1)
    If Not IsNull(Me.dateStart) And Not IsNull(Me.dateEnd) Then
        ' Filter between two dates
        strFilter = strFilter & " AND TrainingAttendance.date1 BETWEEN #" & Format(Me.dateStart, "yyyy\/mm\/dd") & "# AND #" & Format(Me.dateEnd, "yyyy\/mm\/dd") & "#"
    ElseIf Not IsNull(Me.dateStart) Then
        ' Only start date provided (filter from start date onward)
        strFilter = strFilter & " AND TrainingAttendance.date1 >= #" & Format(Me.dateStart, "yyyy\/mm\/dd") & "#"
    ElseIf Not IsNull(Me.dateEnd) Then
        ' Only end date provided (filter up to end date)
        strFilter = strFilter & " AND TrainingAttendance.date1 <= #" & Format(Me.dateEnd, "yyyy\/mm\/dd") & "#"
    End If
 
    ' Build final SQL - including tblDateTime in the SELECT statement
    strSQL = "SELECT TrainingAttendance.TrainingAttendanceID, Customers.CustomerName, " & _
             "CustomerSite.SiteTitle, Trainers.tFullName, TrainingTypes.TrainingName, " & _
             "TrainingAttendance.Date1, TrainingAttendance.Date2, TrainingAttendance.tblDateTime, " & _
             "[OverallAvgRate]/100 AS Rscore, [Score]/100 AS Qscore " & _
             "FROM TrainingTypes INNER JOIN (Trainers INNER JOIN ((Customers INNER JOIN " & _
             "CustomerSite ON Customers.CustomerID = CustomerSite.CustomerID_FK) INNER JOIN " & _
             "((qryEmpRscores_by_taID INNER JOIN TrainingAttendance ON qryEmpRscores_by_taID.TrainingAttendanceID_FK = TrainingAttendance.TrainingAttendanceID) " & _
             "INNER JOIN qryEmpQscores_by_taID ON TrainingAttendance.TrainingAttendanceID = qryEmpQscores_by_taID.TrainingAttendanceID_FK) " & _
             "ON (Customers.CustomerID = TrainingAttendance.CustomerID_FK) AND " & _
             "(CustomerSite.CustomerSiteID = TrainingAttendance.CustomerSiteID_FK)) " & _
             "ON Trainers.TrainerID = TrainingAttendance.TrainerID_FK) " & _
             "ON TrainingTypes.TrainingTypeID = TrainingAttendance.TrainingTypeID_FK" & _
             IIf(strFilter <> "", " WHERE " & Mid(strFilter, 6), "") & ";"
 
    ' Apply to form
    Me.RecordSource = strSQL
    Me.Requery
 
    ' Ensure tblDateTime textbox is properly bound
    If Not Me.Recordset.EOF Then
        Me.tblDateTime.ControlSource = "tblDateTime"
    End If
End Sub

' ------ CLEAR ALL FILTERS ------
Private Sub cmdClearFilters_Click()
    ' Clear combo boxes
    Me.cboCustomerName = Null
    Me.cboSiteTitle = Null
    Me.cboFullName = Null
    Me.cboTrainingName = Null
 
    ' Clear date range
    Me.dateStart = Null
    Me.dateEnd = Null
 
    ' Reset SiteTitle combo
    Me.cboSiteTitle.rowSource = "SELECT DISTINCT SiteTitle FROM CustomerSite ORDER BY SiteTitle;"
    Me.cboSiteTitle.Requery
 
    ' Reset record source - including tblDateTime in the SELECT statement
    Me.RecordSource = "SELECT TrainingAttendance.TrainingAttendanceID, Customers.CustomerName, " & _
                      "CustomerSite.SiteTitle, Trainers.tFullName, TrainingTypes.TrainingName, " & _
                      "TrainingAttendance.Date1, TrainingAttendance.Date2, TrainingAttendance.tblDateTime, " & _
                      "[OverallAvgRate]/100 AS Rscore, [Score]/100 AS Qscore " & _
                      "FROM TrainingTypes INNER JOIN (Trainers INNER JOIN ((Customers INNER JOIN " & _
                      "CustomerSite ON Customers.CustomerID = CustomerSite.CustomerID_FK) INNER JOIN " & _
                      "((qryEmpRscores_by_taID INNER JOIN TrainingAttendance ON qryEmpRscores_by_taID.TrainingAttendanceID_FK = TrainingAttendance.TrainingAttendanceID) " & _
                      "INNER JOIN qryEmpQscores_by_taID ON TrainingAttendance.TrainingAttendanceID = qryEmpQscores_by_taID.TrainingAttendanceID_FK) " & _
                      "ON (Customers.CustomerID = TrainingAttendance.CustomerID_FK) AND " & _
                      "(CustomerSite.CustomerSiteID = TrainingAttendance.CustomerSiteID_FK)) " & _
                      "ON Trainers.TrainerID = TrainingAttendance.TrainerID_FK) " & _
                      "ON TrainingTypes.TrainingTypeID = TrainingAttendance.TrainingTypeID_FK;"
    Me.Requery
 
    ' Ensure tblDateTime textbox is properly bound
    If Not Me.Recordset.EOF Then
        Me.tblDateTime.ControlSource = "tblDateTime"
    End If
End Sub

Where should I dig in to fix this? Any ideas?

UPD:
- I removed all vba just to test it - VBA doesn't affect it anyhow.
- I cleared out my form's Record Source to just a table (without FK tables) and it load within 1 seconds like it should. but now i have no data in my form

 

[Minty]

It looks like your first query is joining server tables to a couple of access local queries.
Move the entire thing to a server based query (a View in SQL Server) including the local queries.

If the local queries use form references as parameters, it gets more complicated, but can still be done with some planning.

I suspect your dlookups() source query is also an issue

Complicated or multiple joins should always be done on the server where possible, then filter the results locally if required.

 

[Gasman]

Now, I’ve got another form that needs to display data from a table with nearly 6,000 records, and it takes forever to load.

When you are using client server technology, you should be limiting the amount of data coming over the network.
So you would ask the user for a client identifier, and then bring data for that client only.

I have never used client server technology, as all my tables were local on a server PC in the LAN, but believe that is the method you should use.
Also any linked fields should be indexed.

 

[GPGeorge]

It looks like your first query is joining server tables to a couple of access local queries.

Spoiler:

Move the entire thing to a server based query (a View in SQL Server) including the local queries.

If the local queries use form references as parameters, it gets more complicated, but can still be done with some planning.

I suspect your dlookups() source query is also an issue

Complicated or multiple joins should always be done on the server where possible, then filter the results locally if required.

If this is the case, i.e. your queries join local Access tables or queries to remote SQL Server tables, the result is the slowest possible performance. The only time you should do that is when there is absolutely no other alternative.

Figure out a way to avoid having both local Access tables and remote SQL Server tables. Either move everything to the SQL Server database, or create temp tables in the Access accdb for the data you'll need to join with local tables later in the process.