Recent

Author Topic: How do I get the PLAN from Firebird via IBX components?  (Read 4619 times)

RedOctober

  • Sr. Member
  • ****
  • Posts: 452
How do I get the PLAN from Firebird via IBX components?
« on: April 24, 2018, 04:19:09 pm »
I'm trying o emulate the display of stats that FlameRobin has when executing a SELECT SQL statement. Included in those stats is the PLAN that Firebird is using when it executes the SQL.
I'm currently using the components:

TIBXScript -> TIBDataSet -> TDataSource -> TDBGrid

Platform: Microsoft Windows Server Essentials 2016, Lazarus 1.8.2, FPC 3.0.4, IBX 2.3.0 (2018-Apr-16)

I can get all the other stats, (reads, writes, etc.) it's just that I don't know how to get the PLAN.  Thanks in advance.

dsiders

  • Hero Member
  • *****
  • Posts: 1052
Re: How do I get the PLAN from Firebird via IBX components?
« Reply #1 on: April 24, 2018, 05:59:02 pm »
I'm trying o emulate the display of stats that FlameRobin has when executing a SELECT SQL statement. Included in those stats is the PLAN that Firebird is using when it executes the SQL.
I'm currently using the components:

TIBXScript -> TIBDataSet -> TDataSource -> TDBGrid

Platform: Microsoft Windows Server Essentials 2016, Lazarus 1.8.2, FPC 3.0.4, IBX 2.3.0 (2018-Apr-16)

I can get all the other stats, (reads, writes, etc.) it's just that I don't know how to get the PLAN.  Thanks in advance.

The TIBSQL.Plan property is available. It doesn't handle exec stored procedure statements currently. It does handle SELECT, UPDATE, and DELETE statements.
Preview Lazarus 3.99 documentation at: https://dsiders.gitlab.io/lazdocsnext

RedOctober

  • Sr. Member
  • ****
  • Posts: 452
Re: How do I get the PLAN from Firebird via IBX components?
« Reply #2 on: April 24, 2018, 10:18:01 pm »
Hi dsiders. I must be doing something wrong.  I cannot get the .Plan to show anything about my SELECT SQL statement.  Here is what I have tried:  (memPlan is a TMemo)

  sql.Close;
  sql.SQL.Text := 'SELECT * FROM EMPLOYEE';
  sql.Prepare;
  memPlan.Text := sql.Plan;   
Result: sql.Plan is blank

 sql.Close;
  sql.SQL.Text := 'SELECT * FROM EMPLOYEE';
  sql.Prepare;
  sql.ExecQuery;
  memPlan.Text := sql.Plan;   
Result: sql.Plan is blank

 sql.Close;
  sql.SQL.Text := 'SELECT * FROM EMPLOYEE';
  sql.Prepare;
  sql.Open;
  memPlan.Text := sql.Plan;   
Result: sql.Plan is blank

There are records returned, I have check sql.Recordcount.

I just want the PLAN that is going to be used, and if I can get it without actually executing the TIBSQL, then that wd be preferable.





dsiders

  • Hero Member
  • *****
  • Posts: 1052
Re: How do I get the PLAN from Firebird via IBX components?
« Reply #3 on: April 24, 2018, 11:06:51 pm »
I must be doing something wrong.  I cannot get the .Plan to show anything about my SELECT SQL statement.  Here is what I have tried:  (memPlan is a TMemo)

  sql.Close;
  sql.SQL.Text := 'SELECT * FROM EMPLOYEE';
  sql.Prepare;
  memPlan.Text := sql.Plan;   
Result: sql.Plan is blank

 sql.Close;
  sql.SQL.Text := 'SELECT * FROM EMPLOYEE';
  sql.Prepare;
  sql.ExecQuery;
  memPlan.Text := sql.Plan;   
Result: sql.Plan is blank

 sql.Close;
  sql.SQL.Text := 'SELECT * FROM EMPLOYEE';
  sql.Prepare;
  sql.Open;
  memPlan.Text := sql.Plan;   
Result: sql.Plan is blank

There are records returned, I have check sql.Recordcount.

I just want the PLAN that is going to be used, and if I can get it without actually executing the TIBSQL, then that wd be preferable.

