Jet Engine's query-optimization is based on statistics, but how are they refreshed with linked tables?

Question

Hi there,

i am dealing with some legacy access < 2007 "databases". All the tables are linked tables to a SQL Server 2008. While the odbc-links are established the table structure is copied into the mdb-file together with the DSN-string. Thats what one can see in Msysobjects.

A query forces the jet-engine to retrieve the data from SQL Server under respect of the jet-engines optimization. Therefore it is essential to refresh the odbc-links whenever a change in sql server is made, like adding a new index par example too.

As some tables grow larger the jet-optimizer needs refreshed statistics but which process does that? Is it Repair & Compact or refreshing the ODBC-Links? Can this be done programatically?

Peace

Ice

Answer 1

It's important to realize that only certain types of table statistics and metadata for ODBC data sources are going to be relevant to Jet. In general, I think the indexes are the only part that really matters from the standpoint of Jet deciding what to do with a SQL statement -- as long as the indexes are there and it understands the server on the other end, it will hand everything over to the server to process unless you've foolishly selected or sorted on an expression that the server database can't understand. In that case, Jet may still be smart enough to send parts of the SQL statement for processing by the server (such as joins and criteria on literals), and then use the result set returned with the expression.

In regard to the question about linked tables, thouh, unfortunately, there is no dynamic way to update ODBC linked tables -- for instance, add a column to a table or view and it won't be in the table returned, refresh the link and the new column will appear, but it might be read-only.

I have found that the only thing to do when changing your server's table structures and views is to simply delete and recreate the relevant linked tables. I don't know that there's any programmatic way to do this. If there's any metadata about the table/view that tells when its structure was last updated, you might be able to check that against the date of the linked table, and delete and recreate when the back end has been updated after the link, but I'm just guessing here. But that would be back-end specific.

For completeness sake, I'll just give the basics with a Jet back end:

With Access to Jet, a compact should reset the table stats, and upon the next run of each SQL statement, the compilation for each statement will be discarded and the query plan recalculated based on the new stats. It's important if you're compacting a front end for the purpose of updating table stats that you compact the back end before you compact the front end, because that insures that all the back-end table stats are as accurate as possible (though most of them stay up-to-date even without a compact).

Answer 2

I’m sure I will be corrected if I am wrong but I thought that re-linking the tables forced the statistics to be updated. If it is only a few tables then you could use the built in “Linked table manager” to do this. If not then you could automate it in code and then just fire off that function every time you wanted to update the table statistics.

I have some code that relinks access tables to other paths that I'm sure you could modify to work with ODBC linked tables

Answer 3

The most important thing that JET needs to make good optimisation decisions is correct indices on tables. You can check what indices are visible to JET by opening a linked tables (ODBC) in design mode and clicking on the indices icon. You will get a warning to say that the tables are read-only.

Creating indices on source tables is likely to be more effective that waiting for JET to build up optimisation meta data when queries are executed.