Subscribe to this thread
Home - General / All posts - Speed optimazation: importing records from Manifold 9 to Postgres DB
gjsa100 post(s)
#23-Feb-18 01:17

Have just been importing a very large drawing (5.8 million features) into a Postgres DB. This was started as a simple drag-and-drop operation in the project pane. The drawing table has only one geom field (simple polygons) and two integer fields.

Results/Performance:

  • The drawing uploaded into Postgres at a consistent speed of almost 200 records/s.
  • The entire upload has taken about 8 hours (5,800,000 / 200 records/s).

Questions:

1. Does the drag-and-drop import operation in Manifold 9 to Postgres use the COPY method (faster) or the INSERT method (much slower)?

2. Is the solid limit for the import/upload speed of 200 rows/s expected performance based on 1., or is there some optimization that could be achieved?

Note: The internet connection speed to the Postgres DB is definitely not the limiting factor here. It has a solid 2500 KB/s upload capacity and the 200 records/s transfer rate achieved required only about 75 KB/s.

Final word - I prefer to import into Postgres from Manifold 9 because:

  • Postgres shp2pgsql utility is out of the question (2 GB shapefile limit)
  • GDAL ogr2ogr is probably not faster overall - would need to export from Manfold into a recognised vector format before importing (in itself not that fast) = double handling.

gjsa100 post(s)
#23-Feb-18 01:36

(PS: Someone please edit my typo in the subject line. This forum platform is restrictive!)

cartomatic

905 post(s)
#23-Feb-18 08:41

Just a guesswork, so may be totally wrong...

i guess to manifold all the db datasources are external, hence you cannot simply execute a COPY command as it makes the db server read/write files from/to locally accessible files and it could fail.

there is an alternative - you can use psql \copy that will a file on the clientside and wire it to the db server

having in mind manifold only needs some dlls to connect as a db client, it does not use psql internally.

also, if you're importing to an existing table, it is worth to drop indexes prior to the op and then recreate them.


maps made easy - www.cartomatic.pl || www.cartoninjas.net

adamw


10,447 post(s)
#23-Feb-18 14:42

We use INSERT, not COPY. We cannot use COPY because it is file-based and in the general case we run on a different system than the server (cartomatic is correct). We do have some ways to improve the performance though and we will do so.

adamw


10,447 post(s)
#23-Feb-18 15:06

Forgot to ask: are you using 32-bit or 64-bit 9 (and so, drivers for PostgreSQL)? If you are using 32-bit, try 64-bit.

How big are the geometry values (how many coordinates on average)? What is their type in 9: GEOM or GEOMWKB?

artlembo


3,400 post(s)
#23-Feb-18 15:48

do you have 8? It would be interesting to see how 8 does the export to Postgres.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.