[Geoserver-users] GetFeature request extremely slow after upgrade to GeoServer 2.3.3

GeoServer GeoServer User
1

After upgrading to GeoServer 2.3.3 (from version 2.1.1) a GetFeature request is taking several minutes to complete. When using version 2.1.1 the same request took 300-500ms which is already a little bit slow (definitely for our use case). Certainly if you compare with the time needed to render a map, which is lightning fast!

I have monitored the queries that were executed and in the 2.3.3 version it’s a complete other query (using paging). I have no idea why.

2.3.3 query (which takes 23 seconds to execute on sql server 2008):

SELECT *

FROM (SELECT “MI_Id”,“GeoNode”.STAsBinary() as “GeoNode”,“node_type”,“owner”,“system_type”,“asset_id”,“MI_Style”,“uid”,“user_text_3”, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS _GT_ROW_NUMBER

FROM “dbo”.“ViewIPUT”

WHERE “GeoNode”.Filter(geometry::STGeomFromText(‘POLYGON ((151985.91214561 211273.46959495, 151985.91214561 211283.84555244, 151996.2881031 211283.84555244, 151996.2881031 211273.46959495, 151985.91214561 211273.46959495))’, 31370)) = 1

) AS _GT_PAGING_SUBQUERY

WHERE _GT_ROW_NUMBER <= 10

2.1.1 query (which takes <30ms to execute):

SELECT “MI_Id”,CAST(“GeoNode”.STSrid as VARCHAR) + ‘:’ + “GeoNode”.STAsText() as “GeoNode”,“node_type”,“owner”,“system_type”,“asset_id”,“MI_Style”,“uid”,“user_text_3”

FROM “dbo”.“ViewIPUT”

WHERE “GeoNode”.Filter(geometry::STGeomFromText(‘POLYGON ((150397.35305405 211179.04838181, 150397.35305405 211199.80029678, 150418.10496902 211199.80029678, 150418.10496902 211179.04838181, 150397.35305405 211179.04838181))’, 31370)) = 1

Is it possible to disable paging? Or increase performance? Maybe I did something wrong when upgrading to this version? Although I did nothing more than just copying the data directory from the 2.1.1 version to the 2.3.3 version.

Volg Aquafin op Facebook | Twitter | YouTube | LinkedIN

Disclaimer: zie www.aquafin.be P Denk aan het milieu. Druk deze mail niet onnodig af.

2

On Thu, Jul 18, 2013 at 4:49 PM, Roel De Nijs <roel.denijs@anonymised.com>wrote:

After upgrading to GeoServer 2.3.3 (from version 2.1.1) a GetFeature
request is taking several minutes to complete. When using version 2.1.1 the
same request took 300-500ms which is already a little bit slow (definitely
for our use case). Certainly if you compare with the time needed to render
a map, which is lightning fast!

I have monitored the queries that were executed and in the 2.3.3 version
it’s a complete other query (using paging). I have no idea why.

2.3.3 query (which takes 23 seconds to execute on sql server 2008):

SELECT *

FROM (SELECT "MI_Id","GeoNode".STAsBinary() as
"GeoNode","node_type","owner","system_type","asset_id","MI_Style","uid","user_text_3",
ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS _GT_ROW_NUMBER

            FROM "dbo"."ViewIPUT"

            WHERE "GeoNode".Filter(geometry::STGeomFromText('POLYGON
((151985.91214561 211273.46959495, 151985.91214561 211283.84555244,
151996.2881031 211283.84555244, 151996.2881031 211273.46959495,
151985.91214561 211273.46959495))', 31370)) = 1

            ) AS _GT_PAGING_SUBQUERY

WHERE _GT_ROW_NUMBER <= 10

2.1.1 query (which takes <30ms to execute):

SELECT "MI_Id",CAST("GeoNode".STSrid as VARCHAR) + ':' +
"GeoNode".STAsText() as
"GeoNode","node_type","owner","system_type","asset_id","MI_Style","uid","user_text_3"

FROM "dbo"."ViewIPUT"

WHERE "GeoNode".Filter(geometry::STGeomFromText('POLYGON ((150397.35305405
211179.04838181, 150397.35305405 211199.80029678, 150418.10496902
211199.80029678, 150418.10496902 211179.04838181, 150397.35305405
211179.04838181))', 31370)) = 1

Is it possible to disable paging? Or increase performance? Maybe I did
something wrong when upgrading to this version? Although I did nothing more
than just copying the data directory from the 2.1.1 version to the 2.3.3
version.

Yes, if you look into the store config panel you should find a "disable
native paging" flag

Cheers
Andrea

--

Our support, Your Success! Visit http://opensdi.geo-solutions.it for more
information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

-------------------------------------------------------

3

geowolf wrote

Yes, if you look into the store config panel you should find a "disable
native paging" flag

In the store config panel I have a "use native paging" flag which was not
checked, so I guess it shouldn't have generated these paging queries in the
1st place. When I created a new store (instead of copying the complete data
directory), this flag was checked by default.

After checking, saving, unchecking and saving this flag the queries are
generated without the paging. But we are not yet out of the woods. The
request is now executed in 20 seconds, still slower than the 300-500ms in
the earlier version.

When I take another look at the executed queries I notice that for some
reason the whole table (or view) is queried (before the query with the bbox
filter is executed):
SELECT "MI_Id","GeoNode".STAsBinary() as
"GeoNode","node_type","owner","system_type","asset_id","MI_Style","uid","user_text_3"
FROM "dbo"."ViewIPUT"

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/GetFeature-request-extremely-slow-after-upgrade-to-GeoServer-2-3-3-tp5067365p5067376.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

4

On Thu, Jul 18, 2013 at 5:26 PM, Roel De Nijs <roel.denijs@anonymised.com>wrote:

When I take another look at the executed queries I notice that for some
reason the whole table (or view) is queried (before the query with the bbox
filter is executed):
SELECT "MI_Id","GeoNode".STAsBinary() as

"GeoNode","node_type","owner","system_type","asset_id","MI_Style","uid","user_text_3"
FROM "dbo"."ViewIPUT"

Have no idea there, haven't used sql server in months.
This would require some investigation inside the code, but I have no time
to do that right now
outside of working hours.

Cheers
Andrea

--

Our support, Your Success! Visit http://opensdi.geo-solutions.it for more
information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

-------------------------------------------------------

5

geowolf wrote

Have no idea there, haven't used sql server in months.
This would require some investigation inside the code, but I have no time
to do that right now
outside of working hours.

Today I did all kinds of tests:
- Created 2 layers from scratch to verify it had nothing to do with copying
the data directory (as with the data store issue)
- using other geoserver versions
- changing request version (1.1.0 / 1.0.0)
- changing output format (gml2, json)

Since this afternoon this issue isn't reproducable anymore. And I have
totally no idea why these queries aren't generated anymore. Yesterday and
this morning a request took 20 seconds, now it takes 600-1000ms (and the
complete table query isn't executed anymore). Completely flabbergasted!

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/GetFeature-request-extremely-slow-after-upgrade-to-GeoServer-2-3-3-tp5067365p5067656.html
Sent from the GeoServer - User mailing list archive at Nabble.com.