[Geoserver-users] sqlserver

GeoServer GeoServer User
1

Dear list

I didn’t receive any reply (yet) on my last question, so I might as well try again. Same topic, other question.

But feel free to help me on the first question as well J.

Defining a sqlserver datastore (in geoserver 2.8.1) I have the possibility to

· Check “Force spatial index usage via hints”

· Fill “Table hints”

Two questions

· Is this functioning?

· What input is expected in Table hints? I suppose the name of a spatial index. But what If I use the same store to access more than one table?

Thanks

···

Bart Verbeeck

Afdeling Geodiensten - Dienst IT

T 09 276 14 78 | bart.verbeeck@anonymised.com… | www.agiv.be

Van: Bart Verbeeck [mailto:Bart.Verbeeck@anonymised.com]
Verzonden: maandag 18 januari 2016 10:31
Aan: geoserver-users@lists.sourceforge.net
Onderwerp: [Geoserver-users] sqlserver datastore wfs

Dear list

I have found some posts about sqlserver performance issues when doing wfs requests an large datasets.

Sqlserver chooses a non-optimal queryplan when doing a spatial query with sqlserver native paging.

Without native paging sqlserver is slow when performing non spatial requests

This happens when geoserver sends a query with a combination of “SELECT top xxx” and “SHAPE”.Filter(….).

This kind of query is slow because the queryplan decides not to use the spatial index.

Unfortunately it is not possible to specify a query hint [with(index(indexname))] because sqlserver throws an error when no spatial filter is specified.

· What is the status of this issue?

· Does anyone know a workaround for this problem?

Thanks

Bart Verbeeck

Afdeling Geodiensten - Dienst IT

T 09 276 14 78 | bart.verbeeck@anonymised.com… | www.agiv.be

AGIV e-mail disclaimer: http://www.agiv.be/gis/organisatie/?artid=355

Informatie Vlaanderen e-mail disclaimer: http://www.vlaanderen.be/informatievlaanderen

2

On Tue, Feb 2, 2016 at 9:21 AM, Bart Verbeeck <Bart.Verbeeck@anonymised.com> wrote:

Defining a sqlserver datastore (in geoserver 2.8.1) I have the possibility
to

· Check “Force spatial index usage via hints”

· Fill “Table hints”

Two questions

· Is this functioning?

It is supposed to, did you try and it did not?
It should be adding a hint forcing the usage of the spatial index into the
query.
You can enable "geotools developer logging" logging level in GeoServer to
see the queries that are being generated.

· What input is expected in Table hints? I suppose the name of a
spatial index. But what If I use the same store to access more than one
table?

The input is a table hint that is valid for all tables, such as "nolock".
There is no support for table specific hints, but it could be developed if
you want, here is a guide on the process:
https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer

Cheers
Andrea

--

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

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