At 06:38 PM 12/5/2000 +0300, Dmitry Yemanov wrote:

>Who could explain in details, how the engine performs queries with ORDER BY
>clause.

Me, probably. With Helen's concurrence, I'd like to treat this as
an architecture question.

>I have one relatively big table (~20000 records) and a few small ones (0 to
>1000 records) in my database (6MB). When I'm trying to select all fields
>from the big table left joined with all others without sorting, it works
>fine. When I'm trying to perform the same statement with ORDER BY, a
>temporary sorting file is being created by the engine, but this file is
>finally ~600MB in size. Simple calculation shows that the complete result
>set for my query should have the similar size, but only in the case when
>all fields are stored with their maximum possible size, for example ~255
>bytes for VARCHAR(255) and so on. Is it true or it's just coincidence?

No, it's not a coincidence. To avoid re-reading the database in suboptimal
order, InterBase produces an intermediate result set and performs a tag
sort (I believe) on those intermediate results. The intermediate set is
stored in fixed sized buffers - thus the expansion of varchar(255) to 257
bytes.


Regards,

Ann
www.ibphoenix.com
We have answers.