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 <[email protected]> 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)
_______________________________________________ gdal-dev mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/gdal-dev
