Thanks Carlos

as you can see the index depth in 3 in both 8 and 16k.

***************************

DESCRIPTION_8K (194)
Primary pointer page: 480, Index root page: 481
Average record length: 933.31, total records: 32047794
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 4174494, data page slots: 4174494, average fill: 87%
Fill distribution:
0 - 19% = 3148
20 - 39% = 25501
40 - 59% = 204768
60 - 79% = 544894
80 - 99% = 3396183

Index RDB$FOREIGN136 (1)
Depth: 3, leaf buckets: 25152, nodes: 32047794
Average data length: 0.34, total dup: 22144746, max dup: 16
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 25151

=> around 40 ms by select using RDB$FOREIGN136

***************************

DESCRIPTION_16K (194)
Primary pointer page: 369, Index root page: 370
Average record length: 933.31, total records: 32047794
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 2045906, data page slots: 2045906, average fill: 91%
Fill distribution:
0 - 19% = 0
20 - 39% = 1351
40 - 59% = 35106
60 - 79% = 150009
80 - 99% = 1859440

Index RDB$FOREIGN136 (1)
Depth: 3, leaf buckets: 12524, nodes: 32047794
Average data length: 0.33, total dup: 22144746, max dup: 16
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 12523

=> around 8 ms by select using RDB$FOREIGN136

***************************

AND if you compare it with the table IDREL (on 8k pagesize)


IDREL_8K (167)
Primary pointer page: 425, Index root page: 426
Average record length: 91.18, total records: 47258163
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 763097, data page slots: 763097, average fill: 82%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 31763
80 - 99% = 731333

Index RDB$FOREIGN70 (1)
Depth: 3, leaf buckets: 35280, nodes: 47258163
Average data length: 0.20, total dup: 38524909, max dup: 100
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 35279

=> around 3.7 second to select using RDB$FOREIGN70 !



