locked
What type of response times for an STInterscets() select can I expect ? RRS feed

  • Question

  • I have a query that does an STIntersects() that is taking 9 minutes 30 seconds to return results. Seems way too long for the info I'm selecting.  So I was wondering, if given what I'm trying to do, if this is the best timings I can hope for.

    I have a customer table that has a 'location_goem' column with the 'point' of the customer as a geometry data type.  I have another table with polygons. I'm trying to see which customers are contained in the given polygons. Very typical.

    The 'points' table has 5 million rows.  It's indexed on day of service (dos_id) and location_geom.  There are 1.6 million rows for the day of service I'm searching on.

    The 'polygon' table has about 200 polygons that I'm searching against.  Each polygon is 2 - 3 miles in area.

    I know this is a real general description but given this and everything else being 'OK', should it be taking over 9 minutes to find all the customers that are in the given polygons?

    The end result is that there are 75,000 'customers' selected for 101 polygons.

    I can post additional information on the particulars but is 9 minutes or so all I can expect?  Not seconds response time?

    I started doing the tuning on this but my initial attempts haven't brought down the response times significantly.

    This is being done in SQL Server 2008 with the latest service pack installed.

    TIA

    Jay
    Thursday, August 19, 2010 4:02 AM

Answers

  • Well I managed to get my query down from 9 minutes and 30 seconds to 6 seconds !!!!  It was the Bounding Box that was the problem.

    I originally had it at (-112, 33, -111, 33.87) when I created the index and the CREATE SPATIAL INDEX accepted it.

    When I ran sp_help_spatial_geometry_index , that errored out complaining about the Bounding Box.  I then changed it to (-112.818604, 33.330528, -111.434326, 34.079962)  and both my query and sp_help_spatial_geometry_index ran faster. 

    I did try Filter() too instead of STIntersects(). Filter ran in 66 seconds but gave me a results set of 500,000 rows instead of the expected 80,000 rows.

    Thanks again Tanoshimi for the "Hints".

    Jay

    • Marked as answer by JayViz Friday, August 20, 2010 9:05 PM
    Friday, August 20, 2010 9:05 PM

All replies

  • It's the million dollar question again..... ;)

    I'm going to tentatively say that you can better performance than 9 minutes but frankly this is just a wild guess - you might, for example, have forgotten to mention the fact that you're running this on a Pentium I machine with only 256Mb of RAM!

    And the million dollar answer, as always, concerns your spatial index.

    - What are its parameters? BOUNDING_BOX / CELLS_PER_OBJECT etc. - on what basis have you chosen these?

    - Is the spatial index actually being used in your query? (Check the execution plan)

    - What is the primary/internal efficiency you're getting? (Check sp_help_spatial_geometry_index)

    - What if you choose Filter() rather than STIntersects() - does that speed up your query (which would suggest that it's the secondary filter being slow)

     


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, August 19, 2010 7:25 AM
    Answerer
  • Thanks for the ideas Tanoshimi.  

    My BOUNDING_BOX is:

    CREATE SPATIAL INDEX sdx_location_geom ON if_carrier_points_brad ( location_geom )

    USING GEOMETRY_GRID 

    WITH (

      BOUNDING_BOX = (-112, 33, -111, 33.87),   -- (X-min,Y-min) and (X-max,Y-max)

      GRIDS = (

        LEVEL_1 = MEDIUM,

        LEVEL_2 = MEDIUM,

        LEVEL_3 = MEDIUM,

        LEVEL_4 = MEDIUM), 

      CELLS_PER_OBJECT = 16

    )

    I have those X and Y co-ordinates based on the city of Phoenix, AZ where my polygons are at.

    - Is the spatial index actually being used in your query? Yes, the spatial index is being used based on the execution plan.

    -What is the primary/internal efficiency you're getting?  I'll need to check this out.

     Filter() rather than STIntersects()   I'll need to check this out too.

    Thansk again for the thoughts.

    Jay

    Friday, August 20, 2010 3:48 PM
  • Well I managed to get my query down from 9 minutes and 30 seconds to 6 seconds !!!!  It was the Bounding Box that was the problem.

    I originally had it at (-112, 33, -111, 33.87) when I created the index and the CREATE SPATIAL INDEX accepted it.

    When I ran sp_help_spatial_geometry_index , that errored out complaining about the Bounding Box.  I then changed it to (-112.818604, 33.330528, -111.434326, 34.079962)  and both my query and sp_help_spatial_geometry_index ran faster. 

    I did try Filter() too instead of STIntersects(). Filter ran in 66 seconds but gave me a results set of 500,000 rows instead of the expected 80,000 rows.

    Thanks again Tanoshimi for the "Hints".

    Jay

    • Marked as answer by JayViz Friday, August 20, 2010 9:05 PM
    Friday, August 20, 2010 9:05 PM
  • It seems that the problem was not the accuracy of values.

    Probably the original bounding box didn't cover all the geometries, since the second box is bigger. If you're going to insert new geometries in the table, give the bounding box a bigger margin. 

    Saturday, August 21, 2010 2:19 PM
  • OK.  I figured it was the lack of numbers to the right of the decimal in 3 of the values  (-112, 33, -111, 33.87)  in the original Bounding Box.

    Jay

    Saturday, August 21, 2010 4:41 PM