I am trying to find a performance issue in a system and I am using 2 tools just for comparison and verification. One being FB TraceManager (FBTM) and the other is an old util called

Interbase Planalyzer (IP)

It seems that the particular query from the view TRACKED_ITEM_LOCATION_DETAIL is slow.

From FBTM,while monitoring the app, I get the following raw output:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2015-08-11T16:35:52.4990 (6180:0000000000F9A948) EXECUTE_STATEMENT_FINISH

    D:\PROJECTS\KKI\TECH\DATABASE\PRODUCTION DATABASES\2015-07-14\DIGITAN.KKI.FDB (ATT_34, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)

        (TRA_20898, READ_COMMITTED | NO_REC_VERSION | WAIT | READ_WRITE)

Statement 3407:

-------------------------------------------------------------------------------

select * from TRACKED_ITEM_LOCATION_DETAIL where ITEM_ID = ? order by ID

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

PLAN JOIN (JOIN (JOIN (TRACKED_ITEM_LOCATION_DETAIL TILD ORDER TILD_PK_IDX INDEX (TILD_ITEM_IDX), TRACKED_ITEM_LOCATION_DETAIL TL INDEX (TL_PK_IDX)), TRACKED_ITEM_LOCATION_DETAIL FTL INDEX (TL_PK_IDX)), TRACKED_ITEM_LOCATION_DETAIL USR INDEX (USR_PK_IDX))

param0 = bigint, "2147191655"

1 records fetched

    112 ms, 3316 read(s), 3325 fetch(es)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge

***************************************************************************************************************

USER_                                             1

TRACKING_LOCATION_                                1

TRACKED_ITEM_LOCATION_DETAIL_                     1

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

3325 fetches and  112ms seem way over the top. The cache hit ratio is 0%.

However, when I use IP and manually enter the same SQL, I get the following:

Prepare time 1ms

Execution time 148ms

Fetch time 4ms

With a total of 7 fetches. Not sure where FBTM gets 3K fetches?

Also, if I remove the order by, IP reports a drastic reduction is execution time i.e. down to 6msec. Does the ORDER BY on the PK make such a difference?

I am not understanding what is going on.

Either way, here are the table & view info & stats

Thanks

Louis van Alphen

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE TRACKED_ITEM_LOCATION_DETAIL_ (

    UID               DOM_UID /* DOM_UID = VARCHAR(36) */,

    ID                DOM_ID /* DOM_ID = BIGINT NOT NULL */,

    IS_DELETED        DOM_BINARY /* DOM_BINARY = SMALLINT DEFAULT 0 NOT NULL CHECK ((  value in ( 0,1) )) */,

    CREATED_DTM       DOM_DTM default current_timestamp /* DOM_DTM = TIMESTAMP */,

    CREATED_USER_ID   DOM_FK /* DOM_FK = BIGINT */,

    ROW_ORIGIN        DOM_FK /* DOM_FK = BIGINT */,

    ITEM_ID           DOM_FK NOT NULL /* DOM_FK = BIGINT */,

    LOCATION_ID       DOM_FK NOT NULL /* DOM_FK = BIGINT */,

    FROM_LOCATION_ID  DOM_FK /* DOM_FK = BIGINT */,

    START_DTM         DOM_DTM NOT NULL /* DOM_DTM = TIMESTAMP */,

    END_DTM           DOM_DTM /* DOM_DTM = TIMESTAMP */,

    START_DAT         DOM_DAT NOT NULL /* DOM_DAT = DATE */,

    END_DAT           DOM_DAT /* DOM_DAT = DATE */

);

ALTER TABLE TRACKED_ITEM_LOCATION_DETAIL_ ADD CONSTRAINT TILD_PK PRIMARY KEY (ID)

USING INDEX TILD_PK_IDX;

CREATE INDEX TILD_END_DAT_IDX ON TRACKED_ITEM_LOCATION_DETAIL_ (END_DAT);

CREATE INDEX TILD_ITEM_IDX ON TRACKED_ITEM_LOCATION_DETAIL_ (ITEM_ID);

CREATE INDEX TILD_LOCATION_IDX ON TRACKED_ITEM_LOCATION_DETAIL_ (LOCATION_ID);

CREATE INDEX TILD_START_DAT_IDX ON TRACKED_ITEM_LOCATION_DETAIL_ (START_DAT);

CREATE OR ALTER VIEW TRACKED_ITEM_LOCATION_DETAIL(

    UID,

    ID,

    IS_DELETED,

    CREATED_DTM,

    CREATED_USER_ID,

    ROW_ORIGIN,

    ITEM_ID,

    LOCATION_ID,

    FROM_LOCATION_ID,

    START_DTM,

    END_DTM,

    START_DAT,

    END_DAT,

    LOCATION_NAME,

    FROM_LOCATION_NAME,

    USER_FULL_NAME)

AS

select

TILD.*,

TL.NAME as LOCATION_NAME,

FTL.NAME as FROM_LOCATION_NAME,

USR.FIRST_NAME || ' ' || USR.LAST_NAME as USER_FULL_NAME

from TRACKED_ITEM_LOCATION_DETAIL_ TILD

left outer join TRACKING_LOCATION_ TL on TL.ID = TILD.LOCATION_ID

left outer join TRACKING_LOCATION_ FTL on FTL.ID = TILD.FROM_LOCATION_ID

left outer join USER_ USR on USR.ID = TILD.CREATED_USER_ID