Hello Set



Firebird version 1.5.3.4870

The delay it is only opening the query with specific 'where; statement, if I
open the query without 'where' statement it opens immediately.



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))

)



About the 'order by', are the components responsible how the order will be
done? I think only the sql server is responsible how will send the ordered
exported data. I made them from an 'sql editor' so no extra filter or order
functions are activated from the components.



Also very big delay there is on query that returns no results; I think this
is logical because the sql server has expanded all the possibilities to
return something.



Kind regards

Dennis



_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvaer
Sent: Friday, January 12, 2007 3:38 PM
To: Firebird-support
Subject: Re: [firebird-support] 'select' with delays ref/eDN5012844953



Please tell us some more, Dennis.

Which version of Firebird?

What program/components do you use and how do you do things (extracted
parts of the code are sometimes useful)?

When you say 'delay on open' do you mean only the time opening the query
only or do you also include the time connecting to the database, start
the transaction and prepare your query? If so, which of these parts is
taking too long?

As for things being slower with order by, it may be due to your
components displaying records before loading all of them. This is most
visible if your WHERE clause isn't very selective.

Set

Dennis wrote:

> Delay on first open of indexes.
>
> There is a table where has 170000 records, using an index with 3 keys

using

> and 'order by' in the script. No outer joins (seems simple)!
>
> Question 1
>
> The first query delays about 15 seconds, if we try to execute it again the
> same it takes about 1 second. How can we face the first delay?
>
> Question 2
>
> There is an 'order by' statement, where delays too much 1/sec (on the same
> query), without the 'order by' it takes less than 1/10sec. There is an

index

> with this field only, without this index it takes about 2 seconds. May I
> reduce this delay in less than 1 second?
>
> The above time is under good cpu server conditions, when the server is
> 'working' these times take much longer, because of that I try to reduce

the

> one second.

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