MS Access slow to SELECT against a pass-through query

Question

My customer is using MSAccess to read SQL Server Data.

Originally they created a linked table to the SQLServer base table, then in Access, created a Query that aggregated and filtered.

Select f1,f2,sum(f3),sum(f4)
From linkedtablename
where fx = 'somevalue'
group by f1,f2

For security & performance reasons, I built simple a query in MSSQL to do the filtering & aggregating, and asked the user to point to that instead, with a passthrough query.

So now they have and ODBC 'passthroughquery' doing a 'select * from MSSQLview'

However, MSAccess seems to be really struggling when we do anything with this passthrough. e.g. Adding the passthrough to a new MSAccess query window takes forever. Seems as if Access is doing some heavy reading of the source or source metadata each time we interact with it.

Running a select against the passthrough is also taking an age ... but with the aggregating being done by MSSQL, it should be a lot faster !?

So the question is, why does MSAccess struggle so much ? Is Access trying to profile the source data even without an explicit 'select' being done ? Or is it trying to read metadata every time we interact with the Passthrough ?

Ultimately, I am hoping that there is some configuration setting that will force Access to treat this like a 'standard' table !

Answer 1

If you use a PT query, keep in mind that if you use this as a source for a client query, then ZERO additional filtering can be used, or will work.

In other words?

A PT query is one of the fastest (high performance) ways to pull data. BUT ONLY if you do NOT attempt to add additional filtering. A PT query cannot be filtered by client side (well, it can, but ONLY after pulling the PT full query source).

As a result?

Use a linked view. They perform JUST as well as a stored procedure and a PT query, but they can and do respect client side filtering. So, for example you can build a client side query against that linked view with criteria, and ONLY the records matching that query are pulled down the network pipe.

this seems somewhat counter intuitive, but PT queries are fast, but they DO NOT respect additional filters client side (to be specific, you can filter against a pt query, but Access ONLY does so after pulling all records in the PT query). So, one would do VERY well to say avoid using a PT query to fill a combo box, or any thing else client side that will and does attempt to apply additional filtering and criteria.

To be crystal clear:

A PT query is great, but ONLY if you going to have the PT query do the filter in the first place. Additional filtering can be done, but that assumes the original PT query did not pull a lot of data in the first place. So a PT query rows pulled is what you WILL GET client side.

In 99% of cases, you are FAR better off to put that query in a linked view, and thus you are free to filter and add criteria to that view (even client side), and ONLY records meeting that criteria are pulled down the network pipe. this includes even using a client side query on that linked view. And this also includes basing a report on that linked view, and say you have VBA to add/provide a "where" clause to that report. (in this case, once again, Access will ONLY pull records based on that criteria. If you use a PT query for the report and attempt to filter - that filtering ONLY occurs AFTER all PT records been pulled.

So, PT queries cannot effective REDUCE bandwidth requirements to ANY lower then what the PT query returns in the first place. However, linked views DO allow and DO respect additional filters applied - even when done client side. As a result, a PT query is not all that useful unless the PT query has the criteria pre-defined and known ahead of time.

So, I would strong suggest you try/test/use a linked view.

In other words, put that sum and group by in a server side view and link to that.

Edit: and you CAN add the where clause to that view client side against that view.

However, because Fx is not a column, then you have to either add that column to the view, or create a stored procedure, and use it this way:

CREATE PROCEDURE [dbo].[GetMyGroupSum]
   @fx nvarchar(50)
AS
BEGIN
   SET NOCOUNT ON;

   SELECT f1, f2, SUM(f3), SUM(f4)
   FROM dbo.TheTableToQueryOn
   WHERE fx = @fx
   GROUP BY f1,f2
END

Now, create a PT query in Access. You code will then look like:

dim strFX    as string
strFX = "somevalue"

currentdb.querydefs("MyPtQuery").sql = "EXEC GetMyGroupSum '" & strFX & "'"

You are now free to use the above query for a report, code or even launch a form based on that PT query.

In MOST cases you are better to use a view, but because the query does NOT return the column you need to filter on, then a PT query is the solution, but in most cases, it is not.