locked
Why is my spatial query so slow? Can I speed it up? RRS feed

  • Question

  • Hello,

    I followed the instructions on http://www.adathedev.co.uk/2011/01/gb-post-code-geographic-data-load-to.html to create a table in my SQL Server 2008 R2 database containing all UK postcodes (around 1.7 million), and then tried querying the table to find postcodes within a certain distance from a specified postcode.

    The author of that blog said he got 8354 rows in around 500ms. I tried it and got similar results for very small distances (5 miles or so), but found it could take up to 60 seconds if the distance was 200 miles or more. As I want to use this as part of a web site back-end, queries need to be much faster than this. My tests were done on a pretty new Intel i7-2600 CPU @ 3.40 GHz, which is not such a slow machine.

    The spatial index that is created has the default values, ie 16 cells per object, and grid levels all set to medium. I don't really understand these settings, so haven't touched them. Could be that there is scope for improvement here.

    Anyone have any ideas how I can speed this query up? Am I being unrealistic in expecting it to be faster? Thanks for any help you can give.


    FREE custom controls for Lightswitch! A collection of useful controls for Lightswitch developers. Download from the Visual Studio Gallery.

    If you're really bored, you could read about my experiments with .NET and some of Microsoft's newer technologies at http://dotnetwhatnot.pixata.co.uk/

    Wednesday, October 26, 2011 4:23 PM

All replies

  • Hello Mr Yossu,

    Can you confirm that your spatial index is being used?

    Thanks,
    -Isaac


    Isaac Kunen, Microsoft StreamInsight
    Wednesday, October 26, 2011 4:47 PM
  • Hi Isaac, thanks for the reply.

    Yup, my estimated execution plan looks very much like the one shown in that blog post, and includes the following...

    Any other ideas? Thanks again.


    FREE custom controls for Lightswitch! A collection of useful controls for Lightswitch developers. Download from the Visual Studio Gallery.

    If you're really bored, you could read about my experiments with .NET and some of Microsoft's newer technologies at http://dotnetwhatnot.pixata.co.uk/

    Wednesday, October 26, 2011 5:09 PM
  • The UK is not a very big country. I'm guessing that a query to return all those postcodes lying within "200 miles or more" of any location in the UK will return most of the country - say a million records? Are you then sorting those records as well? I think part of the explanation of your performance is not so much in the filtering of the query but the volume of data you're returning in the results.


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, October 26, 2011 7:14 PM
    Answerer
  • Hello,

    I'm actually using this query as part of a bigger one in a stored procedure, where the I get the database IDs of businesses located within nn miles of the supplied postcode. You're right in that the number of postcodes returned can be pretty large, and I did wonder if that would be the problem.

    If it is, do you know of any way of doing such a query more quickly?

    Thanks


    FREE custom controls for Lightswitch! A collection of useful controls for Lightswitch developers. Download from the Visual Studio Gallery.

    If you're really bored, you could read about my experiments with .NET and some of Microsoft's newer technologies at http://dotnetwhatnot.pixata.co.uk/

    Wednesday, October 26, 2011 7:19 PM
  • The best way would be to re-engineer your query so that you don't ever get a million records returned in an intermediate step when you don't actually need that many records! Why are you doing a "within 200 mile" radius query - do you really want to cast your search net that wide?
    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, October 26, 2011 8:03 PM
    Answerer
  • I agree that it would be better to re-engineer the query, but I can't think of a way of doing it without handling so many postcodes along the way. I'm not sure you're right when you say "when you don't actually need that many results." I do need them if that's the only way to work out which businesses are within the distance.

    As far as how wide the search goes, that's really up to the user, not me. If they want to search within 200 miles, they should be able to. The search form allows them to specify the distance, and if they want to choose 200 miles, or even 300 miles, why shouldn't they?

    Which doesn't seem to get us very far unfortunately, unless you can sugest some other way of doing the query so that it runs faster.

    Thanks


    FREE custom controls for Lightswitch! A collection of useful controls for Lightswitch developers. Download from the Visual Studio Gallery.

    If you're really bored, you could read about my experiments with .NET and some of Microsoft's newer technologies at http://dotnetwhatnot.pixata.co.uk/

    Wednesday, October 26, 2011 9:59 PM