I am no kind of an expert, but could you try splitting the import file into
pieces and doing multiple imports concurrently? Although it would make the
import process significantly more complicated, and wouldn't improve the
index creation at all, so perhaps isn't worth the effort.
On 22 September 2017 at 17:57, rsn <raif.s.naf...@gmail.com> wrote:
> 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 h2-database+unsubscr...@googlegroups.com.
> To post to this group, send email to h2-database@googlegroups.com.
> Visit this group at https://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>
--
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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
