On Mon, Oct 22, 2018 at 2:03 PM Richard Hipp <d...@sqlite.org> wrote:
> On 10/22/18, Zoltan Demeter <dzo...@gmail.com> wrote: > > I have a rather poor performance of 50 k inserts per second. The data > > to be inserted is precalculated and passed to the loop. I am using the > > same logic as above, so the loop is wrapped in a transaction and I use > > a parametrized statement. > [...]. Have you compared the RTree insert performance in SQLite against > other systems? > Is SQLite a serious laggard here? Do we need to work on it? > I wrote the wiki-entry below 10 years ago (March 19, 2008 :)), after evaluating Oracle Spatial 11g (using OCI and C++). That was a 55x slowdown, while you report a 24x slowdown with SQLite. I bet things have improved Oracle-side since then, but I don't know. Still, that's only a 2x difference between the two in terms of slowdown, so in the same ballpark I'd say. FWIW. --DD ------------------ Issue#1: Number-based The SDO_GEOMETRY stores all coordinates with the Number SQL type (OCINumber struct in OCI), which is 21 bytes long and base-10 based. When binding floating point values of type float, Oracle tends to add noise around the 6th significant digit... Issue#2: Slow inserts with Spatial Indexes When an SDO_GEOMETRY column has a Spatial index associated to it (usually the case, otherwise you can't use Spatial operator on it), this can slow down inserts quite a bit... For example, inserting 10,000 2D points using an OCI array-bind insert takes only 0.2 seconds without a Spatial index, but a whopping 11 second with one. It appears that the single array-insert if followed by 10,000 individual inserts into an internal Spatial table, probably later use to update the index on commit (which itself adds only around 1 second when processing rows 1,000 at a time, the default, or just 0.35 second 10,000 at a time). No news from the Spatial team since reporting this issue. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
