Tomas Kubes's questions

Eu tenho uma consulta que não funciona bem e o otimizador varre todos os 108.864 itens da tabela ExternFile inutilmente ao buscar apenas 30 linhas. Eu simplifiquei o problema para seguir a seleção da exibição.

A consulta da camada superior se parece com:

SELECT 
realtyId,    
ServerPath,
OfferState,
CategoryId,
ParentCategoryId
FROM FSTest
WHERE OfferState <= 3 AND (CategoryId = 2 OR ParentCategoryId = 2)
ORDER BY Ranking DESC
OFFSET 2970 ROWS FETCH NEXT 30 ROWS ONLY

A visualização subjacente é a seguinte:

CREATE View [dbo].[FSTest] AS
SELECT 
realty.Id as realtyId,
realty.OfferState,
realty.Ranking,
Category.Id as CategoryId,
Category.ParentCategoryId,
ExternFileOuterApply.ServerPath as ServerPath,
ExternFileOuterApply.Height as ImageHeight,
ExternFileOuterApply.Width as ImageWidth
FROM realty
JOIN Category ON realty.CategoryId = Category.Id
OUTER APPLY (SELECT TOP (1) ServerPath, Height,Width FROM ExternFile WHERE ForeignId =  realty.Id AND IsMain = 1 AND ForeignTable = 5) AS ExternFileOuterApply

O problema é o OUTER APPLY. Eu esperaria que a consulta ignorasse todas as 2.970 linhas e executasse OUTER APPLY apenas para 30 linhas contidas no resultado. Mas o OUTER APPLY está verificando toda a tabela ExternFile (108864 linhas). Por quê?

Está de alguma forma conectado com o índice temporário no spool de índice (veja a imagem abaixo)? O que há de errado com a consulta?

Entendo como uma única coluna indexada funciona no SQL Server e como ela é implementada usando árvores balanceadas. Há muitos vídeos interessantes no YouTube sobre esse assunto. No entanto, não entendo como funciona se o índice for baseado em várias colunas. Por exemplo:

CREATE NONCLUSTERED INDEX idxItemsCatState
ON Items (Category,OfferState)
INCLUDE ([Id],[Ranking])

E como isso pode acelerar consultas como

SELECT ID, Ranking FROM Items where Category = 1 AND OfferState < 3

Ainda é implementado como B-Tree? Como ele pode avaliar a combinação de valores? Quais são as restrições para tal recurso?

No SQL Server 2012, tenho uma função com valor de tabela com junção a outra tabela que preciso contar o número de linhas para esta 'função com valor de tabela'. Quando inspeciono o plano de execução, posso ver a tabela de junção à esquerda. Por quê? Como a tabela unida à esquerda pode influenciar o número de linhas retornadas? Eu esperaria que o mecanismo de banco de dados não precisasse avaliar a tabela de articulação esquerda na consulta SELECT count(..).

Select count(realtyId) FROM [dbo].[GetFilteredRealtyFulltext]('"praha"')

O plano de execução:

A função com valor de tabela:

CREATE FUNCTION [dbo].[GetFilteredRealtyFulltext]
(@criteria nvarchar(4000))
RETURNS TABLE
AS
RETURN (SELECT 
realty.Id AS realtyId,
realty.OwnerId,
realty.Caption AS realtyCaption,
realty.BusinessCategory,
realty.Created,
realty.LastChanged,
realty.LastChangedType,
realty.Price,
realty.Pricing,
realty.PriceCurrency,
realty.PriceNote,
realty.PricePlus,
realty.OfferState,
realty.OrderCode,
realty.PublishAddress,
realty.PublishMap,
realty.AreaLand,
realty.AreaCover,
realty.AreaFloor,
realty.Views,
realty.TopPoints,
realty.Radius,
COALESCE(realty.Wgs84X, ruian_cobce.Wgs84X, ruian_obec.Wgs84X) as Wgs84X,
COALESCE(realty.Wgs84Y, ruian_cobce.Wgs84Y, ruian_obec.Wgs84Y) as Wgs84Y,
realty.krajId,
realty.okresId,
realty.obecId,
realty.cobceId,
IsNull(CONVERT(int,realty.Ranking),0) as Ranking,

realty.energy_efficiency_rating,
realty.energy_performance_attachment,
realty.energy_performance_certificate,
realty.energy_performance_summary,

Category.Id AS CategoryId,
Category.ParentCategoryId,
Category.WholeName,
okres.nazev AS okres,
ruian_obec.nazev AS obec,
ruian_cobce.nazev AS cobce,
ExternFile.ServerPath,
Person.ParentPersonId,
( COALESCE(ftR.Rank,0) + COALESCE(ftObec.Rank,0) + COALESCE(ftOkres.Rank,0) + COALESCE(ftpobvod.Rank,0)) AS FtRank

FROM realty
JOIN Category ON realty.CategoryId = Category.Id
LEFT JOIN ruian_cobce ON realty.cobceId = ruian_cobce.cobce_kod
LEFT JOIN ruian_obec ON realty.obecId = ruian_obec.obec_kod
LEFT JOIN okres ON realty.okresId = okres.okres_kod
LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1 AND ExternFile.ForeignTable = 5
INNER JOIN Person ON realty.OwnerId = Person.Id
Left JOIN CONTAINSTABLE(Realty, *, @criteria) ftR ON realty.Id = ftR.[Key] 
Left JOIN CONTAINSTABLE(ruian_obec, *, @criteria) ftObec ON realty.obecId = ftObec.[Key] 
Left JOIN CONTAINSTABLE(Okres, *, @criteria) ftOkres ON realty.okresId = ftOkres.[Key]
Left JOIN CONTAINSTABLE(pobvod, *, @criteria) ftpobvod ON realty.pobvodId = ftpobvod.[Key]
WHERE Person.ConfirmStatus = 1
AND ( COALESCE(ftR.Rank,0) + COALESCE(ftObec.Rank,0) + COALESCE(ftOkres.Rank,0) + COALESCE(ftpobvod.Rank,0))  > 0
)

