You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Stefan Bühlmann <st...@bfh.ch> on 2011/05/23 21:42:42 UTC
Performance Optimization for In-Memory ReadOnly Database
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?
Kind Regards
Stefan
Re: Performance Optimization for In-Memory ReadOnly Database
Posted by Stefan Bühlmann <st...@bfh.ch>.
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,
Re: Performance Optimization for In-Memory ReadOnly Database
Posted by Kristian Waagan <kr...@oracle.com>.
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,
--
Kristian
>
> Kind Regards
>
> Stefan