>Return-Path: <[log in to unmask]>
>X-PH: [log in to unmask]
>Encoding: 25 Text
>Date:         Wed, 16 Jul 1997 17:05:42 -0600
>Reply-To:     Eric Popish <[log in to unmask]>
>Sender:       HP-3000 Systems Discussion <[log in to unmask]>
>From:         Eric Popish <[log in to unmask]>
>Subject:      ODBC performance
>Comments: To: [log in to unmask]
>To:           [log in to unmask]
>
>Can someone shed a little light on how HP's ODBC driver retrieves data
>from an Image/SQL database. How does it determine whether to perform
>a serial read vs. a chained read on detail set or a serial read vs. a
>calculated read on a master set?
>
>My frustration and experience comes from using Microsoft Access 2.0 to
>query data from our Image/SQL databases. For the most part it works
>well with acceptable performance. But sometimes I feel like I've been
>sent to "query hell". Especially if I link Access tables to Image tables.
>
>For example, if I was to create a query to extract a few records out of a
>master dataset, where I specified the key item values I was looking for in
>the criteria line of an Access Query - I would get the results almost
>instantly. On the other hand, if instead of specifying the values  in the
>criteria line, I had stored them in an Access table which I then linked to
>my Image/SQl table - well it would be time for me to take a coffee break
>(and maybe lunch also).
>
>Is there a way to force the query to take a more efficient path to the
>Image/SQL data? Or are there any tricks or configuration items I'm
>missing?
>
>Eric Popish
>Directors Guild of America
>[log in to unmask]
>

The answer is that if MS Access 2.0 is NOT aware of a unique key it will
suck in the entire contents of the external (ImageSQL) table and do the
filter and join on the PC! This is not a clever idea.

If MS Access is aware of a UNIQUE index then it will build a copy of this
index internally and then used parameterised queries locally.

If there is a filter on the external table then the index created will be a
subset.

So for optimum performance, nominate a unique key that includes image
chains and apply a filter upon the external (and if possible make that
filter on a chain). TPIs work as well.

To gain these benefits you really need ALLBASE G1.xx.

<plug>
The LINKWAY ODBC driver supports the nomination od pseudo unique indeces
through its dictionary facility!
<gulp>


Ken Vickers

Manager LINKWAY Development and Support
Computing Solutions Limited

TEL +44 (0) 1905 794400
FAX +44 (0) 1905 794464