>Hi,
>
>I 'd like to share my recent testing experiences with you hoping to help in Firebird development.
>
>I 've been using Firebird (classic server > 70 connections for web serving) for 13 years now.
>
>Recently, I decided to test certain suspicious select queries more carefully. Here are my conclusions
>and I hope you prove me wrong:
>
>1. If the query plan uses indexing, everything works OK and very fast.
>
>2. If the query plan goes the "NATURAL" way then sorting happens which is totally expected. However,
>if the tuning variable TempCacheLimit has a value that is not big enough for the dataset the sorting
>process happens on hard disk and is extremely slow. it depends on the machine and the dataset but it
>has to be a very powerful machine to manage it under 10 secs. On my new 64-bit laptop, Firebird 2.5.2
>for a dataset around 30-40 MB I got 35-40 seconds.
>The NATURAL plan was unavoidable since there was a need of ordering by two columns from two different
>tables.
>I started tuning TempCacheLimit, LockMemSize, DefaultDbCachePages and FileSystemCacheThreshold. I
>discovered that caching in memory does not speed up things so I gave FileSystemCacheThreshold a big
>value. I managed to get an execution time around 2 seconds(4 for the first run) when TempCacheLimit
>was: 536870912(classic super server. With plain classic server you need more memory).
>
>I know that the above value is only a limit. The problem is that during query execution I noticed that
>200 up to 480 MB were actually occupied(depending on the run).
>
>I don't know if things are better with Super Server. It is not an option for me because I am using
>FreeBSD.
>
>As a conclusion, I was disappointed when I tested postgres 9.2 with the same query. It finishes under
>a second without using extra memory. It is obvious that postgres also does extra work for my query but
>in a much more efficient way.
>
>Why Firebird sorts datasets loading whole rows into memory when the plan is NATURAL? Couldn't it sort
>only primary keys for example? There might be something I 'm missing...

Hi Haris!

I've no clue about your actual question, but am curious as to what your query looks like and what size of tables and result sets we're talking about. Going NATURAL on the first table in a plan is quite normal and usually not too time consuming unless we talk about large tables (though I've no experience with Classic...), but NATURAL on other tables in the plan can be quite ugly performancewise. And Firebird can (or at least could) be very slow if you issue the wrong kind of query against it (e.g. ... WHERE <somefield> IN (SELECT DISTINCT <AnotherField> ... wasn't recommended).

Set