--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:

>
> > >Hi, I'm jimmy
> >
> > Hi Jimmy!
> >
> > > I have a problem, please help me!
> > > I have a table with 3,000,000 rows record, every row update 1 to 5 times in one day.
> > > The speed of update statement become slowly, about 30
> > > records/second, but insert speed above 1000 records/second.
> > > Is my usage be bad or not?
> > > what should i do?
> >
> > I don't know whether your usage is bad or not. What kind of indexes do
> > you have and how do you update? What about transactions, do you have a
> > noticeable gap between oldest (active) transaction and next
> > transaction? I don't know whether it is still relevant (it is a very
> > old article), but in some cases I think rdb$db_key can be useful for
> > updates: http://ibexpert.net/ibe/index.php?n=Doc.TheMysteryOfRDBDBKEY
> >
> > HTH,
> > Set
> >
>
> > Thanks your help!
> > My table have a primary key with integer, when update 200 rows then
> > commit trans. Th
>
> Sounds OK, but what's more important is the gap mentioned above - it doesn't help if the update commit every 200 rows if there is one or more other, concurrent transactions, that runs for a long time without committing (well, transactions that are read only AND read committed are OK, but other combinations are not).
>
> And take up Thomas offer, he will notice if there's something wrong with the output of gstat.
>
> Set
>

>thanks a lot

>gstat result

Database "d:\memdb\datastore_memorydb_net"
Database header page information:
Flags 0
Checksum 12345
Generation 12913
Page size 16384
ODS version 11.2
Oldest transaction 10782
Oldest active 10783
Oldest snapshot 10783
Next transaction 12889
Bumped transaction 1
Sequence number 0
Next attachment ID 52
Implementation ID 16
Shadow count 0
Page buffers 4096
Next header page 0
Database dialect 3
Creation date Jun 10, 2012 22:36:02
Attributes force write

Variable header data:
Sweep interval: 2000
*END*


Database file sequence:
File d:\memdb\datastore_memorydb_net is the only file

Analyzing database pages ...
...
MEASURE_VALUE_DEFINE (145)
Primary pointer page: 155, Index root page: 156
Average record length: 116.76, total records: 1334785
Average version length: 16.39, total versions: 218126, max versions: 5
Data pages: 12958, data page slots: 12958, average fill: 88%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 12957

Index IDX_MEASURE_VALUE_DEFINE (1)
Depth: 2, leaf buckets: 960, nodes: 1334915
Average data length: 1.55, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 2
20 - 39% = 0
40 - 59% = 751
60 - 79% = 141
80 - 99% = 66

Index PK_MEASURE_VALUE_DEFINE (0)
Depth: 2, leaf buckets: 836, nodes: 1334921
Average data length: 1.24, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 627
60 - 79% = 21
80 - 99% = 188

...