Today me and the SQL Server DataStore plugin spent some quality time together.
And we definitely had some fun! :-) Here’s an overview of my discoveries. All
these tests are executed using a SQL Server 2008 R2 and the SQL Server
DataStore 13.x version. I can’t perform similar tests against another version
of SQL Server fort he simple reason I don’t have one available :-)
First of all I looked why the “enforcing spatial index” hint isn’t added.to the
query. The code verifies if spatial attributes are used in the filter. If it
is, then the occurences for each spatial attribute in the filter are counted.
Only if there’s one spatial attribute which occurs just once in the filter, the
“enforcing spatial index” hint is added to the query. This is the appropriate
code snippet from SQLServerDialect.getSpatialIndexes:
// we can only apply one index on one condition
if(attribute.getValue() > 1) {
continue;
}
Then I looked at the performance issue we are experiencing. So to render our
(OpenLayers) map 15 GetMap-requests are sent to GeoServer and for every tile
the data of 13 layers (tables) is required. Each request has a CQL_FILTER with
an Intersects. I always used the same scenario to compare the results and it’s
pretty simple: rendering a map on a few different zoom levels. I’ll only focus
on the lowest zoom leve here, because performance is the worst at this zoom
level. I used SQL Server Profiler to gather all sql query related information.
1. Using the original sql server plugin
Using the original sql server plugin, this kind of query is generated and
executed:
SELECT "uid","node_type","owner","geom".STAsBinary() as "geom"
FROM "dbo"."spatial_node"
WHERE ("geom".Filter(geometry::STGeomFromText('POLYGON ((155859.87158203
202811.63085938, 155859.87158203 203077.25537109, 156125.49609375
203077.25537109, 156125.49609375 202811.63085938, 155859.87158203
202811.63085938))', 31370)) = 1
AND "geom".STIntersects(geometry::STGeomFromText('POLYGON ((155859.87158203
202811.63085938, 155859.87158203 203077.25537109, 156125.49609375
203077.25537109, 156125.49609375 202811.63085938, 155859.87158203
202811.63085938))', 31370)) = 1
AND "geom".Filter(geometry::STGeomFromText('POLYGON ((155854.164805411
202805.92408276122, 155854.164805411 203082.96214770878, 156131.202870369
203082.96214770878, 156131.202870369 202805.92408276122, 155854.164805411
202805.92408276122))', 31370)) = 1)
So in the WHERE clause you have 3 spatial functions: one Filter and one
STIntersects (from the CQL_FILTER parameter); and another Filter (from the BBOX
parameter). Each of these queries take up to 250-300ms. And the average time to
execute a GetMap-request (at this zoom level) is 3450ms.
2. Only STIntersects (CQL_FILTER) and Filter (BBOX)
For this test, I changed the sql server plugin so that it only generates a
STIntersects (from the CQL_FILTER parameter) and a Filter (from the BBOX
parameter). The query that’s generated and executed looks like:
SELECT "uid","node_type","owner","geom".STAsBinary() as "geom"
FROM "dbo"."spatial_node"
WHERE ("geom".STIntersects(geometry::STGeomFromText('POLYGON ((155859.87158203
202811.63085938, 155859.87158203 203077.25537109, 156125.49609375
203077.25537109, 156125.49609375 202811.63085938, 155859.87158203
202811.63085938))', 31370)) = 1
AND "geom".Filter(geometry::STGeomFromText('POLYGON ((155854.164805411
202805.92408276122, 155854.164805411 203082.96214770878, 156131.202870369
203082.96214770878, 156131.202870369 202805.92408276122, 155854.164805411
202805.92408276122))', 31370)) = 1)
So in the WHERE clause you now have only 2 spatial functions. Each of these
queries execute in 100-200ms! That’s already a huge performance boost. And
that’s already noticable if you look at the average time to execute a
GetMap-request (at this zoom level): it takes only 1900ms. So almost 50% faster.
3. Only STIntersects (CQL_FILTER)
At this zoom level the polygon in the CQL_FILTER parameter is almost the same
as the BBOX polygon (that’s because our application generates the polygon in
the CQL_FILTER by intersecting the user’s restricted geographical area with the
BBOX parameter). So the Filter condition (from the BBOX parameter) becomes
redundant in this case. For this test, I changed the sql server plugin so that
it only generates a STIntersects (from the CQL_FILTER parameter). The query
that’s generated and executed looks like:
SELECT "uid","node_type","owner","geom".STAsBinary() as "geom"
FROM "dbo"."spatial_node"
WHERE ("geom".STIntersects(geometry::STGeomFromText('POLYGON ((152938.00195313
202811.63085938, 152938.00195313 203874.12890625, 154000.5 203874.12890625,
154000.5 202811.63085938, 152938.00195313 202811.63085938))', 31370)) = 1
AND 1 = 1)
So in the WHERE clause you now have only 1 spatial function. Each of these
queries execute in 20-50ms! Which is again a huge performance improvement.
Needless to say that the average time to execute a GetMap-request (at this zoom
level) will be less as well: only 1400ms.
Conclusion: so it seems for SQL Server 2008 R2 it would be a good thing to get
rid of the additional Filter condition if you have an Intersects in the
CQL_FILTER (and probably for the other spatial functions as well).
Kind regards,
Roel De Nijs
Senior Java Developer
Van: [email protected] [mailto:[email protected]] Namens Andrea Aime
Verzonden: woensdag 30 september 2015 19:08
Aan: Roel De Nijs <[email protected]>
CC: [email protected]
Onderwerp: Re: [Geoserver-users] GetMap-request with CQL_FILTER too sl
On Wed, Sep 30, 2015 at 7:02 PM, Roel De Nijs
<[email protected]<mailto:[email protected]>> wrote:
Hi Andrea,
Luckily this issue only affects a very minor group of users. That’s also why
this issue could be left untouched for half a year. If the majority of our
users would have been affected, that would never have been possible :-D
In an ideal world, that would be true... in practice an issue is fixed when
there is someone willing to spend time or funds on it, which does not always
correlate well with the number of users affected, believe me ;-)
So it seems that when you use a CQL_FILTER there is some kind of additional
processing of the returned features from the database. And if no features are
returned, then no additional processing is required (which would explain why
some of the GetMap requests finish rather quickly). Do these thoughts make any
sense at all?
I'm not aware of any extra processing. But beware, running queries manually and
via JDBC can sometimes lead to very different
results. Especially when done like GeoServer does, but setting a fetch size to
avoid getting millions of records on the client
side in a single shot and going OOM as a result
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.
-------------------------------------------------------
________________________________
Volg Aquafin op Facebook<https://www.facebook.com/AquafinNV> |
Twitter<https://twitter.com/aquafinnv> |
YouTube<http://www.youtube.com/channel/UCk_4P5BJ-MtEEDCkCsR_KqQ?feature=mhee> |
LinkedIN<http://www.linkedin.com/company/aquafin/products>
Disclaimer: zie www.aquafin.be<http://www.aquafin.be> P Denk aan het milieu.
Druk deze mail niet onnodig af.
------------------------------------------------------------------------------
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
