In a message on the Borland b.p.i.general newsgroup "Jason Chapman

>(JAC2)" <jason@...> wrote...
>
>
>> You must consider what happens when your query doe not use an index to locate all records required, e.g. select * from myTable where thedate between xxxx and yyyy and someData =1
>>where someData does not have an index. The engine will have to unpack all of the records that fit into the date criteria into RAM and then traverse them to see if somedata =1. I don't know how IB manages unpackeing records from disk to RAM, but I wouldn't be surprised if it uses buffers that are full width of the rows for speed. If this is the case, then you will see a massive performance hit.
>>
>>

From context I gather that the question was whether to use a huge
varchar, a blob, or a file to hold large amounts of data. Like Jason,
I'd recommend blobs because, as Jim says, they're not read unless
explicitly referenced. Jason is right that records are expanded to
their maximum length (counting blobs as eight bytes) before a comparison
can be made against them. However, Firebird reads in one page of data,
expands each qualifying row on that page and checks the non-indexed
comparison. If the record matches, it is returned. If not the space is
reused for the qualifying record on the page. The only time a large
group of records is maintained in RAM is when a sort must be done -
for duplicate removal, to support a sort merge join, or to implement an
order by clause. Then, only the requested fields are kept in RAM.

Regards,


Ann