>Although the first time an index is used is where it is cached, unless
>your index is absolutely huge, I doubt that this is your issue. Have
>you tried running this query in iSQL rather than your application?
>Some components cache different pieces of information, so the delays
>you experience may well be related to this rather than Firebird itself.

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

>* Make sure your database does not have a gdb file extension.

I had gdb extension! I changed it now.

>* 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?



From which point do you think I have to search bigger sql server like Oracle
for faster performance?



Regards

Dennis



_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Saturday, January 13, 2007 1:21 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: 'select' with delays ref/eDN5012844953



--- In firebird-support@ <mailto:firebird-support%40yahoogroups.com>
yahoogroups.com, "Dennis" <dennis@...> wrote:

>
> Hi Alexandre
>
>
>
> Firebird version 1.5.3.4870
>
> The second run of the query is faster because of the cached data

indeed, how

> can we reduce the 1st delay? It this 15seconds a normal delay?

Although the first time an index is used is where it is cached, unless
your index is absolutely huge, I doubt that this is your issue. Have
you tried running this query in iSQL rather than your application?
Some components cache different pieces of information, so the delays
you experience may well be related to this rather than Firebird itself.

* Make sure your database does not have a gdb file extension.
* 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.

> The query is:
>
> select * from reg_header
>
> where
>
> (reg_header.rpl_deleted=0) <-- this doesn't make difference
>
> and (
>
> ((merch_fromstore_innerc='agse.1228991')and(merch_iss_regtype=0))
>
> or ((merch_tostore_innerc='agse.1228991')and(merch_rec_regtype=0))
>
> )
>
> Indicies:
>
> MERCH_FROMSTORE_INNERC,MERCH_ISS_REGTYPE,RPL_DELETED
>
> MERCH_TOSTORE_DESC,MERCH_REC_REGTYPE,RPL_DELETED
>
>
>
> Which indexes must exist to support this query?

The two indices provided seem appropriate.

Adam





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