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);