[Geoserver-users] slow perfromance with SQL parametric view

GeoServer GeoServer User
1

Hi all,
I have a very complex query in a SQL server DB from which to grab features
for delivering a WFS.
I tried using SQL parametric view in Geoserver but unfortunately the
performance are very poor, while the query is fast enough if executed inside
SQL environment.
So the SQL part is improved at its best, the target now is to find a
suitable solution for delivering my
data as WFS but with good performance.
Do you have any suggestions about solving such issue?
One thing I thought is to split my data in the DB so that the access would
be faster, but of course I would need to instruct GeoServer about what data
to deliver according the WFS parameters, maybe I could use Geoscript for
shifting all logic about which data to consider. Is geoscript able to build
a WFS service?

Thanks a lot in advance.

Below you can find the log of geoserevr about one call with SLQ parametric
view:

2015-03-23 10:33:00,482 INFO [org.geoserver.wfs] -
Request: getServiceInfo
2015-03-23 10:33:00,484 INFO [org.geoserver.wfs] -
Request: getFeature
    service = WFS
    version = 1.0.0
    baseUrl = http://localhost:8080/geoserver/
    query[0]:
        typeName[0] = {eurdep.jrc.ec.europa.eu}testMaxAvg
    outputFormat = application/json
    resultType = results
2015-03-23 10:33:00,486 INFO [org.geoserver.wfs.json] - about to encode JSON
2015-03-23 10:33:00,486 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
2015-03-23 10:33:00,488 DEBUG [org.geotools.jdbc] - SELECT count(*) FROM
(SELECT * FROM
[JRC_EURDEP_PUBLIC_DM].[dbo].[fn_LastANDMaxDoseRateHourlyAverages_3]
('2015-02-13','2015-03-13 13:00')
) as "vtable"
2015-03-23 10:33:01,246 DEBUG [org.geotools.util] -
InterpolationConverterFactory can be applied from Strings to Interpolation
only.
2015-03-23 10:33:01,247 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
2015-03-23 10:33:01,247 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
2015-03-23 10:33:01,248 DEBUG [org.geotools.jdbc] - SELECT top 100000
"StationCode","Country","GeoLocation".STAsBinary() as
"GeoLocation","LastAverage","MaxAverage" FROM (SELECT * FROM
[JRC_EURDEP_PUBLIC_DM].[dbo].[fn_LastANDMaxDoseRateHourlyAverages_3]
('2015-02-13','2015-03-13 13:00')
) as "vtable"
2015-03-23 10:33:01,900 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/slow-perfromance-with-SQL-parametric-view-tp5194921.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

2

On Mon, Mar 23, 2015 at 11:37 AM, defelix <defelice.luca@anonymised.com> wrote:

Hi all,
I have a very complex query in a SQL server DB from which to grab features
for delivering a WFS.
I tried using SQL parametric view in Geoserver but unfortunately the
performance are very poor, while the query is fast enough if executed
inside
SQL environment.

Just to be sure, have you tried the full query GeoSever is emitting,
including the
the wrapping portion that limits the results to the first 100000 instances?

SQLServer is know to have a pariticularly poor query optimizer (at least
compared
to PostgreSQL/Oracle), that easily gets tricked by the small changes in the
query structure
and/or requires manual optimizer hints to be added to the query.

One of the options that we have in recent versions of GeoServer is to
disable the native paging, as that in some cases results in queries that run
slower than extracting the full data set.
You will find it as "native paging", try to disable it, it should make the
"select top 100000" part go away, maybe that will solve your issue.
There is also the option to force certain query hints in all queries,
and/or to force
the usage of the spatial index, which often SQLServer decides not to use.

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 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

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

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

3

Hi Andrea,
Thanks a lot for your quick answer.
I have just disabled the "native paging", but still that line of the query
(I mean "select top 100000" part) remains there. I tried to use exactly the
same query in SQL server, I mean:
SELECT count(*) FROM (SELECT * FROM
[JRC_EURDEP_PUBLIC_DM].[dbo].[fn_LastANDMaxDoseRateHourlyAverages_3]
('2015-02-13','2015-03-13 13:00')
) as "vtable"

SELECT top 100000 "StationCode","Country","GeoLocation".STAsBinary() as
"GeoLocation","LastAverage","MaxAverage" FROM (SELECT * FROM
[JRC_EURDEP_PUBLIC_DM].[dbo].[fn_LastANDMaxDoseRateHourlyAverages_3]
('2015-02-13','2015-03-13 13:00')
) as "vtable2"

