Recent

Author Topic: TIBXScript ShowPerformanceStat, ShowEffectedRows ....where?  (Read 1236 times)

RedOctober

  • Full Member
  • ***
  • Posts: 219
TIBXScript ShowPerformanceStat, ShowEffectedRows ....where?
« on: March 10, 2018, 12:23:11 am »
I have both of these checked (true) and I have a procedure assigned for all the logging events (OnErrorLog, OnOutputLog, OnProgressLog, OnSelectSQL, OnSetStatement).  Yet the performance stats and effected rows is never provided. What am I doing wrong?

Platform:  Lazarus 1.8.1, FPC 3.0.5, IBX 2.2.0


tonyw

  • Full Member
  • ***
  • Posts: 124
    • MWA Software
Re: TIBXScript ShowPerformanceStat, ShowEffectedRows ....where?
« Reply #1 on: March 10, 2018, 12:42:18 pm »
Have you also added a "Data Output Formatter" component to your project and linked it to the TIBXScript?

Data Output Formatters are available for "Insert format", table formats and CSV format. Performance stats are also generated by the Data Output Formatter for select queries. TIBXScript is only responsible for Insert/Update/Delete query stats. See section 7.2 of the IBX User Guide.

RedOctober

  • Full Member
  • ***
  • Posts: 219
Re: TIBXScript ShowPerformanceStat, ShowEffectedRows ....where?
« Reply #2 on: March 10, 2018, 10:45:12 pm »
Ok, I'm confused, and not using the components correctly.  Can you have a look at what I am doing wrong in the attached demo?

tonyw

  • Full Member
  • ***
  • Posts: 124
    • MWA Software
Re: TIBXScript ShowPerformanceStat, ShowEffectedRows ....where?
« Reply #3 on: March 11, 2018, 12:24:50 am »
Ok, I'm confused, and not using the components correctly.  Can you have a look at what I am doing wrong in the attached demo?
OK, the simple answer is that because you have set an OnSelectSQL handler, the DataOutputFormatter never gets called. When TIBXScript encounters a select statement, it will either: call the OnSelectSQL handler if present, or call the DataOutputFormatter, if present, or otherwise throw an exception.

You have used a TIBDataset to show the results of the select query which is fine. However, you won't get any statistics from the DataOutputFormatter as it's never called.

TIBDataset never has had any direct way to get the performance statistics and I am not sure how meaningful they will be given that it doesn't necessarily fetch all records as once - unlike a DataOutputFormatter. However, there is a way of getting them:

TIBDatabase has a public property SelectStmtHandle: IStatement. This is the low level API handle. You can feed this to the class procedure TIBCustomDataOutput.ShowPerfStats(Statement: IStatement; Add2Log: TAdd2Log); in the unit IBDataOutput to format the current statistics and write them to the log. However, as dsiders has been pointing out to me, while this procedure works fine on Linux there seems to be a platform incompatibility with Windows.

This is a bug waiting to be fixed. In the meantime, if on Windows, you might fined it easier to just copy the code from this class procedure and replace all "%f" formatting codes with %u.

RedOctober

  • Full Member
  • ***
  • Posts: 219
Re: TIBXScript ShowPerformanceStat, ShowEffectedRows ....where?
« Reply #4 on: March 11, 2018, 02:27:33 am »
Hi tonyw,

Sounds complicated, and, I'll be replacing all that with your newly architected version of IBX anyway.  I'll wait for the new version.

My platform is:  Lazarus 1.8.1, FPC 3.0.5, IBX 2.2.0, on Windows Server Essentials 2016.

My goal is to build a bare-bones replacement for FlameRobin, since FlameRobin doesn't work on Windows Server Essentials 2016, and the build instructions don't work.  I'd be interested in paying for a minimalist database management utility like FlameRobin.  FlameRobin always gave the plan and performance stats, which I depended upon to tune my SQL statements.

I don't want to pay the $CDN 600 for a "generalist" tool.  I'd pay $CDN 30  ($US 20) for a minimalist Firebird specialized tool to replace FlameRobin.