Justin,
Thanks for the confirmation. Looks like it's time to switch to postgis for
the time being or to start learning java ;-)
Dominik
From: Justin Deoliveira [mailto:[email protected]]
Sent: Tuesday, November 16, 2010 5:13 AM
To: Dominik Mikiewicz
Cc: [email protected]
Subject: Re: [Geoserver-users] Poor performance with SqlServer data source
Hi Dominik,
Good to know where the issue is. Indeed the plan is to implement wkb reading
support for sql server. It is just that no developer has had a project or
mandate that funded the feature. So until that happens (or an eager
developer decides to submit a patch ;) ) it will probably remain the way it
is.
-Justin
2010/11/15 Dominik Mikiewicz <[email protected]>
Hi folks,
We had a bank Holiday over here so it took me a while to come back to this.
I have done some tests as suggested and the problem appears to lay on the
sql server side or to be more precise I should add geoserver (or the
extension) here as well.
Basically geoserver pulls something like CAST(Geometry.STSrid() as varchar)
+ ':' Geometry.STAsText() (which actually seems to do the same as
ToString()).
The spatial condition is evaluated using Geometry.Filter() and is fast
enough. In my case though STAsText() seems to be the problematic part. It
accounts for something like 80 to 90% of the time needed to execute the
query.
Selecting Geometry as binary is very quick on the other hand.
I have read a bit more on using postgis with geoserver and found out there
is a 'wkb enabled' option when specifying such data source. Are there any
plans to make geoserver read native wkb of sql server? (apologies if it is
with the 2.1 beta - I had problems connecting to sql server with this
version, though I believe it may have been caused by the varchar problem
mentioned on the list a couple of days ago)
Dominik
From: Justin Deoliveira [mailto:[email protected]]
Sent: Wednesday, November 10, 2010 4:32 PM
To: Dominik Mikiewicz
Cc: Rahkonen Jukka; [email protected]
Subject: Re: [Geoserver-users] Poor performance with SqlServer data source
Hi Dominik,
A useful thing to do would be to turn on GEOTOOLS_DEVELOPER logging which
will output the raw sql statements being executed against the database. Take
the one that gets output when you make the map request and execute it
directly against the database, or do an explain on it. If it is fast we know
the problem lies elsewhere in geoserver. If not the query should give us a
better idea about why it is slow.
Also what version of geoserver are you using?
-Justin
2010/11/10 Dominik Mikiewicz <[email protected]>
Jukka,
This is what I thought too, but as you said the table is very small and
should not cause problems even if it did not have indexes.
Retrieving the data from sql server by other apps is fast, no problems with
that.
I have found a post by Sjoerd here:
http://blog.geoserver.org/2008/11/10/146/ and was wondering whether this
still could be the case or not. The table I am trying to use has an index on
the primary key and also a spatial index.
Of course 5m points is nothing compared to something like 300k points in
polygons, though so far I seemed to be unable to make it perform better.
dom
From: Rahkonen Jukka [mailto:[email protected]]
Sent: Wednesday, November 10, 2010 2:08 PM
To: Dominik Mikiewicz; [email protected]
Subject: Re: [Geoserver-users] Poor performance with SqlServer data source
Hi,
Almost always the reason for slow response of a database driven system is in
poor indexes. However, your table is so small that it should be fast even
without indexes. Anyhow, check that you have a spatial index in place and
normal indexes for attributes which are used in queries. Try to capture the
SQL queries which are sent to the database and send them with plain SQL with
timing on. It is also possible that your client is slow, not Geoserver.
What client do you use? I prefer to use hand written WMS requests and web
browser for first tests, wget and jmeter when I want to capture some
comparable numbers about the speed.
-Jukka Rahkonen-
_____
Lähettäjä: Dominik Mikiewicz [mailto:[email protected]]
Lähetetty: 10. marraskuuta 2010 14:47
Vastaanottaja: [email protected]
Aihe: [Geoserver-users] Poor performance with SqlServer data source
Hi list,
I am trying to set up an SQL Server data source for my geoserver. All goes
fine, I can connect to the server, pull the data and publish a layer.
The problems start when I try to view my layer in a browser. This is
terribly slow even though I have only roughly 8k polygons in a table.
A temporary solution is to use shp but the system will have to provide
means for updating the data on a daily basis and file based storage does not
seem to be the best thing
Any ideas on how to improve performance with sql server behind appreciated.
Thanks
Dominik
----------------------------------------------------------------------------
--
The Next 800 Companies to Lead America's Growth: New Video Whitepaper
David G. Thomson, author of the best-selling book "Blueprint to a
Billion" shares his insights and actions to help propel your
business during the next growth cycle. Listen Now!
http://p.sf.net/sfu/SAP-dev2dev
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
--
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.
----------------------------------------------------------------------------
--
Centralized Desktop Delivery: Dell and VMware Reference Architecture
Simplifying enterprise desktop deployment and management using
Dell EqualLogic storage and VMware View: A highly scalable, end-to-end
client virtualization framework. Read more!
http://p.sf.net/sfu/dell-eql-dev2dev
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
--
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.
------------------------------------------------------------------------------
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3.
Spend less time writing and rewriting code and more time creating great
experiences on the web. Be a part of the beta today
http://p.sf.net/sfu/msIE9-sfdev2dev
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
