Rick, Kristian, Thank you for your replies. The performance improvements with CLOBs are dramatic. I created a record with a CLOB containing about 10 million characters using derby 10.4. The following query took over 6(!) minutes:
SELECT CONTENT FROM MESSAGES WHERE ID = 1 (CONTENT is the CLOB column.) I then installed derby 10.5 and did a soft upgrade. The same query now takes less than three seconds! For these tables we don't use ORDER BY, so hopefully the bug you mentioned won't affect us. Out of curiosity, is there any advantage to doing a hard upgrade? Except for the CLOB performance issue, we're quite happy with version 10.4. Cheers, David Rick Hillegas wrote: >> > Hi David, > > The 10.4 -> 10.5 upgrade changes some of the system metadata on disk > but does not alter any user data. However, if you yourself rewrite > your CLOBs after the upgrade, the on disk format will change. That is > because 10.5 adds some extra header information to on disk CLOBs in > order to deliver the performance improvements you want. I don't > believe that you will see the performance improvements until you > update your CLOBs yourself. David and Rick, What Rick says is correct, except for the last sentence. You will see the general performance improvements for streaming and positioning, amongst other things. What you won't see until you write the CLOBs with the 10.5 format, is the optimizations for operations involving the character length of the CLOB. For small CLOBs this isn't that much of a deal, but if we're talking about CLOBs ranging in size from tens of MB and upwards it will strain your system as the data is decoded to obtain the length. If your client is very conservative and uses large CLOBs, I would suggest to wait for the next maintenance release. The reason I say this, is DERBY-4245 [1]. I don't know if you can tell whether you're going to be affected by this bug or not. The community is discussing the next maintenance release, and I think the current proposal is to release it in July/August. You say you would like to upgrade due to the improved performance with large CLOBs. Are these improvements you have experienced yourself, or based on feedback from the community/others? Regards, -- Kristian -- View this message in context: http://www.nabble.com/What-happens-during-a-database-upgrade--tp23736685p23761970.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
