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
