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