I guess what Ivan already wanted to point out is that you are paying
the cost for sloppy database design. I know that it probably is not your
fault and maybe you don't have means to change that, if for example some
legacy application expects this structure, and you can't access this
application code.

But whatever you do, you won't be able to increase the speed to a
satisfying level.

Whatever I do, I can not imagine a scenario where 30 indexes on a
10-field table would make sense, looking at the total efficiency
(legacy application included).

Are you absolutely sure that you really need all these indexes?

ciao,
André


~~~Ihre Nachricht~~~

s> thanks ivan,

s> yes i just try with force write off, and yes now i have

s> Time to execute the sql: 16 ms
s> Indexed Read: 117
s> Non Indexed Read: 0
s> Inserts: 30
s> Updates: 0
s> Deletes: 30
s> Time to commit the data: 0

s> the commit is now very fast (in apparence) but in fact it's not
s> really help because if i execute five time the query, on the fifth
s> (exactly on every fifth execute) i have

s> Time to execute the sql: 0 ms
s> Indexed Read: 117
s> Non Indexed Read: 0
s> Inserts: 30
s> Updates: 0
s> Deletes: 30
s> Time to commit the data: 3202

s> :( it's mean if i m unlucky when i execute the query that it is
s> the flush time i loose :( i was thinking that the flush was done by
s> a separate thread and not by a "commit" thread

s> do you have any other idea how to speed up the commit ? the index
s> is multi column (6 at max) ... do you thing reduce the number of column can help ?

s> thanks you by advance


s> --- In firebird-support@yahoogroups.com, "Ivan Prenosil" <Ivan.Prenosil@...> wrote:

>>
>> > when i execute some SQL, the time to execute the sql is very fast (16ms) but the time to commit the data is very very slow (2293
>> > ms, even some time more than 10 secondes !)
>> >
>> > Time to execute the sql: 16 ms
>> > Indexed Read: 117
>> > Non Indexed Read: 0
>> > Inserts: 30
>> > Updates: 0
>> > Deletes: 30
>> > Time to commit the data: 2293
>> >
>> > the tables where the record was inserted/deleted have lot of indexes (around 30 index by table, and around 10 columns by table)
>>
>> On commit, every page in FB cache changed by your transaction
>> (and depending) must be written to disk.
>> FB keeps database in consistent state by writing pages in specific order,
>> and setting Forced Writes On prevents OS from optimizing those writes.
>> So, modifying tables with lots of indexes like yours causes
>> heavy disk trashing and so is slow.
>> You should reduce number of indexes,
>> or (if you can take that risk) turn Forced Writes Off.
>>
>> Ivan
>> http://www.volny.cz/iprenosil/interbase/
>>

s> ------------------------------------

s> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

s> Visit http://www.firebirdsql.org and click the Resources item
s> on the main (top) menu. Try Knowledgebase and FAQ links !

s> Also search the knowledgebases at http://www.ibphoenix.com

s> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
s> Yahoo! Groups Links



---