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