User's Guide

Part III. Using SQL Anywhere

Chapter 23. Importing and Exporting Data

Loading large volumes of data into a database can be very time consuming. There are a few things you can do to save time.

• Run ISQL or the client application on the same machine as the database engine. Loading data over the network adds extra communication overhead. This might mean loading new data during off hours.
• Place data files on a separate physical disk drive from the database file. This could avoid excessive disk head movement during the load.
• In Windows 3.x, if your computer has a 386, 486, or higher processor, use the 32-bit database engine rather than the 16-bit database engine.
• Increase the size of the database cache. See the chapter "SQL Anywhere Components" for a description of the database engine command line option -c. Eliminate disk cache in favor of database cache if the machine is a dedicated SQL Anywhere server.
• Start the database engine or network server with the -b switch for bulk operations mode. In this mode, the database engine does not keep a rollback log or a transaction log, it does not perform an automatic COMMIT before data definition commands, and it does not lock any records. Without a rollback log, you cannot use savepoints and aborting a command always causes transactions to roll back. Without automatic COMMIT, a ROLLBACK undoes everything since the last explicit COMMIT. Without a transaction log, there is no log of the changes. You should back up the database file before and after using bulk operations mode because, in this mode, your database is not protected against media failure (see the chapter "Backup and Data Recovery").
• The network server allows only one connection when you use the -b switch.
• If you have data that requires many COMMITs, running with the -b option may slow down database operation. At each COMMIT, the engine carries out a checkpoint; this frequent checkpointing can slow down the database engine.
• Extend the size of the database file using ALTER DBSPACE (see the chapter "Watcom-SQL Language Reference"). This command allows a database file to be extended in large amounts before the space is required, rather than the normal 32 pages at a time when the space is needed. As well as improving performance for loading large amounts of data, it also serves to keep the database files more contiguous within the file system.