slow response loading table for spatial db

Posted in 2013

Summary

KARL OLIVER found loading shape files into an 11.50 spatial database from ArcCatalog slow, about 900 rows a minute. Art Kagel asked how rows were inserted and the FetBufSize. Karl did not know, then reported a speedup from 4 hours to 10 minutes by moving the data to the ArcCatalog server and setting MINBUFSIZE 409600 and MAXBUFSIZE 819200 in giomgr.defs, pointing to network speed.

Auto-generated by Claude from the posts below — may be imperfect; read the full thread.

Topics: Versions, Editions & End-of-Life

KARL OLIVER — Mon, 07 Oct 2013 22:32:50 — source: IIUG Forums & Mailing Lists

Calling spatial db experts please ! IBM Informix Dynamic Server Version 11.50.UC9 OS Name Linux OS Release 2.6.18-308.el5 OS Node Name hvgreyst25 OS Version #1 SMP Fri Jan 27 17:17:51 EST 2012 OS Machine x86_64 arcCatalogue 10 loading shape files the db is buffered. Wriing data to smart blob using arc to load table sql as follows ast parsed SQL statement : INSERT INTO test:test.PlaceFC (objectid, foreigndbid, x, y, shape) VALUES (?,?,?,?, SE_MPolyFromSde(?,?,2)) there are no index on table loading about 900 rows per min any ideas how I can make this go faster. the operating system is not stressed only using 8% cpu.

↪ replying to KARL OLIVER

Art Kagel — Mon, 07 Oct 2013 23:02:09 — source: IIUG Forums & Mailing Lists

Questions: - How are you inserting the records? -- EXECUTE the prepared statement above? -- INSERT CURSOR? - Are you using the default FetBufSize or did you increase it? Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Oct 7, 2013 at 6:32 PM, KARL OLIVER <karl.oliver@maf.govt.nz> wrote: > Calling spatial db experts please ! > > IBM Informix Dynamic Server Version 11.50.UC9 > OS Name Linux > OS Release 2.6.18-308.el5 > OS Node Name hvgreyst25 > OS Version #1 SMP Fri Jan 27 17:17:51 EST 2012 > OS Machine x86_64 > > arcCatalogue 10 > loading shape files > > the db is buffered. > Wriing data to smart blob > using arc to load table sql as follows > > ast parsed SQL statement : > INSERT INTO test:test.PlaceFC (objectid, foreigndbid, x, y, shape) VALUES> > (?,?,?,?, SE_MPolyFromSde(?,?,2)) > > there are no index on table > > loading about 900 rows per min > > any ideas how I can make this go faster. > the operating system is not stressed only using 8% cpu. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d042ac0e8f34b5204e82ea3d1

↪ replying to Art Kagel

KARL OLIVER — Tue, 08 Oct 2013 00:21:36 — source: IIUG Forums & Mailing Lists

ARCataloue is doing the insert no idea how it does it except for the query i posted

↪ replying to KARL OLIVER

KARL OLIVER — Wed, 09 Oct 2013 00:50:42 — source: IIUG Forums & Mailing Lists

ok i got the load to go faster by 1 moving data to be loaded to server where arcCatalog is 2. changing params to MINBUFSIZE 409600 # minimum buffer size > 4096 MAXBUFSIZE 819200 # maximum buffer size > MINBUFSIZE in giomgr.defs time went from 4 hours to 10 mins. clearly we have an issue with our network speed - try telling our network staff that !