not understand, not understand, not understand :( it's like the number of page use by the data matter in the select even if the select don't return any row and use index :(

--- In firebird-support@yahoogroups.com, "Carlos H. Cantu" <listas@...> wrote:
>
> What is this index depth (check with gstat) when you have 8K page and
> 16k?
>
> Carlos
> Firebird Performance in Detail - http://videos.firebirddevelopersday.com
> www.firebirdnews.org - www.FireBase.com.br
>
> n> OK, doing more test, i pass the Page size to 16K (instead of 8k before) and now
>
> n> CREATE TABLE DESCRIPTIONS
> n> (
> n> IDObj VARCHAR(15) NOT NULL,
> n> type SMALLINT NOT NULL,
> n> Lang VARCHAR(3) NOT NULL,
> n> Descr VARCHAR(10000),
> n> PRIMARY KEY (IDObj, type, Lang)
> n> );
> n>
> n> and a foreign key on IDOBJ
> n>
> n> with 32 Millions rows, then
> n> select IDObj from description where IDObj=randomID
> n> with 50 simultaneous thread, take around 80 ms to return !!
> n> With 1 Thread, take around 8 ms to return !!
>
> n> So i not understand WHY here it's more fast (much more) with a
> n> page size of 16k ?? the index is just a single column index on a
> n> varchar(15) field ... but anyway is still more slower (around 2x
> n> more slower) than the same query on a table without any
> n> VARCHAR(10000) fields and with same amount of reccords. but this
> n> make me crasy because on the select we don't use at all this varchar(10000) field !
>
>
> n> --- In firebird-support@yahoogroups.com, "nathanelrick" <nathanelrick@> wrote:
> >>
> >> I become crazy i not understand why After a fresh backup/restore,
> >>
> >> on this table
> >>
> >> CREATE TABLE DESCRIPTIONS
> >> (
> >> IDObj VARCHAR(15) NOT NULL,
> >> type SMALLINT NOT NULL,
> >> Lang VARCHAR(3) NOT NULL,
> >> Descr VARCHAR(10000),
> >> PRIMARY KEY (IDObj, type, Lang)
> >> );
> >>
> >> and a foreign key on IDOBJ
> >>
> >> with 32 Millions rows, then
> >> select IDObj from description where IDObj=randomID
> >> with 50 simultaneous thread, take around 480 ms to return
> >> With 1 Thread, take around 34 ms to return
> >>
> >> Indexed Read: 125
> >> Non Indexed Read: 0
> >>
> >> BUT
> >>
> >> on this table
> >>
> >> CREATE TABLE IDREL (
> >> ID1 SMALLINT NOT NULL,
> >> ID2 SMALLINT NOT NULL,
> >> ID3 INTEGER NOT NULL,
> >> KEY BIGINT NOT NULL,
> >> IDObject VARCHAR(15),
> >> PRIMARY KEY (ID1,ID2,ID3,KEY));
> >>
> >> and a FOREIGN KEY on IDObject
> >>
> >> with 48 Millions rows, then
> >> select IDObj from IDREL where IDObj=randomID
> >> with 50 simultaneous thread, take around 40 ms to return !!
> >> With 1 Thread, take around 3.7 ms to return !!
> >>
> >> Indexed Read: 150
> >> Non Indexed Read: 0
> >>
> >> So 10x more faster !! but their is more rec in the table IDREL
> >>
> >> i even force plan to use the index on the IDObj Foreign key, nothing change :(
> >>
> >> i m lost lost lost :(
> >>
> >> the speed stay the same Blob instead of varchar(10000):
> >>
> >> CREATE TABLE DESCRIPTIONS
> >> (
> >> IDObj VARCHAR(15) NOT NULL,
> >> type SMALLINT NOT NULL,
> >> Lang VARCHAR(3) NOT NULL,
> >> Descr BLOB SUB_TYPE 1 SEGMENT SIZE 4096,
> >> PRIMARY KEY (IDObj, type, Lang)
> >> );
> >>
> >> still 10x more slower than the table IDREL
> >>
> >>
> >>
> >> --- In firebird-support@yahoogroups.com, "nathanelrick" <nathanelrick@> wrote:
> >> >
> >> > hello,
> >> >
> >> > i do some more tests to investigate ...
> >> >
> >> > * When the table is read only by a single user (or very few user) speed is fast (around 30 ms by select)
> >> >
> >> > * when the table is read by more users (around 50), then the speed go down ! around 500 ms by select
> >> >
> >> > * When the table is also updated by some writer, then the speed completely fall down, 1200 ms by select :(
> >> >
> >> > But on other table, with even more records, same amount of select / insert / update / seconds, but without any varchar(10000) field then speed stay always around 13 ms
> >> >
> >> > So
> >> >
> >> > remembered the table is simply :
> >> >
> >> > CREATE TABLE DESCRIPTIONS
> >> > (
> >> > IDObj VARCHAR(15) NOT NULL,
> >> > type SMALLINT NOT NULL,
> >> > Lang VARCHAR(3) NOT NULL,
> >> > Descr VARCHAR(10000),
> >> > PRIMARY KEY (IDObj, type, Lang)
> >> > );
> >> >
> >> > Why a varchar(10000) field (if it's because of the varchar(10000) field, still not sure) can slow down so much the select ? remembered than even select ... where id not exist => also 1200 ms !
> >> >
> >> > what i can do to speed it up ?
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > --- In firebird-support@yahoogroups.com, "nathanelrick" <nathanelrick@> wrote:
> >> > >
> >> > > hello,
> >> > >
> >> > > the table :
> >> > >
> >> > > CREATE TABLE DESCRIPTIONS
> >> > > (
> >> > > IDObj VARCHAR(15) NOT NULL,
> >> > > type SMALLINT NOT NULL,
> >> > > Lang VARCHAR(3) NOT NULL,
> >> > > Descr VARCHAR(10000),
> >> > > PRIMARY KEY (IDObj, type, Lang)
> >> > > );
> >> > >
> >> > > With around 40 millions records
> >> > >
> >> > > now the query :
> >> > >
> >> > > select Descr From DESCRIPTIONS where (IDObj='XXX') AND (type = 1)
> >> > >
> >> > > => 1200 ms to return only one reccord :(
> >> > > the speed is the same if xxx not exist !
> >> > >
> >> > > the plan used:
> >> > > PLAN (DESCRIPTIONS INDEX (RDB$PRIMARY135))
> >> > >
> >> > > what is wrong ?
> >> > >
> >> > > on some other table with around the same number of rec but no VARCHAR(10000) field then the return is in around 40 ms !
> >> > >
> >> >
> >>
>
>
>
>
> n> ------------------------------------
>
> n> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> n> Visit http://www.firebirdsql.org and click the Resources item
> n> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> n> Also search the knowledgebases at http://www.ibphoenix.com
>
> n> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> n> Yahoo! Groups Links
>