Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Message-ID: <4DDCCFEB.1050208@bfh.ch>
Date: Wed, 25 May 2011 11:46:19 +0200
From: =?ISO-8859-1?Q?Stefan_B=FChlmann?= <stefan.buehlmann@bfh.ch>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
 rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Performance Optimization for In-Memory ReadOnly Database
References: <4DDAB8B2.1070205@bfh.ch> <4DDB62E3.202@oracle.com>
In-Reply-To: <4DDB62E3.202@oracle.com>
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit

Hi Kristian,

thank you for your e-mail.  The insertion of the data takes a long time
(60s - 5min). I'm using JDBCTemplates with a SingleConnectionDataSource.
There is no need for multiple connections. I have created three indexed
which make the querying very efficient. However creating those indexes
requires just as much time as it takes to import all the CSV data
without indexes.

-- Stefan

On 24.05.2011 09:48, Kristian Waagan wrote:
> On 23.05.11 21:42, Stefan B�hlmann wrote:
>> Hi together,
>>
>> I'm using derby for a dataprocessing application.
>> SYSCS_UTIL.SYSCS_IMPORT_TABLE is used to import CSV data (around 100mb).
>> The tables contain huge amount of rows (~100k) with some CLOB (max 60mb)
>> columns.
>>
>> The table data is never altered (after the CSV import).
>>
>> Currently I'm using a in-memory database with pageCacheSize of 2000.
>>
>> Are there any other tricks to make querying the data more efficient?
> Hi Stefan,
>
> Do I understand you correctly if I say that you find the insert 
> performance acceptable, but you find querying the inserted data too slow?
>
> If so, is the data accessed by a single connection, or with multiple 
> connections concurrently?
> (i.e. can you run with a lower isolation level or use table locking?)
> Have you created the relevant indexes?
> Are you running a small set of queries repeatedly (using prepared 
> statements), or performing "ad-hoc" queries?
>
> It may be good to post some of the DDL and the queries which perform poorly.
>
>
> Regards,