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,
