Pass-through query slow when used as data source

[sharpnova]

So here's my setup. We have a database with several tables that have over 255 fields. So we must use pass-through queries to access some of the data.

Usually no problem.

Here is my pass-through query:

qry001: SELECT db001.table001.field001 from db001.table001

If I execute this, it instantly spits me to datasheet view with all the millions of records ready for viewing.

If I modify it to:

SELECT db001.table001.field001 from db001.table001 WHERE db001.table001.field001 = '123'

It works fine and displays the one record instantly.

Now on to the problem

When I make a new query, using this pass-through query as a data source such as:

SELECT qry001.field001 FROM tblLocal001 LEFT JOIN qry001 ON tblLocal.field001 = qry001.field001;

This query takes forever to run.

And the thing is, tblLocal001 only has one record in it. In production, this table will have thousands so I need to understand why this query is taking so long and fix it if possible.

Thanks for your help

 

[sharpnova]

Well tests show that the amount of time this query takes to run isn't dependent on the number of records in the local table.

So I'm guessing the "forever to run" is just the initial evaluation of the pass-through query.

And I'm guessing the reason the pass-through query runs so quickly on its own is because it's not evaluating in its entirety, but only enough to present the datasheet view, with the intent of continued evaluation on scrolling.

So I consider this resolved, unless someone can offer an alternate explanation.

 

[MSAccessRookie]

Well tests show that the amount of time this query takes to run isn't dependent on the number of records in the local table.

So I'm guessing the "forever to run" is just the initial evaluation of the pass-through query.

And I'm guessing the reason the pass-through query runs so quickly on its own is because it's not evaluating in its entirety, but only enough to present the datasheet view, with the intent of continued evaluation on scrolling.

So I consider this resolved, unless someone can offer an alternate explanation.

I have a question regarding your Table Structure. You say you have over 255 Fields. I am curious as to why you might need so many. A database Table does not often need to have that many Fields. Does your Table have groups of repeated data in a structure similar to the one below? If it does, then it might not be properly normalized, and you would do a great service to redesing the structures of this and other Tables like it.

PrimaryKey, (Group1Value1... Group1ValueLast), (Group2Value1... Group1ValueLast), (GroupLastValue1... GroupLastValueLast)

 

[sharpnova]

I have no control over or access to the structure of this database. It's not even based in my state. It's a large corporate database.

I agree that the table structure is absurd and as a result, a pain to work with.