Subject Re: [firebird-support] Re: First word after in alphabetical order
Author Helen Borrie
At 11:39 AM 28/08/2005 +0200, you wrote:

>- my server is a celeron 333 MHz with 256 MB memory

Hmm, can we get a little bit real here? This is not exactly a *server*
configuration in this day and age. You have multiple sorts going on, with
inadequate RAM to do them in memory. (I have 256 Mb and a Celeron 333 on a
1998 Thinkpad I bought recently for $90 AUD!! Yes, I'm using it as a
Mandrake 10 platform for testing Firebird 2 functionality. Performance is
not even on the radar with this configuration!)

Five million rows are being swapped back and forth to and from disk-based
sort files. That's certainly going to burn some calories. Unless you have
explicitly configured sort space, this I/O is happening in /tmp, which is
the general dumping ground for all manner of junk. Granted, Linux on that
machine will give you good "bang for the buck" but nobody is ever going to
win the Monte Carlo on a pushbike.

>- I am using IBOConsole with interactive SQL for my test

Run your test at the command line using isql and compare timings with those
across the wire to a GUI interface. Your query is certainly a very
demanding one for the server but you are throwing a lot of overhead at it
at the client side and across the wire as well. The time taken to populate
the Windows control has very little to do with the time taken to fulfil the
query. The components behind that interface request rows from the server
on an "as required" basis. You will see the first batch of rows some
length of time before the entire output has been fetched from the server.

>- my database have a single table (see definition at bottom):
>- I filled my table using an external table (source file that I modified
>: There are
>5,000,000 rows.

Walking 5 million rows three times (two SELECT FIRSTs and then the
elimination of duplicates for the union) is NOT likely to produce a
lightning fast result. I think that ~1 minute is pretty good, considering
your under-resourced server and the environment you are using to perform
the test. I suspect that your times reflect how long it takes to fill the
initial buffer at the client.