Brute force pbtrace.log file processing

Every had to search through a large PBTRACE.LOG file to find the problematic SQL statement that is causing your application to run slow? If you've moved to PowerBuilder 10.2 it's less of an issue because of the enhancements to database tracing they've added to that version. However, if you're still using anything earlier, it can be a pain to find the statement in a large log file.

So here's a brute force method of handling it. Import the pbtrace.log file into Excel, and then insert three columns before the data. In the first column, add the following formula in the first cell and then copy it for each row of data:

=IF(ISERROR(SEARCH("MilliSeconds",D1)),0,SEARCH("MilliSeconds",D1))

That forumla determines if there is timing data for the particular row in the pbtrace.log file.

In the second column, add the following forumla in the first cell of the second column and then copy it for each row of data:

=IF(A1>0,SEARCH("(",D1,A1-9),0)

That forumla determines where the beginning of the numeric value for the timing information begins.

Finally, add the following formula in the first cell of the third column and then copy it for each row of data:

=IF(A1>0,VALUE(MID(D1,B1+1,A1-B1-2)),0)

That forumula strips out the numeric value from the timing data.

You should now be able to perform a descending sort on the third column to determine which SQL statements are the problematic ones.