In the fresh db case, do you create your indexes before or after the
data has been inserted? From you java repro I'm guessing indexes first,
then inserts?
If I'm not mistaken there's still a possibility for experiencing
outdated statistics when done in this order (as inserts are updates to
the cached/stored data).
Someone please correct me if I'm wrong.
BR,
Thomas
DerbyNovice wrote:
Thanks Knut,
I don't think this is the problem here as there has been no updates,
the same happens if you create the db from scratch and run the program.
Thanks anyway, I will look at the mechanism in play with the
statistics.
Cheers
Knut Anders Hatlen wrote:
DerbyNovice <[EMAIL PROTECTED]> writes:
Why is the prepared statement slower than the unprepared statement?
BACKGROUND
----------
DB as follows:
CREATE TABLE "SA"."UDEVENTDATA"
(
"ID" VARCHAR(100) NOT NULL,
"DOMAINKEYID" VARCHAR(100),
"CREATIONTIME" BIGINT NOT NULL,
"NAME" VARCHAR(32672),
*** AND MORE COLUMNS NOT USED IN THIS
EXAMPLE ****
);
with indices :
CREATE INDEX "SA"."UDSOFTOBJECTDATA_CREATIONTIME_IDX_UDEVENTDATA" ON
"SA"."UDEVENTDATA" ("CREATIONTIME");
CREATE INDEX "SA"."UDSOFTOBJECTDATA_DOMAINKEY_IDX_UDEVENTDATA" ON
"SA"."UDEVENTDATA" ("DOMAINKEYID");
CREATE INDEX "SA"."UDSOFTOBJECTDATA_NAME_IDX_UDEVENTDATA" ON
"SA"."UDEVENTDATA" ("NAME");
and runs as embedded.
DB contains 46000 records with the following properties
DOMAINKEY are 98% same.
NAMEs are all different.
CREATIONTIME 82% different.
Hi,
Derby's optimizer sometimes makes bad decisions because the cardinality
statistics are outdated. You could see if this thread helps you:
http://www.nabble.com/Re%3A-FW%3A-Advice-on-*very*-badly-performing-query-p14140691.html
--
Knut Anders
--
Thomas Nielsen
