I have a table with approximately 246 thousand records. It contains about 25 columns, all of which are integers, except for one small blob.
If I query the table in all fields
select a.recordid, a.editcount, ect.. from ARTrans a
Runs in a second. But if I include only the record identifier
select a.recordID from ARTrans a
It takes 20 seconds. Most of the time I spend scheduling (Natural), which seems weird because in most cases I have an index on the records.
I collected garbage, recreated indexes, deleted indexes, added only one index to RecordID, and it is still very slow.
Any help would be greatly appreciated.
Edited to provide additional information:
Firebird: 2.5.3.26778
fbclient.dll: 2.5.1.26351
There is no one else in the database, I moved it locally.
Here is the def table
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 (withdrawal 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 just 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
In addition, I must add that there are 246,804 entries, and it took almost a minute to get the bill.
Preparing query: SELECT count(*) FROM ARTRANS a Prepare time: 52.614s Field
Update
If I remove the blob column, performance will return. If I leave it and NULL out of all the values, the performance will be still fast. If I update the blob field on each record to contain a stream of 20 bytes, the performance returns to 20 + seconds to complete a simple query
select a.recordID from ARTrans a
I went even further and deleted all the columns except the blob and recordID fields and I still get slowness. It looks like it would be earlier. Very strange.