Tenho uma tabela grande (dezenas a centenas de milhões de registros) que dividimos por motivos de desempenho em tabelas ativas e de arquivo, usando um mapeamento de campo direto e executando um processo de arquivo todas as noites.
Em vários lugares do nosso código, precisamos executar consultas que combinam as tabelas ativas e arquivadas, quase invariavelmente filtradas por um ou mais campos (nos quais obviamente colocamos índices em ambas as tabelas). Por conveniência, faria sentido ter uma visão como esta:
create view vMyTable_Combined as
select * from MyTable_Active
union all
select * from MyTable_Archive
Mas se eu executar uma consulta como
select * from vMyTable_Combined where IndexedField = @val
ele fará a união em tudo , desde Active e Store antes de filtrar por @val, o que prejudicará o desempenho.
Existe alguma maneira inteligente de fazer com que as duas subconsultas da união visualizem cada filtro @valantes de criarem a união?
Ou talvez haja alguma outra abordagem que você sugeriria para alcançar o que estou procurando, ou seja, uma maneira fácil e eficiente de obter o conjunto de registros da união, filtrado pelo campo indexado?
EDIT: aqui está o plano de execução (e você pode ver os nomes reais das tabelas aqui):
Curiosamente, a tabela ativa está realmente usando o índice correto (além de uma pesquisa RID?), mas a tabela de arquivo está fazendo uma varredura na tabela!
Os comentários sobre a questão mostram que o problema é que o banco de dados de teste que o OP estava usando para desenvolver a consulta tinha características de dados radicalmente diferentes do banco de dados de produção. Ele tinha muito menos linhas e o campo usado para filtragem não era seletivo o suficiente.
Quando o número de valores distintos em uma coluna é muito pequeno, o índice pode não ser suficientemente seletivo. Nesse caso, uma varredura de tabela sequencial é mais barata do que uma operação de busca de índice/pesquisa de linha. Normalmente, uma varredura de tabela faz uso extensivo de E/S sequencial, que é muito mais rápida do que as leituras de acesso aleatório.
Freqüentemente, se uma consulta retornar mais do que apenas uma pequena porcentagem de linhas, será mais barato fazer apenas uma varredura de tabela do que uma busca de índice/pesquisa de linha ou operação semelhante que faz uso pesado de E/S aleatória.
Só para acrescentar, o que eu encontrei. Se você fizer:
Em seguida, você pode filtrar no campo [Ativo] e garantir que a outra parte não seja carregada.