and that seems to be slower, so most likely removing the SELECt TOP 100000
should improve the things.
Is there any other way for removing native paging?
Regards, Luca

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/slow-perfromance-with-SQL-parametric-view-tp5194921p5194967.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

4

On Mon, Mar 23, 2015 at 2:35 PM, defelix <defelice.luca@anonymised.com> wrote:

Hi Andrea,
Thanks a lot for your quick answer.
I have just disabled the "native paging", but still that line of the query
(I mean "select top 100000" part) remains there. I tried to use exactly the
same query in SQL server, I mean:
SELECT count(*) FROM (SELECT * FROM
[JRC_EURDEP_PUBLIC_DM].[dbo].[fn_LastANDMaxDoseRateHourlyAverages_3]
('2015-02-13','2015-03-13 13:00')
) as "vtable"

SELECT top 100000 "StationCode","Country","GeoLocation".STAsBinary() as
"GeoLocation","LastAverage","MaxAverage" FROM (SELECT * FROM
[JRC_EURDEP_PUBLIC_DM].[dbo].[fn_LastANDMaxDoseRateHourlyAverages_3]
('2015-02-13','2015-03-13 13:00')
) as "vtable2"

and that seems to be slower, so most likely removing the SELECt TOP 100000
should improve the things.
Is there any other way for removing native paging?

No... I've just checked the current code, if you turn off the native paging
in the store
UI there should be no select top generated.
Just to make sure, was the change saved, and did you check if maybe you
have multiple stores, and haven't changed the setting in one that is not
actually serving the layer you're using in your tests?

It should not be required, but have you tried restarting GeoServer?

Finally, which version of GeoServer are you using?

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 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

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

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

5

