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 [email protected], "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 [email protected], "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 [email protected], "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 ! > > > > > >
