Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

Access Path Usage

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Access Path Usage

    I have set up a linked server in Microsoft SQL Server to a Client Access ODBC Connection. If I perform an SQL statement on a large file (60,000 records) selecting only a single product# it takes 35 seconds to find this product using the physical file. It actually takes a little longer to perform the same SQL statement using a logical file keyed by product#. Does anyone know how to take advantage of logical file access paths on ODBC seraches?

  • #2
    Access Path Usage

    Steve, That seems like a long time. You might try running the same query in an interactive session with debug turned on. Next, look at your low level messages. You will likely see some suggestions about access paths used by the query. At 35 seconds, I would guess SQL is not able to use your existing access path. If you have operations navigator installed and are at a real current V4R5 or on V5R1, you can try running the explain plan option. It will give even more information than debug. You access that option from the database Run SQL Scripts option. David Morris

    Comment


    • #3
      Access Path Usage

      Steve, Are you using a parameterized statement? Utilizing this technique allows the SQL statement to be pre-compiled in the appropriate SQL Package for retrieval; otherwise the SQL engine has to do the access path search every time you run the request. Bill > I have set up a linked server in Microsoft SQL Server to a Client Access ODBC Connection. If I perform an SQL statement on a large file (60,000 records) selecting only a single product# it takes 35 seconds to find this product using the physical file. It actually takes a little longer to perform the same SQL statement using a logical file keyed by product#. Does anyone know how to take advantage of logical file access paths on ODBC seraches?

      Comment

      Working...
      X