Como forço o SQL Server a usar meu índice espacial por meio de uma exibição?

Funções e visualizações da janela

Recentemente, respondi a uma pergunta diferente sobre visualizações e funções da janela, mas nem todas as respostas dadas se aplicam aqui.

Duas diferenças são que este exemplo usa geographytipos de dados e filtros na função de janela. A parte positiva aqui é que você não está vinculado à função window e pode usar algo como CROSS APPLYobter os resultados mais próximos para uma combinação de latitude& .longitude

A pergunta anterior + resposta

Em resumo , a função da janela é calculada antes da aplicação da filtragem.

POR EXEMPLO

where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
and most_recent = 1

Informações extras podem ser encontradas nesta postagem do blog de 2013 de Paul White sobre funções e visualizações da janela.

Teste

Como primeiro teste, o resultado é claro ao omitir o ROW_NUMBER()da visão

create or alter view dbo.v_example2
with schemabinding as
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.example;

set statistics xml on;
select *
from dbo.v_example2
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1

Resultando no plano de consulta de desempenho esperado.

DB<>Fiddle

Mas você obviamente ainda quer a filtragem adicional

Mantendo a função view + window

Você pode optar por adicionar a função de janela posteriormente, assim:

create or alter view dbo.v_example2
with schemabinding as
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.example;

&

set statistics xml on;
;WITH CTE AS
(
select most_recent= iif(row_number() over (partition by latitude,longitude order by transaction_dt desc) < 5,1,null)
,*
from dbo.v_example2
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
)
SELECT
* 
FROM CTE
WHERE most_recent is not null;

Novamente resultando no plano de execução esperado.

DB<>Fiddle

Usando uma função de valor de tabela em linha + função de janela

CREATE FUNCTION dbo.F_Example
(
    @P1 INT

)  
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
    (
SELECT example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent FROM
(
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
WHERE latlong.STDistance(geography::Point(40,-74,4326)) <= 1609.344e1
) AS A
WHERE most_recent= @P1
);

EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.F_Example(@P1)',N'@P1 INT',@P1 = 1

Resultando no plano de consulta que você esperava.

DB<>Fiddle

Usando uma função CTE + janela

;WITH CTE AS
(
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent= row_number() over (partition by latitude,longitude order by transaction_dt desc)
FROM dbo.example
WHERE latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
)

SELECT example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent
FROM CTE2
WHERE most_recent < 5;

Usando CROSS APPLYe TOP(4) sem a função de janela

SELECT example_id
      ,a.transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.example e1
CROSS APPLY(
SELECT TOP(4) transaction_dt
FROM dbo.example e2
WHERE e1.latitude = e2.latitude  and e1.longitude = e2.longitude
GROUP BY latitude,longitude,transaction_dt
ORDER BY transaction_dt desc
) as a
WHERE latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
AND e1.transaction_dt = a.transaction_dt
ORDER BY transaction_dt desc;

Eu usei top(4)desde que o conjunto de resultados foi baseado em < 5não<= 5

Ao usar o CROSS APPLYmétodo, há mais um índice a ser adicionado, para remover o spool de índice e classificar:

CREATE INDEX IX_latitude_longitude_transaction_dt
ON dbo.example(latitude,longitude,transaction_dt);

Escusado será dizer que você pode adicionar a CROSS APPLYsolução a uma visualização e consultá-la da mesma maneira que costumava fazer, um exemplo disso aqui . Todos os exemplos acima em um DB<>Fiddle aqui .

Comentário da solução final de MichaelB

Obrigado Randi, consegui reescrever minha lógica em uma subconsulta select usando a ideia de sua aplicação cruzada. Como um bônus adicional, se eu não mencionar o campo nas colunas que seleciono, não recebo um impacto no desempenho.

DB<>Fiddle

create or alter view dbo.v_example2
with schemabinding as
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent= (
          select a.transaction_dt
          intersect
          select top 5 b.transaction_dt
          from dbo.example b
          where a.latitude=b.latitude
                and a.longitude=b.longitude
          order by transaction_dt desc
      )
from dbo.example a;

&

select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.v_example2
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
      and most_recent is not null