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.