Access query to SQL Server runs slow

I have a Main table of 5000 rows, a Managers table of 51 rows and a Phase table of 16 rows. I created a query with a LEFT JOIN from the Main table to each of the other two:

SELECT [tblTrue-UpMain].Contract_Number, [tblTrue-UpMain].ProjectName, [tblTrue-UpMain].AFGroupName, [tblTrue-UpMain].AFAccountMgr, [tblTrue-UpMain].AFSubStationName, [tblTrue-UpMain].AFAssociatedContract, [tblTrue-UpMain].AFPreviousFile, [tblTrue-UpMain].AFFinancing, [tblTrue-UpMain].AFReplaceCoverage, [tblTrue-UpMain].AFBillingRate, [tblTrue-UpMain].AFProjectType, [tblTrue-UpMain].AFPreviousClients, [tblTrue-UpMain].ID, [tblTrue-UpMain].AF_VS_File_Number, [tblTrue-UpMain].Customer_Number, [tblTrue-UpMain].Contract_Execution_Date, [tblTrue-UpMain].Service_Account, [tblTrue-UpMain].Contract_Account, [tblTrue-UpMain].Contract_No_IF_Or_AF, [tblTrue-UpMain].CM, [tblTrue-UpMain].FinanceReplacementCPUC, [tblTrue-UpMain].Project_Type, [tblTrue-UpMain].Contract_Status, [tblTrue-UpMain].Contract_Phase, Local_ContractManagers.Contract_Manager, Local_Contract_Phase.PhaseName
FROM ([tblTrue-UpMain] 
LEFT JOIN Local_ContractManagers ON [tblTrue-UpMain].CM = Local_ContractManagers.CMID) 
LEFT JOIN Local_Contract_Phase ON [tblTrue-UpMain].Contract_Phase = Local_Contract_Phase.PhaseID
WHERE ((([tblTrue-UpMain].Contract_Number) Like "AF*"))
ORDER BY [tblTrue-UpMain].Contract_Number;

[tblTrue-UpMain].CM and [tblTrue-UpMain].Contract_Phase are both indexes. [Removing these indexes did not impact the time either way.] Local_ContractManagers.CMID and Local_Contract_Phase.PhaseID are both Primary Keys. [INNER JOINS instead of LEFT JOINS ran for about 1 second in both cases.

When I run the query with the Managers and Phase tables as Access tables, it takes 1 second or less. If I run it with the Managers and Phase tables on SQL Server, it takes over 40 seconds. The Main table is on SQL Server for both queries.]

Running the query on SSMS (with all tables on SQL Server) runs quickly too.

Any ideas why it might be running so long?

Solution

As long as the tables are all SQL server based?

Then the query should not take that long. However, if you mix a local table and a SQL server table, then it will be slow as turtles.

However, one way to fix the issue?

Fire up SQL manager, and build the same query in SQL server.

In fact, choose create new view. A SQL "view" is really like a Access saved query, and you get to use familiar query builder (it similar to the Access one). you will note that sorting is not in general allowed, so, do that in the report, or even build a client-side query against that view.

Now, save this view, and then create a link to this view Access side.

I do this so often, I have a little helper VBA routine to add this one link, and that's a whole lot less than using the ODBC manager from the ribbon.

Now, in place of the local query you have, try using the SQL "view" (it will appear as a linked table).

The result should be a VERY high performance, and as good as when you try the query in sql studio.

Views tend to be a far better choice than say using a stored procedure, since it is much easier to use in Access and even VBA code.

In fact, I recommend giving the linked view the SAME name as what you had for the local SQL query. That way, existing forms, code or reports don't require any modifications, and you get fantastic performance.

This performance includes even opening a report with a "where" clause. So, you can still use client-side filters, or even one's created in VBA that launches the report with "where" clause. Access will ONLY pull those records that meet the criteria (and thus this approach is a network/bandwidth friendly approach).

So, anytime you encounter a slow turtle like performance? Try the view approach, it has fixed every performance issue I seen.

For a simple query based on one table? Then a view doesn't help. But, once you start introduction of joins and additional tables, then access can't do a proper join without pulling a lot of data - so a view really helps.

[tblTrue-UpMain].CM and [tblTrue-UpMain].Contract_Phase are both indexes.

We assume you talking about SQL server-side indexes. Do NOT attempt to create index's client side - they don't do anything and can't be used.

I also STRONG suggest you do NOT attempt to create index(s) server side when using a view. ALWAYS create the index(s) on the base sql server tables server side - nothing more, nothing less.

Edit: Adding a link with vba in place of odbc manager.=

So, as noted, since one often needs to add one new talbe, or one new view (as a link from SQL server), then I wrote a VBA routine to make save time.

Nothing speical about the routine. I have a boatload of such routines. I mean, once one adopts a split database, then for ease of deployment, we all over time "cobbile" together some re-link code. This holds true for just a access back end, or a sql server one.

So, my little VBA helper routine to add one link?

Say I just created a new view sql server side. say viewCustomers.

Now, I need a linked table (or view) in the client side. Too much time + pain to fire up the odbc manager and go through all those steps to link just one table.

So, I have this routine, and run it from the debug window. So, I'll hit ctrl-g, and in the debug window type in this:

MyCreateOneLink "server table", "Local name for link"

So, the above is much quicker.

So, what does the above routine look like?

In my standard code module, I have this:

Sub MyCreateOneLink(strServerTable As String, strLocalTable As String)
  
  Dim tdfcurrent          As DAO.TableDef
  
  Set tdfcurrent = CurrentDb.CreateTableDef(strLocalTable)
  
  tdfcurrent.connect = dbCon("MYSERVER\SQLEXPRESS", "Customers", "MyUserName", "MyPassword", cControlPassword, "MyApp")
  
  tdfcurrent.SourceTableName = strServerTable
  CurrentDb.TableDefs.Append tdfcurrent


End Sub

And above calls a routine "dbcon()". That routine creates a valid SQL connection string from values passed.

this:

Public Function dbCon(ServerName As String, _
                     DataBaseName As String, _
                     Optional UserID As String, _
                     Optional USERpw As String, _
                     Optional APP As String = "Office 2010", _
                     Optional WSID As String = "Axis") As String

   ' returns a SQL server conneciton string
   
  dbCon = "ODBC;DRIVER=SQL Server;" & _
          "SERVER=" & ServerName & ";" & _
          "DATABASE=" & DataBaseName & ";"
          If UserID <> "" Then
             dbCon = dbCon & "UID=" & UserID & ";" & "PWD=" & USERpw & ";"
          End If
          dbCon = dbCon & _
          "APP=" & APP & ";" & _
          "WSID=" & WSID & ";" & _
          "Network=DBMSSOCN"

End Function

above will have to be changed for "native" driver, but the simple idea here is to have a quick code and thus from the access command prompt (debug window), I can type in a quick easy command to create a table link.

So, often during development, one will use the access command line (immediate) window, and we can thus call little VBA helper routines, such as one to create a table link for us.