Simple Firebird query very slow

Question

I have a table with roughly 246k records. It has about 25 columns, all are integers except one small blob.

If I Query the table on all fields

select a.recordid, a.editcount, ect.. from ARTrans a

It executes in under a second. But if I include only the record id

select a.recordID from ARTrans a

It takes 20+ seconds to execute. Most of the time is spent planning(Natural) which seems weird since, in most cases, I have an index on recordid.

I've garbage collected, recreated indexes, removed indexes, added only one index on RecordID and it's still super slow.

Any help would be greatly appreciated.

Edited to give more information:

Firebird: 2.5.3.26778

fbclient.dll: 2.5.1.26351

There is no one else in the database, I've moved it local.

Here is the table def

CREATE TABLE ARTRANS
(
  RECORDID Integer NOT NULL,
  EDITCOUNT Smallint,
  CLASSIFICATION Smallint,
  TRANSID Integer,
  DATEENTERED Integer,
  CLIENTID Integer,
  TRANSTYPE Smallint,
  BILLED Smallint,
  FINALIZEID Smallint,
  INVOICEID Integer,
  INVOICENUM Integer,
  INVOICEDATE Integer,
  GROUPID Smallint,
  EXPORTED Char(1),
  TRANSVALUE Decimal(18,4),
  DESCRIPTION Blob sub_type 0,
  POSTPERIOD Smallint,
  LINKEDTRANSID Integer,
  LINKEDINVID Integer,
  LINKEDFUNDSID Integer,
  INFOONLY Smallint,
  NEEDTRANSFER Char(1),
  DESTTRANSID Integer,
  LSTTKREDIT Integer,
  SPELLNGRAMMARCHECKSTATUS Smallint
);

Index

CREATE UNIQUE INDEX IDX_ARTRANSRecID ON ARTRANS (RECORDID);

SQL statement:

SELECT a.RECORDID FROM ARTRANS a

Plan (output from flamerobin)

Preparing query: 
SELECT a.RECORDID 
FROM ARTRANS a
Prepare time: 20.008s
Field #01: ARTRANS.RECORDID Alias:RECORDID Type:INTEGER
PLAN (A NATURAL)


Executing...
Done.
13257 fetches, 0 marks, 76 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 6552 seq.
Delta memory: -19204 bytes.
Total execution time: 20.025s
Script execution finished.

This SQL statement works fine:

Preparing query: 
SELECT a.RECORDID, a.EDITCOUNT, a.CLASSIFICATION,
a.TRANSID, a.DATEENTERED, a.CLIENTID, a.TRANSTYPE, a.BILLED, a.FINALIZEID,
a.INVOICEID, a.INVOICENUM, a.INVOICEDATE, a.GROUPID, a.EXPORTED, 
a.TRANSVALUE, a.DESCRIPTION, a.POSTPERIOD, a.LINKEDTRANSID, a.LINKEDINVID, 
a.LINKEDFUNDSID, a.INFOONLY, a.NEEDTRANSFER, a.DESTTRANSID, a.LSTTKREDIT, 
a.SPELLNGRAMMARCHECKSTATUS, a.RDB$DB_KEY
FROM ARTRANS a

