Knowledgebase
[QODBC-Desktop] Slow Performance of QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

QODBC Performance Instruction

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

Why is the driver so slow sometimes?

      This question is rarely 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.

       UserWhy Access is slow compared to other systems can be easily explained. s almost always ask it of Microsoft Access, whose first operation is to open the most extensive 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.

The Explanation of QODBC Slow Performance with Access

      Access is overloaded with multi-user functionality, meaning that features like updating all stations when the native Access file format easily accomplishes the record changes online and other features.

      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 do 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, you will see how fast our driver can run under Access if you create a query and send it as input to a report.

      When programming with SQL (the transport language of ODBC drivers), it is a good idea to think of an SQL table as 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 want 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 checkbox on the QODBC Setup Screen -> Messages tab screen.

How to make QODBC run faster

      Remember that QODBC is not a database but rather a translation tool. QuickBooks is a non-normalized flat file system that 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 via large, complicated XML transactions to QuickBooks.

      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 standardized 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 in 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 suggest you use reports whenever possible for maximum performance, which removes the need to learn all about the internal structure of QuickBooks data. See Reports in the schema at http://www.qodbc.com/data.

(181 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).