If I understand correctly, the JayBird driver doesn't allow prepared
statements to persist beyond a commited transaction.
Doesn't this mean a great performance hit? If so, then any improvements to
the query prepare time would be significant!

Regards
Kenneth

----- Original Message -----
From: "Carlos H. Cantu" <warmbooter@...>
To: "Roberto Della Pasqua" <ib-support@yahoogroups.com>
Sent: Saturday, February 22, 2003 7:43 PM
Subject: Re[2]: [ib-support] Re: just if you are curious


> I see the major diference is in prepare time. I think preparing a
> query 1000 times in a loop is not a common task in a real world
> application so I would not bother with this.
>
> I would be more preocupated if FB would be much slower in query
> execution (FB win) and transaction operations (almost same time
> with IB7). Anyway, I think this should be posted in firebird-devel and
> not here ;)
>
> []s
>
> Carlos
> WarmBoot Informatica - http://www.warmboot.com.br
> FireBase - http://www.FireBase.com.br
>
> RDP> OK, let's go with the benchs; resuming ib7 is 40% faster on selects
than
> RDP> fb1.5 beta2
>
> RDP> table of 550,000 records, fresh restored with fb1.5beta2; this is the
table:
>
> RDP> SET SQL DIALECT 3;
>
> RDP> CREATE GENERATOR GEN_MAIN_ID;
> RDP> SET NAMES NONE;
>
>
> RDP>
/***************************************************************************
> RDP> ***/
> RDP> /*** Tables
> RDP> ***/
> RDP>
/***************************************************************************
> RDP> ***/
>
> RDP> CREATE TABLE MAIN (
> RDP> ID INTEGER NOT NULL,
> RDP> NICK VARCHAR(12),
> RDP> EMAIL VARCHAR(50),
> RDP> UOMO INTEGER,
> RDP> ISCRITTO DATE,
> RDP> NATO DATE,
> RDP> SEGNO INTEGER,
> RDP> REGIONE INTEGER,
> RDP> PROVINCIA INTEGER,
> RDP> LOCALITA INTEGER,
> RDP> AMICO INTEGER,
> RDP> PAROLE VARCHAR(250),
> RDP> FOTOESISTE INTEGER,
> RDP> FOTOURL VARCHAR(50),
> RDP> COLCAPELLI INTEGER,
> RDP> STICAPELLI INTEGER,
> RDP> COLOCCHI INTEGER,
> RDP> STIOCCHI INTEGER,
> RDP> CORPO INTEGER,
> RDP> ACCESSORI INTEGER,
> RDP> CARATTERE INTEGER,
> RDP> ARGOMENTO INTEGER,
> RDP> FUMARE INTEGER,
> RDP> FUMARETUTTO INTEGER,
> RDP> BERE INTEGER,
> RDP> UBRIACARMI INTEGER,
> RDP> CUCINARE INTEGER,
> RDP> CASA INTEGER,
> RDP> SPESA INTEGER,
> RDP> FIGLI INTEGER,
> RDP> SHOPPING INTEGER,
> RDP> AMICI INTEGER,
> RDP> ORTO INTEGER,
> RDP> ANIMALI INTEGER,
> RDP> AMORE INTEGER,
> RDP> ALLAMORE INTEGER,
> RDP> MUSICA INTEGER,
> RDP> GUIDARE INTEGER,
> RDP> PUNTUALITA INTEGER,
> RDP> STUDIARE INTEGER,
> RDP> TV INTEGER,
> RDP> COCCOLE INTEGER,
> RDP> VIAGGIARE INTEGER,
> RDP> RISTORARE INTEGER,
> RDP> DISCOTECA INTEGER,
> RDP> ARTE INTEGER,
> RDP> COLLEZIONISMO INTEGER,
> RDP> MUSEO INTEGER,
> RDP> TEATRO INTEGER,
> RDP> CONCERTI INTEGER,
> RDP> CINEMA INTEGER,
> RDP> INTERNET INTEGER,
> RDP> VOLONTARIATO INTEGER,
> RDP> LEGGERE INTEGER,
> RDP> NATURA INTEGER,
> RDP> MONTAGNA INTEGER,
> RDP> CAMPEGGIO INTEGER,
> RDP> POLITICA INTEGER,
> RDP> MODA INTEGER,
> RDP> VIDEOGIOCHI INTEGER,
> RDP> MARE INTEGER,
> RDP> PUB INTEGER,
> RDP> PWD VARCHAR(12)
> RDP> );
>
>
>
>
>
> RDP>
/***************************************************************************
> RDP> ***/
> RDP> /*** Primary Keys
> RDP> ***/
> RDP>
/***************************************************************************
> RDP> ***/
>
> RDP> ALTER TABLE MAIN ADD CONSTRAINT PK_MAIN PRIMARY KEY (ID);
>
>
> RDP>
/***************************************************************************
> RDP> ***/
> RDP> /*** Indices
> RDP> ***/
> RDP>
/***************************************************************************
> RDP> ***/
>
> RDP> CREATE INDEX IDX_ACCESSORI ON MAIN (ACCESSORI);
> RDP> CREATE INDEX IDX_ALLAMORE ON MAIN (ALLAMORE);
> RDP> CREATE INDEX IDX_AMICI ON MAIN (AMICI);
> RDP> CREATE INDEX IDX_AMICO ON MAIN (AMICO);
> RDP> CREATE INDEX IDX_AMORE ON MAIN (AMORE);
> RDP> CREATE INDEX IDX_ANIMALI ON MAIN (ANIMALI);
> RDP> CREATE INDEX IDX_ARGOMENTI ON MAIN (ARGOMENTO);
> RDP> CREATE INDEX IDX_ARTE ON MAIN (ARTE);
> RDP> CREATE INDEX IDX_BERE ON MAIN (BERE);
> RDP> CREATE INDEX IDX_CAMPEGGIO ON MAIN (CAMPEGGIO);
> RDP> CREATE INDEX IDX_CARATTERE ON MAIN (CARATTERE);
> RDP> CREATE INDEX IDX_CASA ON MAIN (CASA);
> RDP> CREATE INDEX IDX_CINEMA ON MAIN (CINEMA);
> RDP> CREATE INDEX IDX_COCCOLE ON MAIN (COCCOLE);
> RDP> CREATE INDEX IDX_COLCAPELLI ON MAIN (COLCAPELLI);
> RDP> CREATE INDEX IDX_COLLEZIONISMO ON MAIN (COLLEZIONISMO);
> RDP> CREATE INDEX IDX_COLOCCHI ON MAIN (COLOCCHI);
> RDP> CREATE INDEX IDX_CONCERTI ON MAIN (CONCERTI);
> RDP> CREATE INDEX IDX_CORPO ON MAIN (CORPO);
> RDP> CREATE INDEX IDX_CUCINARE ON MAIN (CUCINARE);
> RDP> CREATE INDEX IDX_DISCOTECA ON MAIN (DISCOTECA);
> RDP> CREATE INDEX IDX_EMAIL ON MAIN (EMAIL);
> RDP> CREATE INDEX IDX_FIGLI ON MAIN (FIGLI);
> RDP> CREATE INDEX IDX_FOTOESISTE ON MAIN (FOTOESISTE);
> RDP> CREATE INDEX IDX_FUMARE ON MAIN (FUMARE);
> RDP> CREATE INDEX IDX_FUMARETUTTO ON MAIN (FUMARETUTTO);
> RDP> CREATE INDEX IDX_GUIDARE ON MAIN (GUIDARE);
> RDP> CREATE INDEX IDX_INTERNET ON MAIN (INTERNET);
> RDP> CREATE INDEX IDX_ISCRITTO ON MAIN (ISCRITTO);
> RDP> CREATE INDEX IDX_LEGGERE ON MAIN (LEGGERE);
> RDP> CREATE INDEX IDX_LOCALITA ON MAIN (LOCALITA);
> RDP> CREATE DESCENDING INDEX IDX_MAIN ON MAIN (ID);
> RDP> CREATE INDEX IDX_MARE ON MAIN (MARE);
> RDP> CREATE INDEX IDX_MODA ON MAIN (MODA);
> RDP> CREATE INDEX IDX_MONTAGNA ON MAIN (MONTAGNA);
> RDP> CREATE INDEX IDX_MUSEO ON MAIN (MUSEO);
> RDP> CREATE INDEX IDX_MUSICA ON MAIN (MUSICA);
> RDP> CREATE INDEX IDX_NATO ON MAIN (NATO);
> RDP> CREATE INDEX IDX_NATURA ON MAIN (NATURA);
> RDP> CREATE INDEX IDX_NICK ON MAIN (NICK);
> RDP> CREATE INDEX IDX_ORTO ON MAIN (ORTO);
> RDP> CREATE INDEX IDX_POLITICA ON MAIN (POLITICA);
> RDP> CREATE INDEX IDX_PROVINCIA ON MAIN (PROVINCIA);
> RDP> CREATE INDEX IDX_PUB ON MAIN (PUB);
> RDP> CREATE INDEX IDX_PUNTUALITA ON MAIN (PUNTUALITA);
> RDP> CREATE INDEX IDX_REGIONE ON MAIN (REGIONE);
> RDP> CREATE INDEX IDX_RISTORARE ON MAIN (RISTORARE);
> RDP> CREATE INDEX IDX_SEGNO ON MAIN (SEGNO);
> RDP> CREATE INDEX IDX_SHOPPING ON MAIN (SHOPPING);
> RDP> CREATE INDEX IDX_SPESA ON MAIN (SPESA);
> RDP> CREATE INDEX IDX_STICAPELLI ON MAIN (STICAPELLI);
> RDP> CREATE INDEX IDX_STIOCCHI ON MAIN (STIOCCHI);
> RDP> CREATE INDEX IDX_STUDIARE ON MAIN (STUDIARE);
> RDP> CREATE INDEX IDX_TEATRO ON MAIN (TEATRO);
> RDP> CREATE INDEX IDX_TV ON MAIN (TV);
> RDP> CREATE INDEX IDX_UBRIACARMI ON MAIN (UBRIACARMI);
> RDP> CREATE INDEX IDX_UOMO ON MAIN (UOMO);
> RDP> CREATE INDEX IDX_VIAGGIARE ON MAIN (VIAGGIARE);
> RDP> CREATE INDEX IDX_VIDEOGIOCHI ON MAIN (VIDEOGIOCHI);
> RDP> CREATE INDEX IDX_VOLONTARIATO ON MAIN (VOLONTARIATO);
>
>
> RDP>
/***************************************************************************
> RDP> ***/
> RDP> /*** Triggers
> RDP> ***/
> RDP>
/***************************************************************************
> RDP> ***/
>
>
> RDP> SET TERM ^ ;
>
>
>
>
> RDP> /* Trigger: MAIN_BI */
> RDP> CREATE TRIGGER MAIN_BI FOR MAIN
> RDP> ACTIVE BEFORE INSERT POSITION 0
> RDP> AS
> RDP> BEGIN
> RDP> IF (NEW.ID IS NULL) THEN
> RDP> NEW.ID = GEN_ID(GEN_MAIN_ID,1);
> RDP> END
> RDP> ^
>
>
> RDP> SET TERM ; ^
>
>
>
RDP> -----------------------------------------------------------------------
-----
> RDP> ------------------
> RDP> This code produce 1000 random queries, so to avoid too much cache
behavior.
> RDP> function randomSQL: string;
> RDP> var fields: array[0..50] of string;
> RDP> I: Integer;
> RDP> logic: array[0..1] of string;
> RDP> begin
> RDP> Result := 'SELECT * FROM MAIN WHERE ';
> RDP> fields[0] := 'FUMARE';
> RDP> fields[1] := 'FUMARETUTTO';
> RDP> fields[2] := 'BERE';
> RDP> fields[3] := 'UBRIACARMI';
> RDP> fields[4] := 'CUCINARE';
> RDP> fields[5] := 'CASA';
> RDP> fields[6] := 'SPESA';
> RDP> fields[7] := 'FIGLI';
> RDP> fields[8] := 'SHOPPING';
> RDP> fields[9] := 'AMICI';
> RDP> fields[10] := 'ORTO';
> RDP> fields[11] := 'ANIMALI';
> RDP> fields[12] := 'AMORE';
> RDP> fields[13] := 'ALLAMORE';
> RDP> fields[14] := 'MUSICA';
> RDP> fields[15] := 'GUIDARE';
> RDP> fields[16] := 'PUNTUALITA';
> RDP> fields[17] := 'STUDIARE';
> RDP> fields[18] := 'TV';
> RDP> fields[19] := 'COCCOLE';
> RDP> fields[20] := 'VIAGGIARE';
> RDP> fields[21] := 'RISTORARE';
> RDP> fields[22] := 'DISCOTECA';
> RDP> fields[23] := 'ARTE';
> RDP> fields[24] := 'COLLEZIONISMO';
> RDP> fields[25] := 'MUSEO';
> RDP> fields[26] := 'TEATRO';
> RDP> fields[27] := 'CONCERTI';
> RDP> fields[28] := 'CINEMA';
> RDP> fields[29] := 'INTERNET';
> RDP> fields[30] := 'VOLONTARIATO';
> RDP> fields[31] := 'LEGGERE';
> RDP> fields[32] := 'NATURA';
> RDP> fields[33] := 'MONTAGNA';
> RDP> fields[34] := 'CAMPEGGIO';
> RDP> fields[35] := 'POLITICA';
> RDP> fields[36] := 'MODA';
> RDP> fields[37] := 'VIDEOGIOCHI';
> RDP> fields[38] := 'MARE';
> RDP> fields[39] := 'PUB';
> RDP> logic[0] := 'AND';
> RDP> logic[1] := 'OR';
>
> RDP> Result := Result + 'UOMO=' + IntToStr(RandomBool) + ' AND
LOCALITA=' +
> RDP> IntToStr(Random(12)) + ' AND ';
>
> RDP> for I := 1 to Random(50) do // Iterate
> RDP> begin
> RDP> Result := Result + fields[random(39)] + '=' +
IntToStr(RandomBool) + #32
> RDP> + logic[RandomBool] + #32;
> RDP> end; // for
> RDP> SetLength(Result, Length(Result) - 4);
> RDP> Result := Result + ' ORDER BY ID DESC';
> RDP> end;
>
> RDP> procedure TForm1.Button3Click(Sender: TObject);
> RDP> var sql: string;
> RDP> I: Integer;
> RDP> begin
> RDP> Randomize;
>
>
>
> RDP> for I := 1 to 1000 do // Iterate
> RDP> begin
> RDP> sql := randomSQL;
> RDP> Memo1.Lines.add(sql);
> RDP> IBTransaction1.StartTransaction;
> RDP> IBSQL1.SQL.Clear;
> RDP> IBSQL1.SQL.Add(Double39(sql));
> RDP> IBSQL1.Prepare;
> RDP> IBSQL1.ExecQuery;
> RDP> IBTransaction1.Commit;
>
> RDP> end; // for
>
>
> RDP> end;
> RDP> ------------------
> RDP> Here the results:
> RDP> 1) www.dellapasqua.com/fb15beta2.txt here the 1000 random queries on
the
> RDP> table
> RDP> 2) www.dellapasqua.com/ib7.txt here the 1000 random queries on the
table
> RDP> 3) www.dellapasqua.com/fb15beta2.gif 3,9 msec of average time
> RDP> 4) www.dellapasqua.com/ib7.gif 2,8 msec average time
>
> RDP> SeeYa boys :D
> RDP> Btw... I forgot....I go out and buy IB7 :))) LOL
> RDP> Kidding apart, IMHO it's alright that the commercial version of IB is
> RDP> faster...so don't worry about this 40% performance difference at all!
>
> RDP> Robi
>
>
>
> RDP> To unsubscribe from this group, send an email to:
> RDP> ib-support-unsubscribe@egroups.com
>
>
>
> RDP> Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>