1

I'm running a query to get all locations in a table within a 15 mile radius. The query can take up to 15 seconds.

SELECT * 
FROM rmiukpostcodes
WHERE 3963.191 * ACOS( (SIN(PI()*50.8320711166062/180)*SIN(PI()*latitude/180))+(COS(PI()*50.8320711166062/180)*cos(PI()*latitude/180)*COS(PI()*longitude/180-PI()*-0.207548227491786/180))) < = 5

Is there anything I can do to speed it up?

Dan Is Fiddling By Firelight
  • 5,981
  • 17
  • 77
  • 128
James Privett
  • 1,079
  • 3
  • 15
  • 23

3 Answers3

3

Use spatial data types.

vstrien
  • 2,547
  • 3
  • 27
  • 47
1

You might benefit here from using a computed persisted column. You're causing a table/index scan because of your WHERE clause, I believe.

Capture your execution plan. Try adding the computed column and alter your SELECT query.

  • But the where clause will change every time - There's nothing that can be computed is there? – Matt Fellows Mar 05 '12 at 13:03
  • @MattFellows The OP can surely group the `latitude` and `longitude` portions of the equation into a computed column –  Mar 05 '12 at 13:06
  • I guess he could pre-calculate SIN(PI()*latitude/180) and cos(PI()*latitude/180) - but that is all as everything else is a function of the require lat and long – Matt Fellows Mar 05 '12 at 13:10
0

Your query reads every row in the table. Use some SARGABLE filter criteria and an index.

For example: before you run the query, precompute the max/min lat or long and use it.

WHERE
  codes.Lat BETWEEN @minLat AND @maxLat
  AND YourDistanceCalcHere(codes.Lat, codes.Long)

Then, an index on codes (Lat) would help the query

Community
  • 1
  • 1
Amy B
  • 108,202
  • 21
  • 135
  • 185
  • This is entirely un-useful - you've basically told him to do what he's asked how to do. The OP asked what he could do to make it faster, i assume he knows that filtering the query more would do that, but how can he do that based on his query - i.e. find all rows within n miles of a lat and long. – Matt Fellows Mar 05 '12 at 13:06
  • @MattFellows if you assume he knows sargable criteria, of course my answer is useless. I assume the opposite, directed by the evidence (the query). – Amy B Mar 05 '12 at 14:47
  • You are missing the point - there's no way to restrict that down if the only criteria is distance. There's no evidence of a lack of knowledge of anything. – Matt Fellows Mar 05 '12 at 15:04
  • @MattFellows no way? Maybe no ideal way, but there is a way... see edit. – Amy B Mar 05 '12 at 16:31