Hi,

Can you check this execution times of this query using normal
Statement with some real parameters? Also, it would be great if you
compare it with performance in some native tool (ibconsole, etc.). It
might happen that it is not related to JDBC driver, but to the
database engine.

Best regards,
Roman Rokytskyy

--- In Firebird-Java@y..., "hlavac2" <egroups@c...> wrote:
> Hi,
>
> I was analyzing performance problem in my application
> and to my great surprise I found out that
> Connection.prepareStatement(String sql) is 50-100 times slower
> than I would expect.
>
> Queries I use are a bit more complex joins (they are automatically
> generated), but hey, they should be slow to execute, not to
prepare????
>
> I use org.firebirdsql.jdbc.FBDriver from FirebirdSQL-1.0_RC1a.zip.
> Database is Firebird 1.0 / WIN 2000sp3 on localhost
> connecting over TCP/IP. I use Sun's JDK1.4.1_01.
>
> Following example query takes about 600ms (!!!) to _prepare_
> on Athlon XP 1700+ machine! Even when there is no data in tables.
> There must be something WRONG.
>
> Any ideas? I'd like to help to fix this.
>
> My query looks like this:
>
>
> SELECT O.OBJECT_ID, O.CLASS_ID,
> J0.REF,
> J1.STRING,
> J2.CONTENT,
> J3.STRING,
> J4.STRING,
> J5.STRING,
> J6.STRING,
> J7.STRING,
> J8.STRING,
> J9.STRING
> FROM POKUS$OBJ O,
> POKUS$DATA_REF J0,
> POKUS$DATA_STR J1,
> POKUS$DATA_FILE J2,
> POKUS$DATA_STR J3,
> POKUS$DATA_STR J4,
> POKUS$DATA_STR J5,
> POKUS$DATA_STR J6,
> POKUS$DATA_STR J7,
> POKUS$DATA_STR J8,
> POKUS$DATA_STR J9,
> POKUS$DATA_REF J10
> WHERE O.CLASS_ID = ?
> AND O.OBJECT_ID = J0.OBJECT_ID AND J0.ATTR_ID = ?
> AND O.OBJECT_ID = J1.OBJECT_ID AND J1.ATTR_ID = ?
> AND O.OBJECT_ID = J2.OBJECT_ID AND J2.ATTR_ID = ?
> AND O.OBJECT_ID = J3.OBJECT_ID AND J3.ATTR_ID = ?
> AND O.OBJECT_ID = J4.OBJECT_ID AND J4.ATTR_ID = ?
> AND O.OBJECT_ID = J5.OBJECT_ID AND J5.ATTR_ID = ?
> AND O.OBJECT_ID = J6.OBJECT_ID AND J6.ATTR_ID = ?
> AND O.OBJECT_ID = J7.OBJECT_ID AND J7.ATTR_ID = ?
> AND O.OBJECT_ID = J8.OBJECT_ID AND J8.ATTR_ID = ?
> AND O.OBJECT_ID = J9.OBJECT_ID AND J9.ATTR_ID = ?
> AND O.OBJECT_ID = J10.REF AND J10.OBJECT_ID = ? AND
J10.ATTR_ID = ?
> ORDER BY J10.IDX
>
>
> ----------- Table structure looks like this: -------------------
>
> SET SQL DIALECT 3;
>
> /* CREATE DATABASE 'localhost:C:\firebird\data\WEB.GDB' PAGE_SIZE
4096
>
> DEFAULT CHARACTER SET WIN1250 */
>
> /* Table: POKUS$ATTR, Owner: WEB */
>
> CREATE TABLE "POKUS$ATTR"
> (
> "ATTR_ID" INTEGER NOT NULL,
> "ATTRNAME" VARCHAR(83) CHARACTER SET WIN1250 NOT NULL,
> PRIMARY KEY ("ATTR_ID"),
> UNIQUE ("ATTRNAME")
> );
>
> /* Table: POKUS$CLASS, Owner: WEB */
>
> CREATE TABLE "POKUS$CLASS"
> (
> "CLASS_ID" INTEGER NOT NULL,
> "CLASSNAME" VARCHAR(83) CHARACTER SET WIN1250 NOT NULL,
> PRIMARY KEY ("CLASS_ID"),
> UNIQUE ("CLASSNAME")
> );
>
> /* Table: POKUS$DATA_FILE, Owner: WEB */
>
> CREATE TABLE "POKUS$DATA_FILE"
> (
> "ID" INTEGER NOT NULL,
> "OBJECT_ID" INTEGER NOT NULL,
> "ATTR_ID" INTEGER NOT NULL,
> "IDX" INTEGER NOT NULL,
> "CONTENT" BLOB SUB_TYPE 0 SEGMENT SIZE 4096,
> PRIMARY KEY ("ID")
> );
>
> /* Table: POKUS$DATA_REF, Owner: WEB */
>
> CREATE TABLE "POKUS$DATA_REF"
> (
> "ID" INTEGER NOT NULL,
> "OBJECT_ID" INTEGER NOT NULL,
> "ATTR_ID" INTEGER NOT NULL,
> "IDX" INTEGER NOT NULL,
> "REF" INTEGER,
> PRIMARY KEY ("ID")
> );
>
> /* Table: POKUS$DATA_STR, Owner: WEB */
>
> CREATE TABLE "POKUS$DATA_STR"
> (
> "ID" INTEGER NOT NULL,
> "OBJECT_ID" INTEGER NOT NULL,
> "ATTR_ID" INTEGER NOT NULL,
> "IDX" INTEGER NOT NULL,
> "STRING" VARCHAR(64) CHARACTER SET WIN1250 COLLATE PXW_CSY,
> PRIMARY KEY ("ID")
> );
>
> /* Table: POKUS$OBJ, Owner: WEB */
>
> CREATE TABLE "POKUS$OBJ"
> (
> "OBJECT_ID" INTEGER NOT NULL,
> "CLASS_ID" INTEGER NOT NULL,
> PRIMARY KEY ("OBJECT_ID")
> );
>
> /* Table: POKUS$STAT, Owner: WEB */
>
> CREATE TABLE "POKUS$STAT"
> (
> "STAT" VARCHAR(83) CHARACTER SET WIN1250 NOT NULL
> );
>
> /* Index definitions for all user tables */
>
> CREATE INDEX "POKUS$DATA_FILE$IX1"
ON "POKUS$DATA_FILE"("OBJECT_ID", "ATTR_ID", "IDX");
> CREATE INDEX "POKUS$DATA_REF$IX1"
ON "POKUS$DATA_REF"("OBJECT_ID", "ATTR_ID", "IDX");
> CREATE INDEX "POKUS$DATA_REF$IX2" ON "POKUS$DATA_REF"("REF");
> CREATE INDEX "POKUS$DATA_STR$IX1"
ON "POKUS$DATA_STR"("OBJECT_ID", "ATTR_ID", "IDX");
> CREATE INDEX "POKUS$DATA_STR$IX2"
ON "POKUS$DATA_STR"("ATTR_ID", "STRING");
> ALTER TABLE "POKUS$DATA_FILE" ADD FOREIGN KEY ("OBJECT_ID")
REFERENCES POKUS$OBJ ("OBJECT_ID");
> ALTER TABLE "POKUS$DATA_FILE" ADD FOREIGN KEY ("ATTR_ID")
REFERENCES POKUS$ATTR ("ATTR_ID");
> ALTER TABLE "POKUS$DATA_REF" ADD FOREIGN KEY ("OBJECT_ID")
REFERENCES POKUS$OBJ ("OBJECT_ID");
> ALTER TABLE "POKUS$DATA_REF" ADD FOREIGN KEY ("ATTR_ID") REFERENCES
POKUS$ATTR ("ATTR_ID");
> ALTER TABLE "POKUS$DATA_REF" ADD FOREIGN KEY ("REF") REFERENCES
POKUS$OBJ ("OBJECT_ID");
> ALTER TABLE "POKUS$DATA_STR" ADD FOREIGN KEY ("OBJECT_ID")
REFERENCES POKUS$OBJ ("OBJECT_ID");
> ALTER TABLE "POKUS$DATA_STR" ADD FOREIGN KEY ("ATTR_ID") REFERENCES
POKUS$ATTR ("ATTR_ID");
> ALTER TABLE "POKUS$OBJ" ADD FOREIGN KEY ("CLASS_ID") REFERENCES
POKUS$CLASS ("CLASS_ID");
>
> CREATE GENERATOR "POKUS$ID";
> ALTER TABLE "POKUS$CLASS" ADD
> CHECK (CLASS_ID > 0);