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