At 06:09 PM 4/04/2003 +0000, you wrote:

>In firebird, is there a paging size to improve the performance of
>queries that contain large (i.e. 1024) VARCHARs when an ORDER BY
>clause is present?
>
>Consider the following:
>
>SELECT integerFoo, varchar1024 FROM test;
>
>This is very fast. But this is much slower:
>
>SELECT integerFoo, varchar1024 FROM test ORDER BY integerFoo;
>
>Where or not there are indices makes no impact on speed. I'm
>assuming that Firebird has to shuffle buffers around for the large
>varchar field when doing an ORDER BY. So, I'm wonder if there are
>any config parameters that could increase the performance of this
>kind of query.

An index on integerFoo is essential for the sort, unless your query
involves just a few rows. You have no WHERE clause so one assumes that
your output sets will be large and unrestricted.

Your problem is probably that the sorting space that you have configured on
the server is too small. If you didn't specifically configure sort space
in a directory where plenty of space is available, then your TMP directory
will be used. If space in that is limited, there will be a lot of swapping
going on during the sort.

You can configure a list of disk locations for sorting by adding
tmp_directory items to the configuration file. You need to allocate 1.5 to
2 times the maximum size of your largest output set; more if multiple
users are concurrently running huge sorts.

I realise that your example here probably isn't your real query. It is
quite possible that there are other indexes in the plan that are
interfering with the ordered version of your query, causing it to be
slow. The best way to investigate slow queries is to examine the
plan. You can use the PLANONLY keyword in isql to see the plan without
proceeding to generate the output. Various other tools, e.g. IB_SQL, can
show you the plan for a prepared query.

heLen