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