SHP TO SQLITE Spatial Very Slow

It can be indices that do this, or the file system/media being written to is slow.

For checking the impact of indices, compare by writing to an entirely new SQLite Spatial database with no index creation options. Similarly the impact of the downstream file system can be compared by instead writing it to a fast local storage.

Also note that the index creation Yes\\No dropdown on the field parameters in the Writer create the indices before writing the features instead of afterwards. It is instead recommended to create indices in the Writers by using CREATE INDEX statements in the SQL to Run after Write parameter.

The SQLite Spatial FDO Writer in latest FME does have index creation options.

On my PC, just tested the Writer. It wrote 750,000 complex polygons (Cadastre with a very large number of attributes) to a Spatial FDO database on my local SSD in 59 seconds. So the underlying Writer itself looks to be high performance.

Can you screenshot how you've configured the Writer? I suspect it is something in there? The data schema you've shown doesn't look complex and shouldn't be the issue.

Thanks, a couple of questions:

• What were the settings in the User Attributes Tab?
• What is the full path of the SQLite database being written? Is this is a local drive or a network drive?

Apart from that, you don't need "Overwrite Table" set to "Yes" when the "Overwrite Existing Database" option is turned on, but that shouldn't make any difference.

Possibly to dig down a bit further is looking over the Translation Log to see at what steps the time is being consumed. It seems a little strange that creating a new SQLite database vs writing to an existing one would create any difference. The process to create a new DB with SQLite takes virtually no time at all, unless something like an overzealous background virus protection program takes a lot of time with the new file before deciding to allow it to be accessed further.

If the Translation Log "Options" button is clicked, there is a option to "Log Timestamp Information". This should show the relative times between creating the database file, when FME begins to write to the file, and how long it takes.

Perhaps it is something as silly as the Writer not maintaining an active connection to the SQLite database when the Writer has to "wait" on upstream transformers and it keeps disconnecting and re-connecting on every few features, causing a cumulative time overhead in having to perform extra connection/disconnection steps numerous times (SQLite writing is by doing database transactions: Anything that wants to write data to an SQLite file first has to connect to it and this step takes a little bit of processing time).

A workaround may be to insert a FeatureHolder immediately upstream of the Writer to prevent FME from writing any features to SQLite until all the features are ready to be written. FeatureHolder will stop the Writer from doing anything until everything like LineCombiner is finished first.

Another potential problem is that the geometry coming out of LineCombiner may not be "simple" enough to directly write to fdo_line format in SQLite spatial format, and the Writer is churning in having to convert the geometry from one form to another, even though in Inspector the geometries "look" like simple 2D lines. I've recently experienced a little bit of craziness in FME in that some of the geometries coming out of LineCombiner seem to have some hidden geometry traits that upset other downstream transformers.

The "hack" I used that seemed to work was post-processing all the geometries with GeometryRefiner as a final step, which in theory shouldn't have done anything, but for unexplained reasons allowed the downstream transformers to function properly again, even though Inspector was indicating that it didn't change anything on any geometry properties or traits that were exposed.

The behaviour seems somewhat inconsistent, so yes that certainly makes it difficult to diagnose.

So just to check whether missed anything:

1. The Workflow Writes OK if the Writer does not create a new SQLite file, but instead writes to an existing SQLite file (or was this instead, append to an existing table?)
2. The Workflow Writes OK if the Writer does not use the output from LineCombiner
3. The Workflow Write OK if instead use the SHP file Writer

That is indeed difficult to pin down. 1 and 2 don't seem to have anything in common.

If it is to do with the Writer struggling with the FME feature geometry in some way, then perhaps try switching the geometry type to "fdo_null", and seeing if that makes any difference to narrow down possibilities. It there is no difference then it suggests it is not to do with the geometries themselves.

Otherwise, really clutching at straws, but perhaps FME and SQLite are struggling with SQLite's dynamic data typing. Each value in SQLite has its own data type, the setting in the Table definition is just a "loose" data typing, it actually doesn't get decided on a per value level until something attempts to commit the data in a transaction, but the application writing the data can optionally provide "hints" to SQLite via the API in order to help it decide if it is a string, a numeric etc. Whether the FME Writer uses this functionality for or not is unknown. LineCombiner might upset the carrying forward of the workspace schema into the Writer and that as a result the Writer keeps trying to hint at data types that SQLite disagrees with and overrules causing time delay. But that doesn't really explain why Workaround 1 seems to be successful, and I don't think I've seen a real-world instance of this happening for applications writing to SQLite despite it being a possibility. Other things that can be tried in this space is setting the Table Schema in the Writer Manually and providing your own hints through setting the Field data types in the drop downs.

Perhaps trialling yet another variation of Workaround 1 , this can be emulated by setting the Writer to instead use blank SQLite database file with a zero record table already setup as a Template file in the Writer Parameters which effectively adds a step of copying an existing SQLite file to a new file path, and then opening this and writing to it. This overrides the Writer's default behaviour of getting the API to generate a new SQLite Spatial database from scratch. I can't see why this should work or make any difference, but if that is what your testing suggests then who knows!

Also, I'm not sure how flexible you are with downstream format, but SpatiaLite is somewhat functionally equivalent and tends to be jointly supported by GIS packages that can read alternately either SQLite Spatial or SpatiaLite. The geometry schema and schema metadata tracking is a little different, but at their core they store data basically the same way, although the support for one format vs the other can vary depending on what application are needing to use it in.

The best solution I've come up with, is to use AutoCAD Map or QGIS to convert the original shapefiles to SQLite, then do my FME file processing. Using this method, the SQLite writer has the normal speed. Its an extra step, but for the half hour + I save its not a big deal.