Ema Bakar created SIS-639:
-----------------------------
Summary: Embedded EPSG database has no index on
"Usage"(OBJECT_TABLE_NAME, OBJECT_CODE), so creating each CRS scans the whole
table
Key: SIS-639
URL: https://issues.apache.org/jira/browse/SIS-639
Project: Spatial Information Systems
Issue Type: Improvement
Components: Referencing
Affects Versions: 1.7
Environment: sis-referencing 1.7 + sis-embedded-data 1.7 (embedded
EPSG dataset 13.101, HSQLDB 2.7.4), OpenJDK 11.0.25
Reporter: Ema Bakar
Creating many CRS objects from the embedded EPSG database is much slower than
on 1.4. Found while upgrading the same application from 1.4 (see SIS-629).
Creating every projected CRS once:
{code:java}
var codes =
CRS.getAuthorityFactory("EPSG").getAuthorityCodes(ProjectedCRS.class);
for (String code : codes)
{
CRS.forCode("EPSG:" + code); // 67 of the 5416 codes throw; ignored here
}
{code}
takes *33.9 s* for 5416 codes (5349 created).
Stack samples taken during the loop show almost all the time in HSQLDB, under:
{noformat}
org.apache.sis.referencing.factory.sql.EPSGDataAccess.executeMetadataQuery(EPSGDataAccess.java:1010)
org.apache.sis.referencing.factory.sql.EPSGDataAccess.getUsages(EPSGDataAccess.java:1301)
org.apache.sis.referencing.factory.sql.EPSGDataAccess.createProperties(EPSGDataAccess.java:1449)
org.apache.sis.referencing.factory.sql.EPSGDataAccess.createCoordinateReferenceSystem(EPSGDataAccess.java:1966)
{noformat}
That is this query, run once per object:
{code:sql}
SELECT EXTENT_CODE, SCOPE_CODE FROM "Usage" WHERE OBJECT_TABLE_NAME=? AND
OBJECT_CODE=?
{code}
In the {{SpatialMetadata}} database in {{sis-embedded-data-1.7.jar}}, the
{{"EPSG"."Usage"}} table (15,869 rows) has only its primary key
({{USAGE_CODE}}) and the two foreign-key indexes ({{EXTENT_CODE}},
{{SCOPE_CODE}}). Nothing covers {{OBJECT_TABLE_NAME}}/{{OBJECT_CODE}}, so
HSQLDB reads the whole table from disk for every lookup.
*Workaround / verification*
On a copy of the embedded database, I added:
{code:sql}
CREATE INDEX IX_USAGE_OBJECT ON "EPSG"."Usage"(OBJECT_TABLE_NAME, OBJECT_CODE)
{code}
and pointed {{SIS_DATA}} at it. The same loop then takes *5.2 s*, with the same
5349 CRS created. Building coordinate operations improves the same way.
*Expected*
The embedded database ships with an index on {{"Usage"(OBJECT_TABLE_NAME,
OBJECT_CODE)}}, or SIS creates one when it installs the database. Databases
that SIS creates from the EPSG scripts might need the same index.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
