[gdal-dev] GeoPackage Open() Performance

[Kevin Bentley]

Ok, yes, thank you, the code in the trunk made a huge difference. I
appreciated it!

On Thu, Mar 22, 2018 at 3:21 AM, Even Rouault <even.rouault at spatialys.com>
wrote:

> Kevin,
>
> > I've been working on some large GeoPackage files that have a lot of
> layers.
> > One in particular has over 5000 layers. It was taking a good 5+ minutes
> > just to open, so I did some analysis. Here's what I found:
> >
> > By far the biggest hit is in a query to sqlite_master (called
> > from OGRGeoPackageTableLayer::ReadTableDefinition()). This is the
> > particular query (in my dataset, this query generally takes about 90ms):
> >
> > "SELECT type FROM sqlite_master WHERE lower(name) = lower('%q') AND type
> "
> >             "IN ('view', 'table')"
> >
> > The calls to lower means it has to do a full table scan, but there isn't
> an
> > index on sqlite_master, so just removing it doesn't change anything right
> > away (it's still a table scan). I was able to optimize this by creating a
> > temp table from sqlite_master in GDALGeoPackageDataset::Open(), updating
> it
> > so name is all lower case, then creating an index on name. The one time
> > cost of this process was about 100ms, but when I query instead of
> > sqlite_master, it shaved down each query to <1ms. With 5000+ layers, this
> > cut several minutes off time to open a dataset.
>
> You should re-try with GDAL trunk. I've had this same issue a few
> weeks/months
> ago and this should be fixed now per
> https://trac.osgeo.org/gdal/changeset/40474
> (please use latest trunk and not only this patch, as I somehow remember
> later
> fixes were needed)
>
> The reason for this lower case comparison if I remember well was to add
> compatibility with GeoPacakage databases created by ArcMap where there was
> inconsistent case used.
> See https://trac.osgeo.org/gdal/ticket/6916
>
> Even
>
> --
> Spatialys - Geospatial professional services
> http://www.spatialys.com
>



-- 
Kevin Bentley
Cognitics, Inc.
947 E Winding Creek Drive #200
Eagle, ID 83616-7231
(208) 904-3780 (Office)
(208) 890-4472 (Cell)
(866) 922-2037 (Fax)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20180322/13c01d12/attachment-0001.html>