Prepare time: 0.013s
Field #01: ARTRANS.RECORDID Alias:RECORDID Type:INTEGER
Field #02: ARTRANS.EDITCOUNT Alias:EDITCOUNT Type:SMALLINT
Field #03: ARTRANS.CLASSIFICATION Alias:CLASSIFICATION Type:SMALLINT
Field #04: ARTRANS.TRANSID Alias:TRANSID Type:INTEGER
Field #05: ARTRANS.DATEENTERED Alias:DATEENTERED Type:INTEGER
Field #06: ARTRANS.CLIENTID Alias:CLIENTID Type:INTEGER
Field #07: ARTRANS.TRANSTYPE Alias:TRANSTYPE Type:SMALLINT
Field #08: ARTRANS.BILLED Alias:BILLED Type:SMALLINT
Field #09: ARTRANS.FINALIZEID Alias:FINALIZEID Type:SMALLINT
Field #10: ARTRANS.INVOICEID Alias:INVOICEID Type:INTEGER
Field #11: ARTRANS.INVOICENUM Alias:INVOICENUM Type:INTEGER
Field #12: ARTRANS.INVOICEDATE Alias:INVOICEDATE Type:INTEGER
Field #13: ARTRANS.GROUPID Alias:GROUPID Type:SMALLINT
Field #14: ARTRANS.EXPORTED Alias:EXPORTED Type:STRING(1)
Field #15: ARTRANS.TRANSVALUE Alias:TRANSVALUE Type:NUMERIC(18,4)
Field #16: ARTRANS.DESCRIPTION Alias:DESCRIPTION Type:BLOB SUB_TYPE 0
Field #17: ARTRANS.POSTPERIOD Alias:POSTPERIOD Type:SMALLINT
Field #18: ARTRANS.LINKEDTRANSID Alias:LINKEDTRANSID Type:INTEGER
Field #19: ARTRANS.LINKEDINVID Alias:LINKEDINVID Type:INTEGER
Field #20: ARTRANS.LINKEDFUNDSID Alias:LINKEDFUNDSID Type:INTEGER
Field #21: ARTRANS.INFOONLY Alias:INFOONLY Type:SMALLINT
Field #22: ARTRANS.NEEDTRANSFER Alias:NEEDTRANSFER Type:STRING(1)
Field #23: ARTRANS.DESTTRANSID Alias:DESTTRANSID Type:INTEGER
Field #24: ARTRANS.LSTTKREDIT Alias:LSTTKREDIT Type:INTEGER
Field #25: ARTRANS.SPELLNGRAMMARCHECKSTATUS Alias:SPELLNGRAMMARCHECKSTATUS Type:SMALLINT
Field #26: ARTRANS.DB_KEY Alias:DB_KEY Type:STRING(8)
PLAN (A NATURAL)


Executing...
Done.
1135 fetches, 0 marks, 7 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 560 seq.
Delta memory: 25852 bytes.
Total execution time: 0.047s
Script execution finished.

Also, I should add that there are 246804 records and it took almost a minute to get the count.

Preparing query: SELECT count(*) FROM ARTRANS a
Prepare time: 52.614s
Field #01: .COUNT Alias:COUNT Type:INTEGER
PLAN (A NATURAL)


Executing...
Done.
499643 fetches, 0 marks, 3016 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 246804 seq.
Delta memory: -18576 bytes.
Total execution time: 52.716s
Script execution finished.

Update

If I delete the blob column, performance returns. If I leave it in and NULL out all the values, performance is still fast. If I update the blob field on every record to contain a stream that's 20 bytes, performance goes back to 20+ seconds to perform the simple query

select a.RecordID from ARTrans a

I went even further and deleted all columns except for the blob field and the recordID and I'm still getting the slowness. Seems like this would have been seen before. Very weird.

Fornof

What version FB? Can you show us the exact queries? (I'm pretty sure your second query as written is an error referring to an alias you did not define.) How about the exact PLANs? – Lymphosarcoma

just added version, 2.5 – Fornof

What happens to performance if you add the magic RDB$DB_KEY (physical record position) to the slow query and remove it from the fast query? – Lymphosarcoma

I don't understand why selecting a single column would be slower. However, Firebird cannot fulfill queries only from index, it always has to read the row to check if it is visible to the current transaction. So natural is the only plan if you don't apply a condition. – Brink

I suggest you first upgrade to Firebird 2.5.5 and see if that fixes the problem. – Brink

It didn't speed up the recordID query to add the DB_KEY. As I tried different queries, any combination but the full query is slow. – Fornof

I've upgraded to 2.5.5 and the query is still slow. No improvement. – Fornof

Tried it in classic, super and super classic with the same results. – Fornof

Added an update at the end of the original post.. I'm not sure if I should be updating the question or sticking to using the comments... Do people that are watching the question get updates if I edit it? – Fornof

@Fornof I think this is sufficently strange to open a ticket with them tracker.firebirdsql.org/secure/Dashboard.jspa. Their 2.5.4 release notes has "Optimized handling for internal temporary blobs." so maybe something hasn't worked out there for your scenario firebirdsql.org/en/firebird-2-5-release-description – Abridge

Thank you for that link Alan. I've opened a ticket. – Fornof

Answer 1

To improve performance create separate table with only id and blob filed, and join it with your table only if you need this blob filed. And use count(RECORDID) if you need count rows (because it is indexed and not null for all rows).

Facient