Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I already have a question on gis.stackexchange.com assuming it may be geoserver the problem but as I go deeper into problem I found that I have a database bottleneck. My SQL server CPU usage is almost 100% when geoserver executes following queries :

SELECT "siteId","Vendor",CAST("SP_GEOMETRY".STSrid as VARCHAR) 
  + ':' + "SP_GEOMETRY".STAsText() as "SP_GEOMETRY" FROM "LiveData"."sites"
         WHERE ("SP_GEOMETRY".Filter(
    geometry::STGeomFromText('POLYGON 
    ((87.16003722182671 27.034666490477207, 
      87.16003722182671 28.328562656235395, 
      88.62121886224784 28.328562656235395, 
      88.62121886224784 27.034666490477207, 
      87.16003722182671 27.034666490477207))', 4326)) = 1 
AND ( UPPER("Vendor") LIKE 'A%'  OR  UPPER("Vendor") LIKE 'B%' ))

--

SELECT "Site_ID","Vendor",CAST("sp_geometry".STSrid as VARCHAR) 
  + ':' + "sp_geometry".STAsText() as "sp_geometry" 
FROM "LiveData"."sites"
WHERE ("sp_geometry".Filter(geometry::STGeomFromText('POLYGON ((
  82.89734190991959 26.99551618775358, 
  82.89734190991959 28.367238076413702,
  84.44641417532948 28.367238076413702, 
  84.44641417532948 26.99551618775358, 
  82.89734190991959 26.99551618775358))', 4326)) = 1 
AND ( UPPER("Vendor") LIKE 'A%'  OR 
 UPPER("Vendor") LIKE 'B%' ))

Now the problem is as I open multiple pages just 4-5 pages the web pages hangs as it has to retrieve data from database.

I have a view that is made spatial by selecting spatial column SP_GEOMETRY from base table:

    CREATE view [LiveData].[sites]
      As
     select
     live.Site_ID as siteId,live.Site_Name,live.Vendor,live.Status,live.MI_SQL_REC_NUM,
     live.MI_STYLE,live.MI_PRINX,live.SP_GEOMETRY
     from baseTble.spatialSites live ,LiveData.liveStatus stat 
where 
live.Site_ID  = stat.siteId and stat.Code in ('x123','y345') and stat.alias = 'Neyman'
share|improve this question
    
What's the structure of the LiveData table? Specifically, what indexes are on it? –  Simon Righarts Apr 22 '12 at 14:56
    
Your query is non-SARGable. You're forcing a scan. It's optimization practices like eliminating query aspects like that which can lead to better performance. –  Thomas Stringer Apr 22 '12 at 16:21
    
While I believe the geometry part of the query is your problem, is there an index on vendor? The second part of your clause could be simplified to AND Vendor LIKE '[A-B]%' (which will work best if there is an index on that column). Also note that using "double quotes" for column aliases is deprecated - you should use 'single quotes' or [square brackets]. –  Aaron Bertrand Apr 22 '12 at 19:36
3  
Ok, well someone should tell them they're doing it wrong. I don't know who told you you shouldn't index OLTP systems, but unfortunately I'm not familiar enough with spatial to recommend any specific index techniques. I will suggest you use proper INNER JOIN syntax instead of FROM a,b... –  Aaron Bertrand Apr 23 '12 at 3:36
2  
For more info on the FROM a, b bit : dba.stackexchange.com/questions/16725/sql-join-syntax-in-ms-sql –  Aaron Bertrand Apr 23 '12 at 4:15
show 7 more comments

2 Answers

up vote 2 down vote accepted

I suggest you try replacing Filter() with STIntersects(). If the queries are auto-produced by the geoserver, you can test this directly in SQL-Server (so at least you know where the problem is):

WHERE
  ( "SP_GEOMETRY".STIntersects( 
      geometry::STGeomFromText(
        'POLYGON ((87.16003722182671 27.034666490477207, 
                   87.16003722182671 28.328562656235395, 
                   88.62121886224784 28.328562656235395, 
                   88.62121886224784 27.034666490477207, 
                   87.16003722182671 27.034666490477207))',
        4326)) = 1 
  AND
     ...
  )

(update)

This page: Filter: One Odd Duck suggests that Filter() is just a fast STIntersects() with the difference that it may return false positives.

It also suggests that the two functions have the exact same (and slow) performance when there is no usable index. So, the best to do is to create a spatial index (and then replace Filter() with STIntersects(), if you want).

The blog (by @Isaac at MSDN) SQL Server Spatial Indexing has 7 nice articles about spatial indexes.

share|improve this answer
    
the time for query is same –  kinkajou Apr 23 '12 at 6:26
    
@Kitex: See my update and the link. –  ypercube Apr 23 '12 at 6:26
    
you were right about index but it already has spatial index. The actual cause was lack of index in base table. IT has made it unbelievably fast. So fast that I forgot the spelling of "unbelievably". Thank you very much. –  kinkajou Apr 23 '12 at 6:41
add comment

I haven't worked much with geospatial types but the fact that it's CPU-intensive isn't all that surprising. If you want to avoid the CPU pegging out at 100% for the server I'd suggest taking a look at your MAXDOP settings at the instance level. The default value is 0 meaning SQL Server will use up all available processors (logical) when processing. If your query is pegging out all your CPUs and causing issue you may want to experiment tweaking your settings. Take a read of @paulrandal's post on MAXDOP: http://www.sqlskills.com/BLOGS/PAUL/post/MAXDOP-configuration-survey-results.aspx

Word of caution: Don't go changing this willy-nilly in production as it WILL affect performance and the last thing you want is to get yelled at/fired because "some guy on internet said it'd work!". Test your settings in development environment first and see if this works for you.

share|improve this answer
3  
Jorge, why at the instance level instead of targeting these specific queries, if they've been identified as the culprit behind the CPU usage? –  Aaron Bertrand Apr 22 '12 at 19:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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