Hello Adam



Well I did backup and restore, the restore produces an error telling that
cannot inactivate an index (?) doesn't this tell you something?.



I did a backup and restore from my code (creating new database and dropping
there data by the usual way) and then the backup and restore of firebird
worked well.



After these backups and restores the database has the half size and it is
much faster, 3-4 the initial query. The delay is restricted in the statement
'order by issue_date desc'. This produces this delay, from user's interface
I made this descent order optional, so if the user wants descent he will
wait some seconds.



Unfortunately didn't manage to reduce this delay.



I checked a known and famous trade applications installed on one of my
customers (I don't want to say the name of it) where it works with mssql, on
this same point with mine problem it is much worse, about 200000 records
they do 50 sec to return data and this is happening every time the user
request the same list. Of course we cannot know the reason of this delay as
we are not the programmers of this application, but looking as simple user
two different applications this delay is delay and this is bad.



Have a nice weekend

Dennis





_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Tuesday, January 16, 2007 2:01 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: 'select' with delays ref/eDN5012844953

>
> I have tried through iSQL and it is doing the same.

OK, so we have eliminated client connection component caching (eg IBO).

>
>
> >* Make sure your database does not have a gdb file extension.
>
>
>
> I had gdb extension! I changed it now.

Windows thinks that gdb is important for system restore, so it will
take a snapshot during the first connection. You are not clear whether
you have run your tests after making the change, so if not, test again.

>
>
> >* Perform a backup-restore cycle on your database. It is possible the
> >15 second of the initial connection is actually the garbage collection
> >as it is discovered.
>
>
>
> Similar delays are occurred on other databases with fewer records on

their

> table (2000-3000 recs).
>
>
>
> Is there need for any kind of database repair? Like this repair you said
> (backup and restore), is there any other repair procedure that is

required

> on large databases?

Well backup and restore will cure the lot. You can set the statistics
on all the indices to make sure the best plan is used, but in your
case I doubt the plan is the problem.

>
>
>
> From which point do you think I have to search bigger sql server

like Oracle

> for faster performance?
>
>

... after a lot of TLC, you probably will get that performance yes.
Believe me, 200000 records is not a large database.

Lets just look first at your problem.

There is just too big a gap between 15 seconds and 0.1 seconds for the
same query on the same data.

A similar query on a similar sized table on my laptop is under 2
seconds on initial run. On an adequately resourced server it would be
well subsecond.

My theory is that your initial run is collecting garbage. Subsequent
runs do not encounter this garbage. A backup-restore will fix that.
This can happen if you do something like

delete from mytable;

before your test. As a test, try something like:

delete from mytable;
commit;
select count(*) from mytable;

and see how long it takes for it to discover there are 0 records. This
is garbage collection at work, which in turn is the downside of MGA.
The upside is that you don't have to worry about locks causing a huge
bottleneck.

Adam





[Non-text portions of this message have been removed]