Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

projinfo with EPSG:7842 slow #1913

Closed
nyalldawson opened this issue Feb 5, 2020 · 3 comments · Fixed by #1914
Closed

projinfo with EPSG:7842 slow #1913

nyalldawson opened this issue Feb 5, 2020 · 3 comments · Fixed by #1914
Labels

Comments

@nyalldawson
Copy link
Contributor

@nyalldawson nyalldawson commented Feb 5, 2020

Running

projinfo EPSG:7844

is basically instant. But running

projinfo EPSG:7842

pegs out the CPU for about 5 seconds before exporting the WKT. Is this expected?

@nyalldawson nyalldawson added the bug label Feb 5, 2020
@kbevers
Copy link
Member

@kbevers kbevers commented Feb 5, 2020

Is this expected?

Probably not :-) I can't explain what's going on, but I can confirm the behaviour on my computer with 6.3.0

@rouault
Copy link
Member

@rouault rouault commented Feb 5, 2020

I don't get this order of mangitude of difference:

$ time PROJ_LIB=data src/projinfo EPSG:7842
[...]
real	0m0.062s

$ time PROJ_LIB=data src/projinfo EPSG:7842
[...]
real	0m0.161s

That is with sqlite 3.11. But if I use more recent sqlite versions, I also replicate the massive slowness. So likely a regression in the sqlite3 query optimizer.

The added slowness is due to having PROJ.4 string output, which involves researching transformations to EPSG:4326. For EPSG:7842 which is a geocentric CRS, the search is more complicated

Need to investigate what's going on

@rouault
Copy link
Member

@rouault rouault commented Feb 5, 2020

OK, so what is slow is one of the 4 requests similar to the trivial below one (a bit simplified to remove bounding box tests which rely on a user-defined sqlite3 function):

SELECT
v1.table_name as table1,
v1.auth_name AS auth_name1,
v1.code AS code1,
v1.deprecated AS deprecated1,
v2.table_name as table2,
v2.auth_name AS auth_name2,
v2.code AS code2,
v2.deprecated AS deprecated2,
a1.south_lat AS south_lat1,
a1.west_lon AS west_lon1,
a1.north_lat AS north_lat1,
a1.east_lon AS east_lon1,
a2.south_lat AS south_lat2,
a2.west_lon AS west_lon2,
a2.north_lat AS north_lat2,
a2.east_lon AS east_lon2,
ss1.replacement_auth_name AS replacement_auth_name1,
ss1.replacement_code AS replacement_code1,
ss2.replacement_auth_name AS replacement_auth_name2,
ss2.replacement_code AS replacement_code2
FROM coordinate_operation_view v1
JOIN coordinate_operation_view v2
JOIN geodetic_crs g_source
JOIN geodetic_crs g_v1s
JOIN geodetic_crs g_v1t
JOIN geodetic_crs g_v2s
JOIN geodetic_crs g_v2t
JOIN geodetic_crs g_target
ON g_v1s.auth_name = v1.source_crs_auth_name
AND g_v1s.code = v1.source_crs_code
AND g_v1t.auth_name = v1.target_crs_auth_name
AND g_v1t.code = v1.target_crs_code
AND g_v2s.auth_name = v2.source_crs_auth_name
AND g_v2s.code = v2.source_crs_code
AND g_v2t.auth_name = v2.target_crs_auth_name
AND g_v2t.code = v2.target_crs_code
AND g_v1t.datum_auth_name = g_v2s.datum_auth_name
AND g_v1t.datum_code = g_v2s.datum_code
AND g_v1s.datum_auth_name = g_source.datum_auth_name
AND g_v1s.datum_code = g_source.datum_code
AND g_v2t.datum_auth_name = g_target.datum_auth_name
AND g_v2t.datum_code = g_target.datum_code
LEFT JOIN supersession ss1
ON ss1.superseded_table_name = v1.table_name
AND ss1.superseded_auth_name = v1.auth_name
AND ss1.superseded_code = v1.code
AND ss1.superseded_table_name = ss1.replacement_table_name
LEFT JOIN supersession ss2
ON ss2.superseded_table_name = v2.table_name
AND ss2.superseded_auth_name = v2.auth_name
AND ss2.superseded_code = v2.code
AND ss2.superseded_table_name = ss2.replacement_table_name
JOIN area a1
ON v1.area_of_use_auth_name = a1.auth_name
AND v1.area_of_use_code = a1.code
JOIN area a2
ON v2.area_of_use_auth_name = a2.auth_name
AND v2.area_of_use_code = a2.code
WHERE g_source.auth_name = 'EPSG'
AND g_source.code = '7842'
AND g_target.auth_name = 'EPSG'
AND g_target.code = '4978'
AND v1.auth_name = 'EPSG'
AND v2.auth_name = 'EPSG'

