Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 8BIT
Content-type: text/plain; charset=ISO-8859-1
Date: Tue, 08 Jun 2010 10:41:58 +0200
From: Knut Anders Hatlen <Knut.Hatlen@Sun.COM>
Subject: Re: Derby 10.6 upgrade problem
In-reply-to: <4C6E699970AA394AB516EAB6599241AF0C5290@mdts001.mdte.local>
Sender: Knut.Hatlen@Sun.COM
To: derby-dev@db.apache.org
Message-id: <4C0E0256.6040908@sun.com>
References: 
 <8CEFA5581D3F8249B6F9994644B91A980131DF5A@XBNEWEMA2.energex.com.au>
 <4C6E699970AA394AB516EAB6599241AF0C5290@mdts001.mdte.local>
User-Agent: Mozilla/5.0 (X11; U; SunOS i86pc; en-US;
 rv:1.9.1.9) Gecko/20100510
 Lightning/1.0b1 Thunderbird/3.0.4

On 06/ 8/10 08:57 AM, Ray Gala wrote:
>
> Hi,
>
> We are trying to upgrade our existing Derby DB from version 10.2.2 to
> the latest build 10.6.
> Problem is that our database contains thousands of tables with the
> names starting from "SURVEY_xxxxx" where xxxxx can be any integer from
> 1 to 99999. Upgrade fails on this tables to the point that one cannot
> access any of them, because apparently it takes a very long time to
> open them.
>
> We staged a test in order to see how database handles creation of
> thousands of similarly named tables.
>
> Below we will try to describe how the test was conducted.
>
>
>
> Process
>
> �         Create a new blank database in 10.6
>
>
>
> In a loop from 1 to 10000 { although I only managed to get to 1510
> over night}
>
> �         Created a program that creates a table called SURVEY_X
>
> �          Inserts � hour interval data from the range  2006-08-03
> 15:00:00 to 2009-01-15 00:00:00. 40,000 records.
>
>
>
> And this process repeats.
>
>
>
> Results
>
> �         At the start (10:00 pm) a single cycle of create and insert
> was taking 2 seconds - i.e  Creation of SURVEY_1
>
> �         In the morning 7:00am it had only got to 1510 table and
> insert creations, and was taking  2 minutes - i.e Creation of SURVEY_1510
>
>
> If I change the program (and use it on this database with the current
> 1510 tables in it) to create a table called T_SURVEY_X then it goes
> back to 2 seconds, although I suspect that if I left it running and we
> had 1500 tables called T_SURVEY_X we would have the same problem.
>
> The symptom is also present in SQLWorkbench/J where it takes 2 seconds
> to see table T_SURVEY_0 but 45 seconds to see SURVEY_1510 and even
> after it presents the data it still seems to lock up etc.
>
> So this explains why with 6000 tables that we seem to get no response
> at all.  As you can see from the enclosed log performance starts
> really degrading after a 1000 tables.
>
> Question: Is this a known issue, and can this be fixed?
>

Hi Ray,

This is not a known issue, as far as I'm aware. I'd suggest that you
file a bug report at https://issues.apache.org/jira/browse/DERBY and
upload code that demonstrates the problem, so that it can be investigated.

Thanks,

-- 
Knut Anders