Access query to SQL Server runs slow-CodePudding

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?

CodePudding user response：

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 indexs 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.