db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Statement/PreparedStatement performance difference and problem
Date Tue, 08 Apr 2008 09:19:05 GMT
DerbyNovice <clarsson@ureason.com> 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

Mime
View raw message