community.borland.com

Article #25277: slower performance on a select statement that retrieves less data

Problem:
a query that retrieves less data performs slower than a query that 
retrieves more data.

example:
Here is a table and 2 queries.  The query that selects 4 fields is 
faster than the query that selects only 2 fields (the results of this 
query being a subset of the larger query).

/* create table statement */
CREATE TABLE MAILER (MLR_ID INTEGER NOT NULL,
        SHP_ID SMALLINT,
        CMP_ID INTEGER NOT NULL,
        CAT_ID SMALLINT,
        MLR_NAME NAME,
        MLR_EXTERNALCODE EXTERNAL_CODE,
        MLR_SORT SORT_CODE,
        MLR_CREATED CREATE_DATE,
        MLR_CREATEDBY CREATE_USER,
        MLR_REVISED REVISED_DATE,
        MLR_REVISEDBY REVISED_USER,
PRIMARY KEY (MLR_ID));

/* *****two fields query*********** */
SELECT MLR_ID, MLR_NAME
FROM MAILER
WHERE CMP_ID = 3100
ORDER BY MLR_SORT

Current memory = 2775424
Delta memory = 0
Max memory = 2781568
Elapsed time= 6.65 sec
Buffers = 10000
Reads = 0
Writes 0
Fetches = 1287

/* *****four fields query*********** */
SELECT MLR_ID, MLR_NAME, SHP_ID, CAT_ID
FROM MAILER
WHERE CMP_ID = 3100
ORDER BY MLR_SORT

Current memory = 2775424
Delta memory = 0
Max memory = 2781568
Elapsed time= 2.42 sec
Buffers = 10000
Reads = 0
Writes 0
Fetches = 1287

Solution:
This is a known bug (bug number 8112) with InterBase V4.x kits.  It has to do 
with the way we arrive at the number of records that will fit in a packet to be sent 
across the wire.  It is fixed in InterBase 5.0.

The workaround is to use the query that returns more rows and just have your 
application ignore the extra rows.

Last Modified: 02-OCT-00