Loading ...
Sorry, an error occurred while loading the content.

Re: Why it's soo slow ? it's just a very simple select ...

Expand Messages
  • nathanelrick
    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,
    Message 1 of 65 , Mar 1, 2012
    • 0 Attachment
      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 !
      > >
      >
    • nathanelrick
      Dear vlad, yes the prepared statement pooling is working like a charm ! the perf of the database are huge now ! but the problem is that i can not do pooling of
      Message 65 of 65 , Mar 19, 2012
      • 0 Attachment
        Dear vlad,

        yes the prepared statement pooling is working like a charm ! the perf of the database are huge now !

        but the problem is that i can not do pooling of prepared statement for UPDATE (can not let the connection open) :(
      Your message has been successfully submitted and would be delivered to recipients shortly.