Subject Re: [firebird-support] Slow database
Author Rafael Szuminski
One area I would look into is queries with sub-select statements. In my
case (a small database app) the slowdowns started with more data in the
subselect table. For instance:

Select * from TblCustomers where CustomerID in (Select CustomerID from
tblSomething Where Something=Something) or CityID in (Select CityID from
Something where Something=Something)

With 30000 customers the query started slowing with data being added to
table 'something'. If there were 1-5 records in 'something' it was fast,
but at 100 records in something the query execution time increased 50 times

Anyhow, rewriting the query to use left joins solved the issue for me.

Hope this helps


Milan Babuskov wrote:
> Riho-Rene Ellermaa wrote:
>>I'm creating new database with IBO scripts. I use FB 1.03, Win2000, file extension is FDB.
>>Connection TCP_IP, localhost.
>>First I create database and then run script that creates tables and fills them with data.
>>Tables are quite simple (4-5 fields, with integer primary index). Total inserted rows is 1800.
>>In my computer the script runs in 30 sec. In customers site computer it's 4 min. Log shows that database filling takes approx 3 min.
>>I let him change from TCP_IP to local connection, but that didn't help. He has P4 computer that should be fast enough.
>>Any ideas?
> Anti virus software?
> Have you tried to separate script to smaller pieces to see what is slow:
> connection? creating tables? inserts?
> Also, maybe hard disks, P4 can work really slow if HDD is slow. You can
> perhaps try to turn worced writes off, and see if there is any difference...

Rafael Szuminski
Phone:(949)939 - 2458