User's Guide

Part III. Using SQL Anywhere

Chapter 21. Monitoring and Improving Performance

There are many factors that can affect database performance. This chapter describes ways of improving performance from a SQL perspective, and assumes a reasonably designed database running with sufficient resources. Disk and file management, as well as hardware configuration, also play an important part in determining performance. For example, the following can lead to poor performance:

Factors leading to poor database performance

• You are not running with a transaction log (see "The transaction log"). A transaction log improves commit time for transactions that insert, update, or delete rows.
• You are using the 16-bit version of the Windows 3.x database engine. The 32-bit version has better performance, especially for larger databases.
• You are loading huge amounts of data into a database. See "Tuning bulk operations" for methods to improve performance.
• The database engine does not have an adequate amount of memory for caching database pages. See "The database engine" for command line options for controlling the cache size. Extra memory for your computer could improve database performance dramatically.
• Your hard disk is excessively fragmented. This becomes more important as your database increases in size. The DOS and Windows database engines cannot do direct (fast) reading and writing when the database file is very fragmented. There are several utilities available for DOS and Windows to defragment your hard disk. One of these should be run periodically. You could put the database on a DOS disk partition by itself to eliminate fragmentation problems.
• You are using a small page size for a large database. The page size is determined when the database is created by the initialization utility. You can find out the page size by using DBINFO. To change page size, you need to unload and reload your database.
• The database table design is not good. A bad database design can result in time-consuming queries to get information from the database. If indexes will not solve your performance problem, consider alternative database designs.
• Your hard disk is slow. A faster hard disk, a caching disk controller or a disk array can improve performance considerably.
• In a multiuser environment, your network performance is slow.
• You are fetching or inserting many rows of data. Consider using the multi-row operations.
  • For more information, see "FETCH statement" and "PUT statement".