Sorry Andrea, my mistake,
now there is anymore the "SELECT TOP 100000" part but still remains the
first line of the query:
SELECT count(*) FROM (SELECT * FROM
[JRC_EURDEP_PUBLIC_DM].[dbo].[fn_LastANDMaxDoseRateHourlyAverages_3]
('2015-02-13','2015-03-13 13:00')

Unfortunately this line is an overhead for the performance issue, is there
any chance to get rid of it?
regards, Luca

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/slow-perfromance-with-SQL-parametric-view-tp5194921p5194977.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

6

On Mon, Mar 23, 2015 at 3:04 PM, defelix <defelice.luca@anonymised.com> wrote:

Sorry Andrea, my mistake,
now there is anymore the "SELECT TOP 100000" part but still remains the
first line of the query:
SELECT count(*) FROM (SELECT * FROM
[JRC_EURDEP_PUBLIC_DM].[dbo].[fn_LastANDMaxDoseRateHourlyAverages_3]
('2015-02-13','2015-03-13 13:00')

Unfortunately this line is an overhead for the performance issue, is there
any chance to get rid of it?

Hmm... no, there is no easy way.
The "sql views" are currently implemented using a subquery in the from
clause, as you
can see, in order to:
* avoid having to parse/understand the query, which might be complex and
using db specific syntax
* pass down all the filters that we got in the wms/wfs/sld to the database

In order to cut away the first line we'd have to do a full parse of the
query itself, recognize its
bits, and see that we can just replace the select * with a select count(*)

Even in simple queries like yours that's not a trivial amount of work... if
you are interested
in doing the work I can give you pointers to which parts of the code should
be modified,
for the sql parsing there are 2-3 open source libraries that can parse
common sql, but they
will likely break the moment some specific db stuff is used, so this
eventual new code path
would have to be made optional.

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 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

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

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

7

I understood Andrea,
Actually I would like to limit the effort for delivering the WFS which I
need, so I wouldn't consider to work on customization of the SQL parsing
work, rather I would go towards a different logic on the DB.
In any case thanks a lot.
Luca

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/slow-perfromance-with-SQL-parametric-view-tp5194921p5195002.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

8

Is it possible to merge the 2 following requests into a single request?

And if so how ?

https://kgeo.knowsley.gov.uk/geoserver/Geostore/wms?SERVICE=WMS&VERSION=1.1.1&REQUEST=GetFeatureInfo&FORMAT=image%2Fpng&TRANSPARENT=true&QUERY_LAYERS=Geostore%3Av_fmn_planning_apps&LAYERS=Geostore%3Av_fmn_planning_apps&TILED=true&INFO_FORMAT=application%2Fjson&propertyName=refval%2Crecieve_date&FEATURE_COUNT=10&X=64&Y=109&WIDTH=256&HEIGHT=256&SRS=EPSG%3A27700&STYLES=&BBOX=346112%2C392768%2C346176%2C392832

https://kgeo.knowsley.gov.uk/geoserver/Geostore/wms?SERVICE=WMS&VERSION=1.1.1&REQUEST=GetFeatureInfo&FORMAT=image%2Fpng&TRANSPARENT=true&QUERY_LAYERS=Geostore%3Aconservationarea&LAYERS=Geostore%3Aconservationarea&TILED=true&INFO_FORMAT=application%2Fjson&propertyName=conname%2Crefval%2Cdate_created&FEATURE_COUNT=10&X=64&Y=109&WIDTH=256&HEIGHT=256&SRS=EPSG%3A27700&STYLES=&BBOX=346112%2C392768%2C346176%2C392832

thanks

Mark Ismail
This e-mail and any attachments are confidential. It may contain privileged information and is intended for the named recipient(s) only. It must not be distributed without consent. If you are not one of the intended recipients, please notify the sender immediately and do not disclose, distribute, or retain this email or any part of it and do not take any action based on it.

Unless expressly stated, opinions in this email are those of the individual sender, and not of Knowsley MBC. Legally binding obligations can only be created for, or be entered into on behalf of, Knowsley MBC by duly authorised officers or representatives.

Knowsley MBC excludes any liability whatsoever for any offence caused, any direct or consequential loss arising from the use, or reliance on, this e-mail or its contents. We believe but do not warrant that this e-mail and any attachments are virus free. You must therefore take full responsibility for virus checking and no responsibility is accepted for loss or damage arising from viruses or changes made to this message after it was sent. Knowsley MBC reserves the right to monitor and/or record all e-mail communications through its network in accordance with relevant legislation.

9

On Mon, Mar 23, 2015 at 6:01 PM, Ismail, Mark <mark.ismail@anonymised.com>
wrote:

Is it possible to merge the 2 following requests into a single request?

And if so how ?

https://kgeo.knowsley.gov.uk/geoserver/Geostore/wms?SERVICE=WMS&VERSION=1.1.1&REQUEST=GetFeatureInfo&FORMAT=image%2Fpng&TRANSPARENT=true&QUERY_LAYERS=Geostore%3Av_fmn_planning_apps&LAYERS=Geostore%3Av_fmn_planning_apps&TILED=true&INFO_FORMAT=application%2Fjson&propertyName=refval%2Crecieve_date&FEATURE_COUNT=10&X=64&Y=109&WIDTH=256&HEIGHT=256&SRS=EPSG%3A27700&STYLES=&BBOX=346112%2C392768%2C346176%2C392832

https://kgeo.knowsley.gov.uk/geoserver/Geostore/wms?SERVICE=WMS&VERSION=1.1.1&REQUEST=GetFeatureInfo&FORMAT=image%2Fpng&TRANSPARENT=true&QUERY_LAYERS=Geostore%3Aconservationarea&LAYERS=Geostore%3Aconservationarea&TILED=true&INFO_FORMAT=application%2Fjson&propertyName=conname%2Crefval%2Cdate_created&FEATURE_COUNT=10&X=64&Y=109&WIDTH=256&HEIGHT=256&SRS=EPSG%3A27700&STYLES=&BBOX=346112%2C392768%2C346176%2C392832

So... if I read correctly, you're trying to get two different sets of
properties, from two different layers? (maybe next time be explicit about
it, don't make people guess)

If so, you should get a close equivalent by passing both layers in the
layer list and query layer list, and setting
propertyName=(refval,reveice_date)(connname,refval,date_created)
(mind, I did not have time to test it, but it will hopefully work).

I say close equivalent, because if the first layer has more than 10
features in the pixel, you will only get features from the first layer. And
if you set the feature count to 20, and the first
layer has more than 20, you will still get only features from the first
layer. And so on, there is no way to say "X features from the first, and Y
features from the second"

Hope this helps

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 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

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

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

10

Hi all,
I am again here because I am still struggling with the parametric SQL view
performance.
I would like to know if it is possible to use geoscript with python or
javascript for building dynamically a WFS/WMS new layer.
If it were possible I would build a script which would take data from a web
service that I developed [querying SQL and outputting json] and would
dynamically add a new WFS service.
What about you?
thanks in advance
Luca

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/slow-perfromance-with-SQL-parametric-view-tp5194921p5246355.html
Sent from the GeoServer - User mailing list archive at Nabble.com.