Sqlite features vs h2

[dan.hatfield]

I'm looking at using H2 for a Java application.
My prototype was built using Sqlite3 and basically involves quickly
creating a large in memory database from a series of on disk
databases.

Does H2 have anyway of instructing the database engine to load the
entire database into memory at startup?

With SQLite, I essentially do the following:
1) create a new in memory database
2) attach to the persisted database via the attach database command
3) create my table structure in the new in memory database
4) insert into the new in memory tables via select * from on disk
tables

This executes extremely quickly with SQLite and I can get an in-memory
version of the database in very short amount of time.

[dan.hatfield]

Never mind.
Answered here - http://groups.google.com/group/h2-database/browse_thread/thread/aa8a34a274a4a507

[Thomas Mueller]

Hi,

> Does H2 have anyway of instructing the database engine to load the
> entire database into memory at startup?

Is this database also a H2 database? Is it static? If yes, you could
create a SQL script (SCRIPT TO...) and load this script (RUNSCRIPT
FROM...).

Otherwise, you can do the same steps:

> 1) create a new in memory database

Connection conn = DriverManager.getConnection("jdbc:h2:mem:test");

> 2) attach to the persisted database via the attach database command

conn.createStatement().execute("LINK_SCHEMA('targetSchema', 'driver',
'url', 'user', 'password', 'schema')");

The target database could be any JDBC database. If its H2:

targetSchema: for example 'LINK'
driver: NULL
url: for example jdbc:h2:~/test
user, password: for example 'sa'
schema: 'PUBLIC'

> 3) create my table structure in the new in memory database
> 4) insert into the new in memory tables via select * from on disk
> tables

Could be made in two steps:

CREATE TABLE ABC;
INSERT INTO ABC SELECT * FROM LINK.ABC;

Or in one step (faster):

CREATE TABLE ABC AS SELECT * FROM LINK.ABC;

If you don't need the links to the persistent database any more:

DROP SCHEMA LINK

I hope this helps,
Thomas

[dan.hatfield]

Yes very helpful.
I'll give it a whirl.
If you are interested in the performance of this for 1/2 gig of data
or so, I can post my results.

On Aug 15, 2:03 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

> If you are interested in the performance of this for 1/2 gig of data
> or so, I can post my results.

Sure. If possible could you also post how to reproduce it.

Thomas

[dan.hatfield]

Thomas,
I'm attempted to load a portion of the database for starters from a
CSV file (approximately 82MB).
I did this like so:

private void createScript() throws Exception {
Class.forName("org.h2.Driver");

Connection conn =
DriverManager.getConnection("jdbc:h2:mem:test");

Statement stat = conn.createStatement();
stat.execute("create table bene_uom(SEQ_UNIQ_KEY BIGINT,LINE
VARCHAR(3),UNIT_OF_MEASURE_TYPE VARCHAR(3),UNIT_OF_MEASURE_VALUE
VARCHAR(5))");
stat.execute("insert into bene_uom select * from CSVREAD('d:\
\eclipse\\workspace\\accum\\bene_uom.csv')");
System.gc();
Thread.currentThread().sleep(10000);
conn.close();
}

It loaded...but required about 1GB of memory...I was simply
monitoring
the memory in Windows.
Any thoughts on this? Is this what'd you expect?

On Aug 16, 4:37 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

[dan.hatfield]

This table consists of ~3.5 million rows at approximately 20 bytes of
data per row.

> > Thomas- Hide quoted text -
>
> - Show quoted text -