I have tables which holds transactions for properties that are stored as lat long pairs. (There are more columns and datapoints then in my example schema).
A common request is to find the transactions that happened within X miles of a particular point, and only retrieve the 5 most recent transactions that happened for each nearby property.
To make this work, I decided to add a view that encapsulates this logic of most recent:
create or alter view dbo.v_example
with schemabinding as
select example_id
,transaction_dt
,latitude
,longitude
,latlong
,most_recent= iif(row_number() over (partition by latitude,longitude order by transaction_dt desc) < 5,1,null)
from dbo.example;
So a query might look like this::
select *
from dbo.v_example
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
and most_recent = 1
Unfortunately, SQL Server does not want to use the spatial index when I query through the view. If I remove schemabinding
and try to add a hint on the view, I get that the query processor cannot create a plan.
How can I encapsulate the logic and still get it to use my spatial index?
Here's a db<>fiddle with example data and plan shapes.
The table is much larger, and it is much slower to scan it then to do a clustered index seek and then find the near by point.
Best Answer
Window functions & Views
Recently I answered a different question regarding views & window functions but not all answers given there apply here.
Two differences are that this example uses
geography
datatypes and filters on the window function. The positive part here is that you are not bound to the window function and could use something likeCROSS APPLY
to get the closest results for onelatitude
&longitude
combination.The previous question + Answer
In short, the window function is calculated before the filtering is applied.
E.G.
Extra information can be found in this blogpost from 2013 by Paul White on window functions and views.
Testing
As a first test, the result is clear when omitting the
ROW_NUMBER()
from the viewResulting in the expected performant query plan.
DB<>Fiddle
But you obviously still want the additional filtering
Keeping the view + window function
You could choose to add the window function afterwards like so:
&
Again resulting in the expected execution plan.
DB<>Fiddle
Using an inline table valued function + window function
Resulting in the query plan you expected.
DB<>Fiddle
Using a CTE + window function
Using
CROSS APPLY
andTOP(4)
without the window functionI used
top(4)
since the resultset was based on< 5
not<= 5
When using the
CROSS APPLY
method there is one more index to be added, to remove the index spool & sort:It goes without saying that you could add the
CROSS APPLY
solution to a view and query it the same way as you used to, an example of this here. All above examples in one DB<>Fiddle here.End solution comment by MichaelB
DB<>Fiddle
&