The slowness is apparently due to the ON ss1.superseded_table_name = v1.table_name and ON ss2.superseded_table_name = v2.table_name conditions.

rouault added a commit to rouault/PROJ that referenced this issue Feb 5, 2020
Fixes OSGeo#1913

AuthorityFactory::createBetweenGeodeticCRSWithDatumBasedIntermediates() issued
a complex SQL query that pushes the SQLite3 query plan optimizer to its limits.
Was working reasonably with sqlite 3.11, but not with later versions.
So put less constraints in the main query and do post-processing checks and
auxiliary requests to avoid such issues.

For some unknown reason, this slightly slows down a bit execution time of the
whole test_cpp_api binary (~ 10%), but couldn't come with something better,
despite trying many variations of the main SQL query. It seems that in the
general case the non-filter LEFT JOIN on the supersession table helped,
except on this EPSG:7842 case.
rouault added a commit to rouault/PROJ that referenced this issue Feb 5, 2020
Fixes OSGeo#1913

AuthorityFactory::createBetweenGeodeticCRSWithDatumBasedIntermediates() issued
a complex SQL query that pushes the SQLite3 query plan optimizer to its limits.
Was working reasonably with sqlite 3.11, but not with later versions.
So put less constraints in the main query and do post-processing checks and
auxiliary requests to avoid such issues.

For some unknown reason, this slightly slows down a bit execution time of the
whole test_cpp_api binary (~ 10%), but couldn't come with something better,
despite trying many variations of the main SQL query. It seems that in the
general case the non-filter LEFT JOIN on the supersession table helped,
except on this EPSG:7842 case.
rouault added a commit to rouault/PROJ that referenced this issue Feb 5, 2020
Fixes OSGeo#1913

AuthorityFactory::createBetweenGeodeticCRSWithDatumBasedIntermediates() issued
a complex SQL query that pushes the SQLite3 query plan optimizer to its limits.
Was working reasonably with sqlite 3.11, but not with later versions.
So put less constraints in the main query and do post-processing checks and
auxiliary requests to avoid such issues.

For some unknown reason, this slightly slows down a bit execution time of the
whole test_cpp_api binary (~ 10%), but couldn't come with something better,
despite trying many variations of the main SQL query. It seems that in the
general case the non-filter LEFT JOIN on the supersession table helped,
except on this EPSG:7842 case.
@rouault rouault closed this in #1914 Feb 6, 2020
rouault added a commit to rouault/PROJ that referenced this issue Feb 6, 2020
Fixes OSGeo#1913

AuthorityFactory::createBetweenGeodeticCRSWithDatumBasedIntermediates() issued
a complex SQL query that pushes the SQLite3 query plan optimizer to its limits.
Was working reasonably with sqlite 3.11, but not with later versions.
So put less constraints in the main query and do post-processing checks and
auxiliary requests to avoid such issues.

For some unknown reason, this slightly slows down a bit execution time of the
whole test_cpp_api binary (~ 10%), but couldn't come with something better,
despite trying many variations of the main SQL query. It seems that in the
general case the non-filter LEFT JOIN on the supersession table helped,
except on this EPSG:7842 case.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants