Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


[Home] [Table of Contents] [Previous Section] [Next Section]

Breck Carter
Last modified: February 12, 1998
mail to: bcarter@bcarter.com


Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques


7 - Use A Log File

The use of a transaction log can actually improve insert, update and delete performance over a database that doesn't have a log file defined. That's because having a log means SQL Anywhere doesn't have to do a checkpoint every time your program does a commit. Checkpoints are when your updates are finally written to disk; they are necessary but time consuming events, and with a transaction log in use they don't have to be done as often.

Figure 7A shows how to run the DBLOG utility to turn transaction logging on and off, and Figure 7B shows a PowerBuilder script that was used to perform 100 UPDATE and COMMIT statements with and without a transaction log defined. The difference was dramatic: More than 10 seconds without a log, less than one second with a log.

Figure 7A - DBLOG Turns Transaction Logging On And Off 
To associate a transaction log with a database:


   DBLOG -t c:\test\tiny.log c:\test\tiny.db


To disassociate a transaction log from a database:


   DBLOG -n c:\test\tiny.db

Figure 7B - PowerBuilder UPDATE And COMMIT Script 
integer ll_primary1


FOR ll_primary1 = 1 TO 100



   UPDATE test1

      SET search1 = search1 + 1

    WHERE primary1 = :ll_primary1

    USING SQLCA;

   IF SQLCA.SQLCode <> 0 THEN

      MessageBox ( "Error", "Update failed" )

   END IF



   COMMIT USING SQLCA;

   IF SQLCA.SQLCode <> 0 THEN

      MessageBox ( "Error", "Commit failed" )

   END IF



NEXT

If you're deploying an application with the SQL Anywhere Desktop Runtime System (RTDSK50.EXE) you should keep in mind that it does not allow transaction logs so there may be performance problems with heavy updates.

[Home] [Table of Contents] [Previous Section] [Next Section]