Hi,

you compare aples with oranges ..
look at your test - and not index read but pages read
then you will see where is differece

i see here only disc operation speed
and you do test with random data - try use the same ids in all tests
becaouse you can use random id but on the asme database page!

Karol Bieniaszewski

--- In firebird-support@yahoogroups.com, "nathanelrick" <nathanelrick@...> wrote:
>
> OK, doing more test, i pass the Page size to 16K (instead of 8k before) and now
>
> 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 80 ms to return !!
> With 1 Thread, take around 8 ms to return !!
>
> So i not understand WHY here it's more fast (much more) with a page size of 16k ?? the index is just a single column index on a varchar(15) field ... but anyway is still more slower (around 2x more slower) than the same query on a table without any VARCHAR(10000) fields and with same amount of reccords. but this make me crasy because on the select we don't use at all this varchar(10000) field !
>
>
> --- 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 !
> > > >
> > >
> >
>