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 [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 ! >> > > >> > >> 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
