Schema Caching
Home
afs:TRADE
Services
Support
About AFS

Schema Caching

Q:  When I review the SQL that afstrade is emitting, I see many lines like:

select c.name, t.type, c.length, c.status, t.name, c.prec, c.scale from syscolumns c, systypes t where c.id = object_id ('dbo.tckdtl') and c.usertype = t.usertype order by colid ASC 

What are these queries?  Is there any way to get rid of, or reduce the number of, them.

A: afstrade uses a layer known as the BDE to provide database independence.  The BDE looks up the structure of tables before they are used.  The lines you see like those above are the BDE getting the structure of the database.

If your database does not change often, you can reduce the number of these calls by using Schema Caching.  To do this:

  • run the BDE Administrator
  • select the Configuration tab
  • Click on the + to the left of Drivers
  • Click on the + next to Native
  • Select your driver (e.g. Sybase)
  • In the right side, set the following
    • ENABLE SCHEMA CACHE = TRUE
    • SCHEMA CACHE DIR = an existing, local directory.
    • SCHEMA CACHE SIZE = 32

As a result, once the BDE fetches a structure, it caches that structure and does not look it up again, reducing the calls like the one above.

If you use schema caching, you need to make sure you clear the SCHEMA CACHE DIR any time a change is made to the database.  You could clear this directory daily, and still see an improvement in performance.

Last updated: 04/28/05

Back Up Next