db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From DerbyNovice <clars...@ureason.com>
Subject Re: Statement/PreparedStatement performance difference and problem
Date Wed, 09 Apr 2008 07:16:48 GMT

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

-- 
View this message in context: http://www.nabble.com/Statement-PreparedStatement-performance-difference-and-problem-tp16537511p16580998.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message