Geography Find All Zips in Radius Performance

• brandonj.terry

  SSC Rookie

  Points: 34

  More actions

  June 6, 2018 at 3:36 pm

  #413767

  Hello All,
  Let me preface this by saying, I'm fairly new to GEOGRAPHY types. Let me paint the scenario.

  We have an application in which the user puts in an origin zipcode and a destination zipcode. When they do that, we need to get all of our orders that originated and ended in the corresponding zipcodes. Our zipcode table has roughly 900k records in it for the US & CAN. In production, we're using a UDF using lat/long and a bunch of math to calculate this. It's super fast, but recently I felt the need to re-vamp it by getting a little more accurate and using the built-in GEOGRAPHY points. So, I added a field in our zipcode table and added a GEOGRAPHY point based on the zip's lat/long. I went ahead and added a spatial index as well. 

  The issue is I'm finding that this new way, using the geography point, seems to be slower. It takes anywhere from 1-4 seconds to return records. I check the execution plan and the spatial index is being used. I have read people talk about having millions of records and it taking under a second. Am I wrong to think that my query should be extremely quick considering I don't have anywhere close to millions of records.

  My thought process was to first put all the valid zipcodes into a temp table, then once I have those, just SELECT from my orders table and inner join on origin and destination zips. It works fine, but I want to speed it up to be under a second. I don't see why it's taking longer than that. 

  Here's the query:
  
    INSERT INTO #zips ( Zipcode, ZipType )
    SELECT oz.Zipcode, 1
    FROM dbo.MyZipcodeTable  z
        JOIN dbo.MyZipcodeTable  oz ON z.Zipcode <> oz.Zipcode
              AND z.Zipcode=@prmOriginZip
              AND oz.Zipcode <> @prmOriginZip
    WHERE z.GeographyPoint.STDistance(oz.GeographyPoint) <= (100* 1609.344) 

      INSERT INTO #zips ( Zipcode, ZipType )
    SELECT oz.Zipcode, 2
    FROM dbo.MyZipcodeTable z
        JOIN dbo.MyZipcodeTable oz ON z.Zipcode <> oz.Zipcode
              AND z.Zipcode=@prmDestZip
              AND oz.Zipcode <> @prmDestZip
    WHERE z.GeographyPoint.STDistance(oz.GeographyPoint) <= (100* 1609.344)


  Here's the execution plan: https://www.brentozar.com/pastetheplan/?id=Bkx84jHgm

  Am I crazy to think that my performance should be wayy faster than it is? During certain parts of the day, I'm getting sluggishness up to 5-10 seconds using these geography points due to multiple users calling the same sproc I suppose. I can't have this. I thought use cases like this is what these points were made for, correct?

  Like I said, I'm fairly new to these points, so any information is appreciated!