Slow FDO insert

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

Slow FDO insert

Matteo
Hi, I need to insert a lot of data (up to 100k feature) with FDO on this provider -PostGres -Oracle -SDF -SHP -SqLite I try to execute a while cycle where i create a new insert command, set the property and execute it. It work fine but is very slow. How I can improve the insert performance? Regards Matteo
Reply | Threaded
Open this post in threaded view
|

Re: Slow FDO insert

Hans Milling
Make sure there is no index on your table, and create them when you are done inserting.
I work with MS SQL Server most of the time, but I think that this goes for all databases.
If you can bundle multiple inserts in a single sql command (like 100-1000), this can also speed things up a great deal.

Best regards
  Hans Milling...
Reply | Threaded
Open this post in threaded view
|

Re: Slow FDO insert

Jackie Ng
In reply to this post by Matteo
Try to minimize the amount of memory allocations needed.
 - Don't try to create a new insert command on every iteration. Try to retain the insert command and its property value collection.
 - Also don't clear the property value collection on every iteration, instead flip all the property values to null at the start of every iteration and set the property values from the looping reader as appropriate.

This way you'll only require:
 - 1 allocation for the insert command
 - n allocations for each property value (where n is the number of properties in the looping reader)

This is what I do for FDO Toolbox, and we get respectable bulk insert performance out of most providers.

But like Hans said, some providers (SQL Server) are just plain slow for bulk insertion of data and require temporarily turning off indexing and such.

- Jackie
Reply | Threaded
Open this post in threaded view
|

Re: Slow FDO insert

Matteo
Hi Jackie,
  follow your indication I will do these steps:
1) Connect to the class
2) Create insert command
3) populate property collection
4) for each feature that I must insert populate the values of property collection and execute the insert commad
Is right?

Regards
Matteo
Reply | Threaded
Open this post in threaded view
|

Re: Slow FDO insert

Matteo
In reply to this post by Hans Milling
Hi Hans,
 I do not think that the problem are the indexes because I have the same problem with the SHP format. Anyway, thank you gave me the inspiration
Regards
Matteo
Reply | Threaded
Open this post in threaded view
|

Re: Slow FDO insert

Jackie Ng
In reply to this post by Matteo
For 4) Populate as in:
 - Set all property values in the collection to null, then set based on values from the current feature in the reader

And not:
 - Clear all property values in the collection and populate new property values based on the current feature in the reader.

Besides that, you are correct. That is what I meant.

- Jackie
Reply | Threaded
Open this post in threaded view
|

Re: Slow FDO insert

Matteo
Hi Jackie,
  I follow your instructions but I have not seen improvements. I test it with SHP files.
Have you another ideas?
Regards
Matteo
Reply | Threaded
Open this post in threaded view
|

Re: Slow FDO insert

Martin Morrison
So where are you writing the shape file to?  Network drive?  If so, try writing it local.  

I presume after some of the comments you are writing chunks of data rather than one block at a time.  How large are the chunks?  

What if you changed to a real database (e.g. SQL Server, Oracle, MySQL)?  Is it faster?  

Martin Morrison
Application Engineer
Engineering Design Systems, Inc.
3780 Peters Creek Rd Ext SW
Roanoke, VA  24018
540.345.1410
gis.edsi.com

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Matteo
Sent: Monday, May 06, 2013 11:32 AM
To: [hidden email]
Subject: Re: [fdo-users] Slow FDO insert

Hi Jackie,
  I follow your instructions but I have not seen improvements. I test it with SHP files.
Have you another ideas?
Regards
Matteo



--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Slow-FDO-insert-tp5051522p5051664.html
Sent from the FDO Users mailing list archive at Nabble.com.
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow FDO insert

Matteo
Hello Martin,
   the amount of data is about 500 feature. I have tried both with the provider SHP locally or with a Postgres DB. Compared to the previous version where I created for each entry, the Insert command I gained only 1 second.
The steps that execute are:
1) Create the FDO connection
2) Create the Insert command for each class (about 12 classes)
3) Create the properties collection for each command
4) Read a text file in which each line:
    a) I read the coordinates and values
    b) check the Insert command
    c) Execute the Insert command.

Other ideas to improve performance?
Regards
Matteo
Reply | Threaded
Open this post in threaded view
|

Re: Slow FDO insert

Matteo
Hi,
  I resolved the speed problem!
Thanks a lot.
Matteo
Reply | Threaded
Open this post in threaded view
|

Re: Slow FDO insert

Hans Milling
How did you solve this? Others might want to know the solution for future reference.

Best regards
  Hans Milling...
Reply | Threaded
Open this post in threaded view
|

Re: Slow FDO insert

Martin Morrison
In reply to this post by Matteo
I know you said in a later note that you solved your issue.  

But I have to ask why use MapGuide to load text data?  Why not just load it directly to the desired format and then refresh your map with the new layer?

Martin Morrison
Application Engineer
Engineering Design Systems, Inc.
3780 Peters Creek Rd Ext SW
Roanoke, VA  24018
540.345.1410
gis.edsi.com

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Matteo
Sent: Tuesday, May 07, 2013 5:40 PM
To: [hidden email]
Subject: Re: [fdo-users] Slow FDO insert

Hello Martin,
   the amount of data is about 500 feature. I have tried both with the provider SHP locally or with a Postgres DB. Compared to the previous version where I created for each entry, the Insert command I gained only 1 second.
The steps that execute are:
1) Create the FDO connection
2) Create the Insert command for each class (about 12 classes)
3) Create the properties collection for each command
4) Read a text file in which each line:
    a) I read the coordinates and values
    b) check the Insert command
    c) Execute the Insert command.

Other ideas to improve performance?
Regards
Matteo



--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Slow-FDO-insert-tp5051522p5051934.html
Sent from the FDO Users mailing list archive at Nabble.com.
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow FDO insert

Matteo
This post was updated on .
Hi Martin,
 the slowness was due to a sleep function that I had forgotten on the code.
Regards
Matteo
Reply | Threaded
Open this post in threaded view
|

Re: Slow FDO insert

Martin Morrison
Well that would do it...

Martin Morrison
Application Engineer
Engineering Design Systems, Inc.
3780 Peters Creek Rd Ext SW
Roanoke, VA  24018
540.345.1410
gis.edsi.com


-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Matteo
Sent: Thursday, May 09, 2013 11:56 AM
To: [hidden email]
Subject: Re: [fdo-users] Slow FDO insert

Hi Martin,
 the slowness was due to a sleep that I had forgotten the code.
Regards
Matteo



--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Slow-FDO-insert-tp5051522p5052372.html
Sent from the FDO Users mailing list archive at Nabble.com.
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow FDO insert

zspitzer
is this being bulk insert being done in a transaction?

if there's no transaction, each insert requires everything to be written out to disk


On Fri, May 10, 2013 at 2:36 AM, Martin Morrison <[hidden email]> wrote:
Well that would do it...

Martin Morrison
Application Engineer
Engineering Design Systems, Inc.
3780 Peters Creek Rd Ext SW
Roanoke, VA  24018
540.345.1410
gis.edsi.com


-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Matteo
Sent: Thursday, May 09, 2013 11:56 AM
To: [hidden email]
Subject: Re: [fdo-users] Slow FDO insert

Hi Martin,
 the slowness was due to a sleep that I had forgotten the code.
Regards
Matteo



--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Slow-FDO-insert-tp5051522p5052372.html
Sent from the FDO Users mailing list archive at Nabble.com.
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users



--
Zac Spitzer
Solution Architect / Director
Ennoble Consultancy Australia
+61 405 847 168


_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users