Hi everyone.

 

I am experiencing an unusual situation.

 

I have a log table for tracking user changes to records.  Table is as follows:

 

CREATE TABLE AIMLOG

(

  ID Integer NOT NULL,

  USERNAME Char(12) COLLATE EN_UK,

  USERNO Integer,

  LOGDATE Integer,

  LOGTIME Integer,

  LOGTABLE Char(30) COLLATE EN_UK,

  RECORDKEY Char(30) COLLATE EN_UK,

  RECORDACTION Char(10) COLLATE EN_UK,

  RECORDBEFORE Varchar(1000) COLLATE EN_UK,

  RECORDAFTER Varchar(1000) COLLATE EN_UK,

  CONSTRAINT AIMLOG_PK PRIMARY KEY (ID)

);

 

Using Flamerobin, if I want to find the last record I run the following query:

 

SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE, a.LOGTIME, a.LOGTABLE, a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE, a.RECORDAFTER

FROM AIMLOG a

order by a.id descending

 

The query takes about 15 seconds because it doesn’t want to use the primary key, AIMLOG_PK.  Plan below:

 

Starting transaction...

Preparing query: SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE, a.LOGTIME, a.LOGTABLE, a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE, a.RECORDAFTER

FROM AIMLOG a

order by a.id descending

 

Prepare time: 0.015s

Field #01: AIMLOG.ID Alias:ID Type:INTEGER

Field #02: AIMLOG.USERNAME Alias:USERNAME Type:STRING(12)

Field #03: AIMLOG.USERNO Alias:USERNO Type:INTEGER

Field #04: AIMLOG.LOGDATE Alias:LOGDATE Type:INTEGER

Field #05: AIMLOG.LOGTIME Alias:LOGTIME Type:INTEGER

Field #06: AIMLOG.LOGTABLE Alias:LOGTABLE Type:STRING(30)

Field #07: AIMLOG.RECORDKEY Alias:RECORDKEY Type:STRING(30)

Field #08: AIMLOG.RECORDACTION Alias:RECORDACTION Type:STRING(10)

Field #09: AIMLOG.RECORDBEFORE Alias:RECORDBEFORE Type:STRING(1000)

Field #10: AIMLOG.RECORDAFTER Alias:RECORDAFTER Type:STRING(1000)

PLAN SORT ((A NATURAL))

 

 

Executing...

Done.

267615 fetches, 0 marks, 3818 reads, 0 writes.

0 inserts, 0 updates, 0 deletes, 0 index, 129986 seq.

Delta memory: 9460264 bytes.

Total execution time: 14.734s

Script execution finished.

 

Another simple query that should use the index (I would assume) but doesn’t is:

 

select max(a.id) from aimlog a

 

which uses the plan:

 

Starting transaction...

Preparing query: select max(a.id) from aimlog a

 

Prepare time: 0.015s

Field #01: .MAX Alias:MAX Type:INTEGER

PLAN (A NATURAL)

 

 

Executing...

Done.

267615 fetches, 0 marks, 3821 reads, 0 writes.

0 inserts, 0 updates, 0 deletes, 0 index, 129986 seq.

Delta memory: 9724 bytes.

Total execution time: 0.172s

Script execution finished.

 

 

The application we develop in, Clarion, uses a mechanism of SET the records in a key order (usually placing the cursor at the bottom or top of the order, in this case top), then PREVIOUS to find the highest record, then adding 1 to get the next ID.  (Don’t question the method, it works in the IDE we have never had an issue), however when the application does it’s SET/PREVIOUS it takes 10 to 15 seconds to complete.  I can only gather that it is doing a NATURAL read as above instead of using the index.

 

We don’t seem to have the issue (as far as I can tell) with other tables that use the SET/PREVIOUS mechanism.

 

Is it a property of the ‘order by’ to not use the index, is it a bug, is it a fault in my table definition or am I missing the point?

 

Firebird 2.5.7   (WI-2.5.7.27050) 

 

Just realised I ‘should’ be using 2.5.8, but on a server running WI-V2.5.8.27089 it still uses NATURAL.

 

Any pointers or ideas? 

 

Thanks.

 

Andrew Zenz