hi,
i'm hoping somebody can help me solve a performance issue i'm facing.
specifically, i'm trying to load 1M+ records in an H2 (1.4.193) running in
server mode w/ TCP remote connections, and create a spatial index on a
geometry column using H2GIS spatial extension (1.3.1).
after experimenting w/ prepared statement, both simple and batched w/
different batch sizes, i found that the fastest way to accomplish my
objective is to go through an intermediary CSV file.
the SQL statements i now use look like so:
CREATE TABLE "PUBLIC"."T" ("ID" INT, "C1" DOUBLE, "C2" DOUBLE, "G"
GEOMETRY)
AS SELECT ROWNUM() AS "ID", "C1", "C2", ST_GeomFromText(G, 4326) AS "G"
FROM CSVRead('data.csv');
ALTER TABLE "PUBLIC"."T" ALTER COLUMN "ID" SET NOT NULL;
ALTER TABLE "PUBLIC"."T" ADD PRIMARY KEY ("ID");
CREATE SPATIAL INDEX "T_NDX" ON "PUBLIC"."T"("G");
the G column values in the CSV file look like: POINT(-58.56906526
-34.73766728).
the table loading takes around 20 secs. while the spatial index creation
takes around 30 secs.
i also tried changing most of the options mentioned in (
http://h2database.com/html/performance.html#fast_import):
SET LOG 1;
SET LOCK_MODE 1;
SET UNDO_LOG 0;
unfortunately none of those made a positive impact. in fact times got
worse.
the only parameter that had a positive effect was to...
SET MAX_MEMORY_ROWS 500000;
which shaved off around 3 secs from the 50 secs. combined total for table
creation/import and spatial index creation.
are there better ways to do the job and reduce those delays?
TIA + cheers;
rsn
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
