Re: MS Access to Informix Views

Cory J. Elliott <celliott@corpinfo.com> wrote in article <322DFF8E.4C41@corpinfo.com>... > Here is my problem. > > I have about 100 users who are trained in using MS Access 2.0 to query a > Sybase database. We are converting from Sybase to Informix to support > some major applications. I have discovered in testing some of our > queries against the Informix database that the response times are > significantly slower on some queries specifically those where I am > trying to join two or more views > <snip> The problem is that MS-Access is tries to optimize the query in terms of network traffic. To do this, it attempts to determine a "bookmark" for each table (or view) in the query. Access uses the first unique index it finds as a book mark. If one or more tables in the query have no unique index, Access treats each such table (and the query overall) as a "snapshot" rather than a "dynaset". For reasons beyond my comprehension, Access does snapshot joins locally. Since views are not real tables, they have no indexes, so Access always treats them as "snapshots". There is, however, a work around! First, determine a set of columns from each view which comprise a unique key to that view. Ideally, these should correspond to indexes on the underlying tables of the view. If your views have no combination of columns which can be used as unique keys, then you may be out of luck. At the very least, you'll have to modify the views so they have a unique key. Next, go in to Access and attach the views. Perform a "data definition query" for each view as follows: CREATE UNIQUE INDEX view1_idx ON view1 (columns) Substitute the name you gave to the attachment for "view1" and the columns which make up the unique key for "columns". This statement fools Access in to thinking there is a unique index on the view! This does not perform any actual index creation on the server, it merely updates Access's internal definition structures. Now, try your query again. Access should end up processing it by fetching all of the qualifying "bookmarks" for each table (view). Individual rows are then fetched by using the "bookmarks" for each row. When it's working right, this yields excellent performance for scrolling through very large queries. MS has a white paper on the "Jet Engine" (the heart of Access) that describes this "bookmark/snapshot" thing (among other topics). I got it from CompuServe a couple of years ago. I'm sure it's been updated since then, and it can probably be found somewhere on their Web site. HTH