PBDR.COM

About   -   Contact   -   Purchase   -   Search   -   What's New

 
 
How To Find Database Bottle Necks
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?

Well 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.

PowerBuilder 6+
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.

 

Top of Page

Legal Notice

Ken Howe 2011