7

This question is long please bear me and my problem. SQL Server Query profiler The Sample Query from profiler that GeoServer is running that corresponds to SLD I am using and not the SQL SERVER view I am using is:

SELECT "siteId","Vendor",CAST("SP_GEOMETRY".STSrid as VARCHAR) + ':' 
+ "SP_GEOMETRY".STAsText() as "SP_GEOMETRY" FROM "Data"."sites" 
WHERE ("SP_GEOMETRY".Filter(geometry::STGeomFromText
('POLYGON ((80.12878722280502 28.280198584198256, 
    80.12878722280502 29.559126104578176, 81.58996886322707
 `29.559126104578176, 81.58996886322707 28.280198584198256,
 80.12878722280502 28.280198584198256))', 4326)) = 
1 AND ( UPPER("Vendor") LIKE 'A%'  OR  UPPER("Vendor") LIKE 'B%' ))`

I have used JavaScript as suggested by this to redraw pages at 5 minutes interval. It shows 100% CPU utilization when layer is refreshed. IF I open multiple pages then page just gets locked.

enter image description here

I have Computer with following configuration used for GeoServer, SQL Server and Apache Tomcat. The person who was using it until I demonstrated my project was only me but when I ran this only with 5 person the page just hang(chrome browser) and same thing is happening when I open 5-6 window with same address. I am using GeoServer WMS layer with OpenLayers and Google Maps. The Google map loads very fast but my layer loads very slow 1/2 min. The zooming takes similar time. I have used JAI and all for GeoServer. Database is indexed and the query takes 0 ms when I do it from SSMS. The GeoServer is deployed inside Tomcat and data directory is outside the war. I have problem accessing only map pages and believe the memory requirement is more for maps but I am unable to find actual problem. SQL Server is actually consuming most of the CPU and Tomcat is consuming most of the memory.

enter image description here

The task manager shows following usage:

enter image description here

The perfmon shows following usage:

enter image description here

  • You may wish to ask this of the GeoServer mailing list - geoserver.org/display/GEOS/Mailing+Lists – GIS-Jonathan Apr 23 '12 at 9:23
  • @GIS-Jonathan Did that with the problem I am facing – kinkajou Apr 23 '12 at 9:24
  • Its not recommendable to switch library. But I know an Organization(gov) which used geoserver for large data analysis and rendering. But the web app crashes in their case and they switched to mapserver. If your code base is small try the same performance analysis with mapserver and see the difference. – rkm Nov 19 '12 at 5:38
  • @rashad sql tuning had great impact on my case I used sql server tool to capture the sql that geoserver was running and performed tuning which solved the problem i have to say. – kinkajou Nov 20 '12 at 9:07
  • 2
    @kinkajou could you post your comment about sql tuning as an answer so this thread does not reappear on the unanswered question list? Thanks! – underdark Apr 26 '14 at 22:06
1

This might be due to geometry format requested from SQL Server. If you allow for SQL Server to provide the native format this will be faster than using WKB generated by SQL server.

A way of avoiding this was introduced in Geoserver 2.3.1. Read the release documents for more information.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.