When using an ODBC-enabled application to access a Synergy database, keep the following in mind:
- Don’t rely on automatically created queries. These queries are seldom the most efficient way to access a database for a given situation. Instead, create your own queries that adhere to the guidelines listed in Creating efficient SQL statements.
- If you use an application, such as Microsoft Access, where selecting a table opens the entire table in a grid, use pass-through queries to update. If you update a field by selecting it in the grid, the application will update every field in the row including fields whose values haven’t changed. xfODBC does not support updates of this kind; they may cause an error.
- When using Microsoft Access to access Synergy data, consider whether you should import the database or link to the database. Depending on the query, the size of your database, requirements for sharing data, and the client’s resources, one method may be better than another. For databases that rarely change (for example, databases consisting of ZIP codes, product codes, etc.), importing may improve performance. However, if you are modifying the database or need to see the most current picture of the database, you must use a link.
Optimizing with pass-through queries
One way to optimize performance when using Microsoft Access is to use pass-through queries, which are queries that are passed directly to the database server. If you use a pass-through query, the database server does all the processing and returns only the result of the query. This bypasses the Microsoft Jet database engine. The reason this can improve performance is that Jet is key-based. So, for example, if you have the SQL statement “SELECT * FROM part”, Jet will first read all the keys; then it will perform a series of sequential statements like the following:
SELECT * FROM part WHERE part.number = <key>
For simple queries, this can result in many duplicate reads, making a pass-through query the more efficient alternative.