Does including SET PLAN or SET EXPLAIN in the script help? Like:

  sql.SQL.Text := 'set plan on; SELECT * FROM EMPLOYEE; set plan off;';

I know this is the only way to get the info in isql.exe. Or SET PLANONLY when you want the plan and not the data.




Preview Lazarus 3.99 documentation at: https://dsiders.gitlab.io/lazdocsnext

RedOctober

  • Sr. Member
  • ****
  • Posts: 452
Re: How do I get the PLAN from Firebird via IBX components?
« Reply #4 on: April 25, 2018, 12:08:06 am »
IBXScript chokes on "SET PLAN ON;"

(specifically the word PLAN)

Seems that command is available in ISQL only.   I know it's possible to get because FlameRobin displays the PLAN on each SELECT query.

Still stuck.

RedOctober

  • Sr. Member
  • ****
  • Posts: 452
Re: How do I get the PLAN from Firebird via IBX components?
« Reply #5 on: April 25, 2018, 12:17:54 am »
The TIBXScript has a "OnSetStatement" event, but I don't know how to use it, and can't figure it out.

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: How do I get the PLAN from Firebird via IBX components?
« Reply #6 on: April 25, 2018, 01:04:48 am »
The SET PLAN command is handled by the DataOutputFormatter. If you are getting an error on a set plan command then you have probably forgotten to assign one to the TIBXSript.

The OnSetStatement handler is called to process a SET statement that is not otherwise handled by the TIBXScript.

RedOctober

  • Sr. Member
  • ****
  • Posts: 452
Re: How do I get the PLAN from Firebird via IBX components?
« Reply #7 on: April 25, 2018, 01:39:22 am »
I have attached a demo project.  Can some one add what ever needs to be added, and adjust what ever needs to be adjusted so I can:

- Get the result set in the grid
- Get the PLAN in the Memo2
- Not execute the script twice
- Not exclude the Stats, which I am gathering in my production project (not shown in this demo)

Thanks.


RedOctober

  • Sr. Member
  • ****
  • Posts: 452
Re: How do I get the PLAN from Firebird via IBX components?
« Reply #8 on: April 26, 2018, 09:55:20 pm »
Here is an update to my last project.  The attached seems to show that there may be a bug in IBX in that it does not include the SQL PLAN when it's configured to do so.

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: How do I get the PLAN from Firebird via IBX components?
« Reply #9 on: April 27, 2018, 10:44:55 am »
OK, this looks like a bug and is due to a simple logic error in TIBXScript;  Line 988 of ibx/runtime/nongui/ibxscript.pas currently reads:

        if not Result and assigned(OnSetStatement) then
          OnSetStatement(self,command,param,stmt,Result)
        else
          raise Exception.CreateFmt(sInvalidSetStatement, [command,stmt]);

If should be changed to:

        if not Result then
        begin
          if assigned(OnSetStatement) then
            OnSetStatement(self,command,param,stmt,Result)
          else
            raise Exception.CreateFmt(sInvalidSetStatement, [command,stmt]);
        end;

RedOctober

  • Sr. Member
  • ****
  • Posts: 452
Re: How do I get the PLAN from Firebird via IBX components?
« Reply #10 on: April 27, 2018, 06:09:09 pm »
I made the requested changes at line 988 and I'm still getting blank Msg variable in the output log.
I was using either a TIBCSVDataOut or a TBlockDataOut and it's output log to try to get the plan.
 
procedure TfrmExecSQL.BLCKDataOutLog(const Msg: string; IsError: Boolean);
begin
  Writeln(txt_fyl, Msg);  // <-- Msg string is always blank.
end;

I tried issuing a SET PLAN ON; then a SET PLAN; before the SQL SELECT is executed. No difference.  No plan info comes back from the DataOut component(s). So I switched tactics to using an IBSQL component as recommended by dsiders.

Assuming an SQL statement of:

SELECT FIRST 300 SKIP 0
  t2.DEPT_NO, t2.DEPARTMENT, t2.LOCATION, t2.PHONE_NO, t3.LAST_NAME