ATUALIZAR:

Eu adiciono um índice exclusivo para seguir a ideia de Rob Farley:

 Create unique nonclustered index ExternFileIsMainUnique ON ExternFile(ForeignId) WHERE IsMain = 1 AND ForeignTable = 5

E indexado sugerido pelo DB Engine:

CREATE NONCLUSTERED INDEX [RealtyOwnerLocation] ON [dbo].[Realty]

( [OwnerId] ASC ) INCLUDE ( [Id], [okresId], [obecId], [pobvodId]) GO

Para simplificar, removo a condição

WHERE Person.ConfirmStatus = 1

da função de valor tabelada acima.

Agora o plano de execução é muito mais simples, mas ainda toca na tabela ExternFile:

Talvez o servidor SQL não seja inteligente o suficiente?

No passado, escolhi a datetimecoluna criada como índice clusterizado inadequado na tabela.

Agora concluí (com base no plano de execução) que seria melhor escolher a chave primária de identidade de ID como a chave agrupada, porque ela é frequentemente referenciada como chave estrangeira.

Gostaria de descartar a chave clusterizada atual e criar uma nova, mas não posso descartar a chave primária, porque o full-textíndice depende dessa chave primária.

Posso apenas alternar a chave primária para o índice clusterizado ou preciso descartar a chave primária e a cadeia de todos os objetos dependentes?

Abaixo você encontrará definição de tabela e definição de índice clusterizado.

CREATE TABLE [dbo].[Realty](
    [Id] [int] IDENTITY(1,1) NOT NULL,  
    [Created] [datetime] NOT NULL,
    ....

 CONSTRAINT [PK_Realty] PRIMARY KEY NONCLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
...


CREATE CLUSTERED INDEX [Created] ON [dbo].[Realty]
(
    [Created] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

tenho uma tabela:

CREATE TABLE [dbo].[Realty](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RankingBonus] [int] NOT NULL,
    [Ranking]  AS ([Id]+[RankingBonus]) PERSISTED NOT NULL
    ....
)

E uma vista:

CREATE View  [dbo].[FilteredRealty] AS
 SELECT 
realty.Id as realtyId,
...
COALESCE(realty.Wgs84X, ruian_cobce.Wgs84X, ruian_obec.Wgs84X) as Wgs84X,
COALESCE(realty.Wgs84Y, ruian_cobce.Wgs84Y, ruian_obec.Wgs84Y) as Wgs84Y,
realty.Ranking,
...
FROM realty
JOIN Category ON realty.CategoryId = Category.Id
LEFT JOIN ruian_cobce ON realty.cobceId = ruian_cobce.cobce_kod
LEFT JOIN ruian_obec ON realty.obecId = ruian_obec.obec_kod
LEFT JOIN okres ON realty.okresId = okres.okres_kod
LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1
                     AND ExternFile.ForeignTable = 5
INNER JOIN Person ON realty.OwnerId = Person.Id
WHERE Person.ConfirmStatus = 1

Eu tenho um modelo dbml em C# (LinqToSQL) com a exibição FilteredRealty nele. O campo [Ranking] é reconhecido como um int anulável e, portanto, tenho que corrigir o tipo no código gerado toda vez que altero algo no banco de dados. Isso é muito frustrante para mim e muito trabalho manual.

Não há agregações usadas no FilteredRealty (em relação a esta questão relacionada ).

Por que a coluna Classificação da exibição é considerada anulável se Realty.Ranking não for anulável?