QODBC is a very fast ODBC driver. The QuickBooks interface SDK, which uses XML, is not the speediest file system available, but it does the job. The QODBC driver works with all front-end applications which can access ODBC, some of which are faster than others.

This question is almost never asked by our users using C as a front end language for ODBC, nor by report writer users with Crystal, or DBMS users with Delphi. These users will see immediately how fast the driver is because their applications are optimized for ODBC speed.

It is almost always asked by users of Microsoft Access, whose first operation is to open the largest file they have in their QuickBooks system in an Access Grid control and read it all into memory. They notice that the grid comes up quickly, but if they page down to the next screen-full of records, it takes a while. Why Access is slow compared to other systems can be easily explained.

Access is overloaded with multi-user functionality, meaning that features like updating all stations when a record changes on-line and other features are easily accomplished by the native Access file format.

When Access uses ODBC however, it attempts to do the same thing. For instance, when you open a multi-user grid on an ODBC data file, it must read the entire file into memory before continuing. This continues in the background, and when you press page-down, Access waits until the file has been completely read before beginning another query of the entire file, starting with the last record of the previous screen.

Other DBMS systems optimized for ODBC, such as Delphi, query only the number of records needed to fill the current screen with data, and does not continually refresh the database on the screen for no reason.

Access only has this problem in Grid controls, which is unfortunately the first thing people use with our driver. However, if you create a query and send it as input to a report, you will see how fast our driver can run under Access.

When programming with SQL (the transport language of ODBC drivers) it is a good idea to think of an SQL table as being a "cloud" of data with potentially unlimited records. You should therefore design queries and views of only the fields and records required for the current transaction or report, and not attempt to query all records in a given table, which may overload the system.

Microsoft Access is optimized to work with its built-in data manager only (MDB files), and any Access programmer who has used ODBC will tell you about the limitations Access has in this area. Other packages work very well with ODBC data drivers.

If you would like to see what Access is doing that makes it appear to "lock up" while running a query, you may like to turn on "Display Driver Status". It is a check box on the QODBC Setup Screen, Messages screen.

Keep in mind that QODBC is not a database tool, but rather a translation tool. QuickBooks is a non-normalized flat file system which has no indexes available to us and will not perform like SQL Server or dBase files. Every transaction you request must be translated and communicated to QuickBooks via large complicated XML transactions.

Try and keep your result set as small as possible to get a feel for the system, carefully design and test any multi-file joins, and keep the number of returned fields to a minimum for maximum performance.

Our main goal is to make it easier to access QuickBooks data in a standardised database-like fashion, but queries must be optimized to perform as fast as possible.

Also, try and use ranges of dates on TxnDate, TxnDateMacro and TimeModified as much as possible to narrow down the data to the smallest possible segment. For example, make something similar to this the first part of your WHERE clause:

Invoice.TimeModified >= {ts'2003-09-01 17:01:09'} AND
Invoice.TimeModified <= {ts'2003-09-02 17:01:09'}

We also suggest you use reports whenever possible for maximum performance, which also removes the need to learn all about the internal structure of QuickBooks data. See Reports in the schema at http://www.qodbc.com/data.