FROM
  DEPARTMENT t2
  LEFT JOIN EMPLOYEE t3 ON t3.DEPT_NO = t2.DEPT_NO
WHERE
  t2.LOCATION STARTING WITH 'M'
ORDER BY t3.PHONE_EXT

 I do get a plan now, like this:

  Memo2.Lines.Clear;
  IBSQL1.Close;
  IBSQL1.SQL := IBQuery1.SQL;
  IBSQL1.Prepare;
  Memo2.Lines.Add(IBSQL1.Plan);
 
The plan appears in Memo2, and it looks like this:

Select Expression
    -> First N Records
        -> Skip N Records
            -> Sort (record length: 150, key length: 28)
                -> Filter
                    -> Nested Loop Join (outer)
                        -> Filter
                            -> Table "DEPARTMENT" as "T2" Full Scan
                        -> Filter
                            -> Table "EMPLOYEE" as "T3" Access By ID
                                -> Bitmap
                                    -> Index "RDB$FOREIGN8" Range Scan (full match)

My problem is now solved, using TIBSQL instead of a DataOut component.  If you were following just to help me out, thank you.

The following is only for those who would like to compare FlameRobin's PLAN output to TIBX component's PLAN output.


This is very detailed, but what I really need to know is if I am missing an index.  I purposely made an SQL that ORDERs BY in a way that there is no index.  When I used FlameRobin, I would look for the word "NATURAL" in the one line PLAN statement.  Then I'd look for a missing index, add it, then my SQL wd run way faster.  In this case, I made the final ORDER BY call for a non-indexed order, so it's my choice to add an index or not.  However, if I was doing a JOIN of some kind, on a non-indexed field, that is commonly used in my DB app, I'd for sure add an index.  But from the info in the PLAN above, I don't know if I'd be able to spot a missing index as quickly.

For the same SQL statement above, FlameRobin outputs this as the PLAN:

PLAN SORT (JOIN (T2 NATURAL, T1 INDEX (PK_JET_USR)))

I can easily see the word NATURAL, and it's "against" T2, so I can then quickly look at my JOINs and ORDER BY and see if an index can be added to help out.

The PLAN put out by IBX does show this difference also, but just in a different way.

Without an index on the LOCATION field, here is the PLAN output:

No Index on LOCATION:

Select Expression
    -> First N Records
        -> Skip N Records
            -> Sort (record length: 158, key length: 12)
                -> Filter
                    -> Nested Loop Join (outer)
                        -> Filter
                            -> Table "DEPARTMENT" as "T2" Full Scan
                        -> Filter
                            -> Table "EMPLOYEE" as "T3" Access By ID
                                -> Bitmap
                                    -> Index "RDB$FOREIGN8" Range Scan (full match)
                           
                           
With an index on Location (same SQL):

Select Expression
    -> First N Records
        -> Skip N Records
            -> Sort (record length: 158, key length: 12)
                -> Filter
                    -> Nested Loop Join (outer)
                        -> Filter
                            -> Table "DEPARTMENT" as "T2" Access By ID
                                -> Bitmap
                                    -> Index "IDX_DEPT_LOC" Range Scan (full match)
                        -> Filter
                            -> Table "EMPLOYEE" as "T3" Access By ID
                                -> Bitmap
                                    -> Index "RDB$FOREIGN8" Range Scan (full match)                           

Notice the node "Bitmap" under the "Table" nodes.  If it's missing (as in the first example), that indicates a NATURAL order.  What you want to see is a "Table" node followed by a "Bitmap" node.  Like this (as in the second example):

                            -> Table "DEPARTMENT" as "T2" Access By ID
                                -> Bitmap
                                    -> Index "IDX_DEPT_LOC" Range Scan (full match)

This lets me know that an index was used and my SQL is being executed efficiently.

Thanks to all who helped me out with this problem.


RedOctober

  • Sr. Member
  • ****
  • Posts: 452
Re: How do I get the PLAN from Firebird via IBX components?
« Reply #11 on: April 27, 2018, 07:52:50 pm »
I also just discovered that using IBX, FB 2.5x is not returning a PLAN whereas FB3x is.
This just added to my confusion as I was working between the two server versions.

 

TinyPortal © 2005-2018