Alec Swan wrote:

> We are working on tracking those queries down, but fixing them will require
> an upgrade which the customer does not want to do. Moreover, their db size
> is 12GB, so it may not be possible to optimize the queries a lot further.
>
> The customer also ran out of disk space trying to rebuild database indexes
> which left firebird in a corrupted state. We had to add more space and run
> gfix to repair it. This is a different problem, but it's also related to FB
> disk usage.
>
> Is TempDirectories setting expected to work in embedded mode? What happens
> when max allowed temp space is not big enough for a sort?

Personally I would not be comfortable with the set-up you seem to be trying to
run. 12Gb of database really needs it's own server rather than trying to run it
with just a single embedded connection. I'd also be a lot happier having that on
a Linux box which is just managing the database. Windows has it's own 'layers'
of protection that keep files open while a program is running, so I suspect that
it is windows that is retaining the disk space rather than Firebird. Until a
program closes windows does not release some resources.

But more important is to review the queries since a CTE approach may be able to
apply filtering before you try sorting the extremely large results sets you seem
to be working with. I have half million to one million record sets of data these
days and have had to add extra indexes to allow some processing that was fast
even on the slower machines from 10 years ago, but was getting slower.

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php