Does the 'INSERT INTO' take equally long time if you use a unique (or at least selective) value in all the indexed fields which have over 100 million max duplicates?

I just thought this could be a quick way to check whether this was the reason, unfortunately I've no idea how to improve the situation if it is (well, you could of course try to add the primary key to the end of the index as everybody did a few versions ago)...

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Kjell Rilbe
Sent: 17. desember 2010 11:01
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] First insert in large table takes extreme time

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



------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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

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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links