Testing a few things with FlameRobin as the only connected user/app.

Ann W. Harrison skriver:

> Before going into a full investigation, does the
> problem happen whenever you start the database or just
> with an insert to that particular table?

Insert in a different table was fast. Selects, including from "Uppgift"
are fast, as was noted using select from mon$statements when my
application was running. Insert into "Uppgift" takes... 39 seconds.
Deleting the record again was instantaneous. So why does it take forever
(20-60 minutes) when doing it from my application?

> What happens
> with a SELECT FIRST 1 on that table, with no order by?

Fast even with rows 1000.

> What happens with an indexed select?

Instantaneous.

> What about queries
> on other tables - or RDB$RELATIONS?

Fast.

> What does gstat -a report about that table when the
> database is first started?

gstat -a -t "Uppgift" shows this:

----------------------------------------
Database "d:\datadia\data\firebird\diamonds.fdb"
Database header page information:
Flags 0
Checksum 12345
Generation 3362754
Page size 8192
ODS version 11.1
Oldest transaction 3362689
Oldest active 3362690
Oldest snapshot 3362690
Next transaction 3362693
Bumped transaction 1
Sequence number 0
Next attachment ID 47
Implementation ID 26
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Nov 18, 2010 1:49:26
Attributes

Variable header data:
Database backup GUID: {D0FF9040-0002-47C9-B487-FC68B87751DF}
Sweep interval: 20000
*END*


Database file sequence:
File d:\datadia\data\firebird\diamonds.fdb is the only file

Analyzing database pages ...
Uppgift (185)
Primary pointer page: 281, Index root page: 282
Data pages: 2754700, data page slots: 2754700, average fill: 83%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 520684
60 - 79% = 188413
80 - 99% = 2045603

Index IX_PK_Uppgift (0)
Depth: 4, leaf buckets: 234048, nodes: 148721943
Average data length: 5.05, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 95
20 - 39% = 1
40 - 59% = 19410
60 - 79% = 9206
80 - 99% = 205336

Index IX_Uppgift_BorttagsuppA5K (6)
Depth: 3, leaf buckets: 111343, nodes: 148818730
Average data length: 0.00, total dup: 148788663, max dup: 124338626
Fill distribution:
0 - 19% = 17
20 - 39% = 4480
40 - 59% = 21938
60 - 79% = 2569
80 - 99% = 82339

Index IX_Uppgift_F├Ârr├Ñdsv├ñrde (5)
Depth: 3, leaf buckets: 106650, nodes: 148721943
Average data length: 0.00, total dup: 148720013, max dup: 104515232
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 20769
60 - 79% = 397
80 - 99% = 85483

Index IX_Uppgift_Hållare (2)
Depth: 4, leaf buckets: 346896, nodes: 148721943
Average data length: 4.23, total dup: 24919990, max dup: 343256
Fill distribution:
0 - 19% = 543
20 - 39% = 0
40 - 59% = 292039
60 - 79% = 6323
80 - 99% = 47991

Index IX_Uppgift_Länkobjekt (3)
Depth: 3, leaf buckets: 91591, nodes: 148721943
Average data length: 0.00, total dup: 148721942, max dup: 148721942
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 2079
60 - 79% = 2
80 - 99% = 89510

Index IX_Uppgift_RelateradHåIS0 (4)
Depth: 3, leaf buckets: 102098, nodes: 148721943
Average data length: 0.06, total dup: 147037429, max dup: 146837723
Fill distribution:
0 - 19% = 52
20 - 39% = 0
40 - 59% = 19922
60 - 79% = 1
80 - 99% = 82123

Index IX_Uppgift_SenasteÄgar7K5 (8)
Depth: 3, leaf buckets: 110302, nodes: 148760891
Average data length: 0.01, total dup: 148714948, max dup: 36904
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1901
60 - 79% = 13
80 - 99% = 108388

Index IX_Uppgift_TilläggsuppYNC (7)
Depth: 3, leaf buckets: 110623, nodes: 148721943
Average data length: 0.01, total dup: 148676000, max dup: 34245
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 2608
60 - 79% = 1
80 - 99% = 108014

Index IX_Uppgift_Uppgiftsägare (1)
Depth: 3, leaf buckets: 191727, nodes: 148760891
Average data length: 0.01, total dup: 148760887, max dup: 147589040
Fill distribution:
0 - 19% = 1
20 - 39% = 1
40 - 59% = 164625
60 - 79% = 26
80 - 99% = 27074
----------------------------------------

I am aware that some of these have low selectivity and very long
sequences of duplicates. For the most part, these would be mostly-null
columns, but where the index may be useful for the few rows that are not
null. To some extent, the number of non-null rows will grow over time.

> How many records are there in RDB$PAGES?

2578.

I should make a somewhat closer investigation as to what my application
actually does. I just don't know if it's worth the effort, since it's a
bit complicated to get below all the layers on top of the fbConnection.

Regards,
Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64