db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Nielsen <Thomas.Niel...@Sun.COM>
Subject Re: Statement/PreparedStatement performance difference and problem
Date Wed, 09 Apr 2008 08:33:35 GMT
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 <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
>>
>>
> 

-- 
Thomas Nielsen

Mime
View raw message