|Time and time again I hear people complaining about the speed of their
application and windows taking 5 minutes to open. Whilst PowerBuilder is not the fastest
product for script execution it certainly does not deserve all of the blame. Frequently
the problem lies with the database access and how you are retrieving data. This can be
caused by a number of problems such as table scans and bad design of composite indexes,
but how do you find out if your database is the cause of your performance problems?
depending on your version of PowerBuilder there are a number of built in tools you can use
to determine where all the time is being eaten up:
Pre PowerBuilder 6:
Before PowerBuilder 6 your main tool was the use of the SQL Trace feature. This
feature writes a log file of all database access for a single transaction object inside
your application. To enable this feature you just need to alter the DBMS field of the
transaction object before you connect to the database. Append the string 'trace ' to the
front of your DBMS, for example:
SQLCA.DBMS = "Or7"
is replaced with
SQLCA.DBMS = "trace Or7"
PowerBuilder will create a dbtrace.log file in your windows directory. This file
contains every piece of SQL executed by against the transaction object, complete with
substituted variables, number of rows returned and the length of time it took to
execute the SQL.
The SQL Trace feature is still available however you should check out a new feature of
PowerBuilder 6 the tracing and profiling function. This feature enables you to run your
application and produce a log file of how long every line of script took to execute. This
includes SQL statements. You can enable the tracing features either at run time or in the
development environment. My preference is to run the application in development to remove
any speed problems before the user sees them.
From the system options dialog, on the profiling tab check the Enable Tracing feature
and make sure the Embedded SQL option is checked. You can analyse the resulting log file
using the new profiling analysis tool shipped with PowerBuilder.