agreed. not worth the effort but thanks for the suggestion. On Saturday, September 23, 2017 at 10:06:51 AM UTC+10, Kerry Sainsbury wrote: > > 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 <[email protected] <javascript:>> > 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 [email protected] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> 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 [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.
