mdoyle's questions

versão tl;dr: Uma pesquisa de texto completo usando FREETEXTABLE que funcionou bem em uma coluna em uma tabela produz 100% de falsos positivos e 100% de falsos negativos quando as mesmas etapas são seguidas, mas o índice de texto completo está em várias colunas em uma exibição composta por duas tabelas base (a tabela original e uma nova). A pesquisa revela alegações contraditórias de que uma pesquisa de texto completo pode ser executada em várias tabelas se elas forem unidas em uma exibição e que a pesquisa só pode envolver uma única tabela base quando uma exibição é consultada.

Estamos executando o SQL Server 2008 R2. Como parte de um projeto maior, estamos criando um banco de dados pesquisável de receitas para um cliente. O cliente forneceu dados em formato XML adquiridos de terceiros. Com base no wireframe do aplicativo, importamos os dados e quais dados eram relevantes. DDL na parte pertinente e um subconjunto dos dados:

CREATE TABLE recipe (
    id INT IDENTITY(1,1) NOT NULL,
    title NVARCHAR(255) NOT NULL,
    descrip NVARCHAR(4000) NOT NULL,
    prep_time NVARCHAR(127),
    ease_of_prep NVARCHAR(25)
    CONSTRAINT PK_recipe PRIMARY KEY CLUSTERED (id)
);

INSERT INTO recipe (title, descrip, prep_time, ease_of_prep)
VALUES ('Aromatic Rice Pudding', 'Store-bought rice pudding with some simple stir-ins.', '5 minutes', 'Easy'),
    ('Lemon Chicken Stir-Fry', 'Spiked with lots of zesty lemon, this stir-fry has a colorful mix of snow peas, carrots and scallions. Substitute any thinly sliced vegetables like bell peppers or celery.', '40 minutes', 'Easy'),
    ('Salsa-Roasted Salmon', 'Fire up the food processor, add a few simple ingredients, and you’ve got a vibrant-tasting salsa in minutes. Substitute other fish, chicken or turkey for the salmon—adjust the roasting time accordingly.', '10 minutes', 'Easy');

Não me disseram quais seriam os critérios de pesquisa, mas assumi mais de um campo e que o texto completo produziria resultados mais sintonizados com o que o cliente estava procurando. DDL para FTC e índice de texto completo:

CREATE FULLTEXT CATALOG [ftc_default]WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT
AUTHORIZATION [dbo];

CREATE FULLTEXT INDEX ON recipe KEY INDEX PK_recipe ON (ftc_default) WITH (CHANGE_TRACKING AUTO);

ALTER FULLTEXT INDEX ON recipe ADD title;
ALTER FULLTEXT INDEX ON recipe ENABLE;

Escrevi o seguinte procedimento armazenado (opcional, mas útil para teste):

CREATE PROCEDURE test_fulltext
    @terms NVARCHAR(2000) = NULL

AS
BEGIN

    IF ISNULL(@terms, '') = ''

        SELECT id, title, descrip
        FROM recipe;

    ELSE
        SELECT r.id, r.title, r.descrip
        FROM recipe r 
        INNER JOIN FREETEXTTABLE(recipe, title, @terms) kt
            ON r.idid = kt.[KEY]
        ORDER BY RANK DESC;

END

Isso funcionou como anunciado. Uma pesquisa sobre 'salsa com pimenta' retornaria cerca de uma dúzia de receitas, com "Bifes com pimenta e molho pan" como a receita principal e outras com relevância decrescente até "Frango com páprica com ervas". Apenas olhando os títulos, parecia não haver falsos positivos.

Atualizei o gerente de projeto para que ele soubesse que as receitas podiam ser pesquisadas pelo título e, se o cliente quisesse incluir outros campos, eu poderia adicioná-los. Ele então perguntou por que não estávamos pesquisando por tipo de cozinha, tema, considerações de saúde e outros. Eca. Não nos disseram para importar esses dados. De volta à estaca zero.

Os outros dados não caberiam na tabela da receita sem desnormalizá-la, então nós os importamos para uma nova tabela. DDL inteiro e um subconjunto de dados:

CREATE TABLE recipe_search (
    ID INT IDENTITY(1,1) NOT NULL,
    recipe_id INT NOT NULL,
    term_type NVARCHAR(25) NULL,
    term_value NVARCHAR(50) NULL
    CONSTRAINT PK_recipe_search PRIMARY KEY CLUSTERED (id)
);

ALTER TABLE recipe_search 
WITH CHECK ADD CONSTRAINT FK_recipe_recipe_id 
FOREIGN KEY (recipe_id) REFERENCES recipe (id);

INSERT INTO recipe-search (recipe_id, term_type, term_value)
VALUES (1, 'Course', 'Dessert'),
    (1, 'Course', 'Snacks'),
    (1, 'Cuisine', 'Middle Eastern'),
    (1, 'Cusines', 'Mediterranean'),
    (1, 'Dish Type', 'Desserts'),
    (1, 'Health Consideration', 'Healthy Weight'),
    (1, 'Season', 'Winter'),
    (1, 'Season', 'Fall'),
    (1, 'Style/Theme', 'Vegetarian'),
    (1, 'Technique', 'No Cook'),
    (2, 'Course', 'Dinner'),
    (2, 'Cuisine', 'Asian'),
    (2, 'Dish Type', 'Main Dish'),
    (2, 'Health Consideration', 'Low Calorie'),
    (2. 'Health Consideration', 'Low Carb'),
    (2, 'Main Ingredient', 'Chicken'),
    (2, 'Technique', 'Stir-fry'),
    (3, 'Course', 'Dinner'),
    (3, 'Cuisine', 'Southwestern'),
    (3, 'Cuisine', 'Mexican'),
    (3, 'Cuisine', 'American'),
    (3, 'Dish Type', 'Main Dish'),
    (3, 'Health Consideration', 'Low Carb'),
    (3, 'Season', 'Summer'),
    (3, 'Technique', 'Bake'),
    (3, 'Roast', 'Food Processor');

Então, tendo lido que a pesquisa de texto completo só pode funcionar em uma tabela e que para pesquisar várias tabelas é necessário criar uma exibição, fiz isso:

CREATE VIEW dbo.vw_recipe_search
WITH SCHEMABINDING
AS
    SELECT rs.id search_id, r.id recipe_id, r.title, r.descrip, rs.term_value, r.ease_of_prep
    FROM dbo.recipe r
    INNER JOIN dbo.recipe_search rs ON r.id = rs.recipe_id
    WHERE r.active = 1;
GO

CREATE UNIQUE CLUSTERED INDEX idx_searchid ON vw_recipe_search (search_id);
GO

CREATE FULLTEXT INDEX ON vw_recipe_search
KEY INDEX idx_searchid ON (ftc_fu_default) WITH (CHANGE_TRACKING AUTO);
GO

ALTER FULLTEXT INDEX ON vw_recipe_search ADD (descrip);
GO
ALTER FULLTEXT INDEX ON vw_recipe_search ADD (term_value);
GO
ALTER FULLTEXT INDEX ON vw_recipe_search ADD (title);
GO
ALTER FULLTEXT INDEX ON vw_recipe_search ADD (ease_of_prep);
GO
ALTER FULLTEXT INDEX ON vw_recipe_search ENABLE;

E alterei o SP ( EDIT : atualizei o código abaixo para indicar as alterações reais que fiz):

ALTER PROCEDURE test_fulltext
    @terms NVARCHAR(2000) = NULL

AS
BEGIN

    IF ISNULL(@terms, '') = ''

        SELECT id, title, descrip
        FROM recipe;

    ELSE
        SELECT v.*
    FROM vw_recipe_search v 
    INNER JOIN FREETEXTTABLE(vw_recipe_search, (title, descrip, term_value, ease_of_prep), @terms) kt
        ON v.recipe_id = kt.[KEY]
    ORDER BY v.recipe_id DESC;

END

E agora os resultados da pesquisa são um absurdo completo. Uma pesquisa sobre "salsa", por exemplo, encontra cinco receitas exclusivas, nenhuma das quais contém a palavra "salsa" ou qualquer sinônimo razoável de dicionário de sinônimos em qualquer um dos campos indexados; nenhum dos tipos de pratos que se associa à salsa é encontrado. (Um dos resultados contém a palavra "molho", que suponho que possa ser um sinônimo de dicionário de sinônimos para "salsa".) Duas das cinco receitas são as duas primeiras receitas no conjunto de dados acima. Enquanto isso, nem o salmão assado na salsa, nem qualquer outra receita que contenha a palavra "salsa", é retornado pela pesquisa. Verifiquei que o catálogo de texto completo e o índice foram criados conforme o esperado e que o catálogo de texto completo tem o mesmo número de itens que o número de linhas retornadas da exibição (cerca de 4.000,

Um aborrecimento adicional: se uma receita atender aos critérios, TODOS os seus registros na exibição serão selecionados. É quase como se fosse sempre uma das colunas na receita da tabela que leva à ocorrência, portanto, todos os registros unidos na receita_pesquisa são incluídos.

Ao examinar essa reversão bizarra, encontrei o seguinte link : "Ao consultar uma exibição, apenas uma tabela base indexada de texto completo pode ser envolvida."

Pergunta nº 1: Qual é a correta - você pode ou não consultar várias tabelas em uma pesquisa de texto completo?
Pergunta nº 2: Deixei algo de fora ao tentar criar uma pesquisa de texto completo em várias tabelas e, se não, qual pode ser o problema aqui?

Estou procurando substituir minha solução baseada em cursor, se possível, em um determinado procedimento armazenado. Se fizer alguma diferença, isso está sendo executado no SQL Server 2008 R2. Estou procurando mais um algoritmo do que um código preciso.

Fundo:

O SP faz parte de um sistema para uma empresa que envia correspondências por mala direta ou e-mail. As correspondências contêm um código personalizado que o destinatário pode inserir ao visitar o comerciante para obter descontos ou ofertas especiais. O uso do código é rastreado e relatórios agregados sobre a resposta a várias correspondências são fornecidos aos comerciantes. Um "cliente" é definido como o alvo de uma das correspondências com um nome, sobrenome e endereço exclusivos; se não houver endereço, o e-mail substituirá o endereço.

As tabelas em causa são as seguintes (versões simplificadas):

CREATE TABLE job (
    id INT PRIMARY KEY IDENTITY (1,1),
    job_num VARCHAR(32) NOT NULL,
    mailing_id INT NOT NULL,
    personal_code NVARCHAR(50) NOT NULL,
    fname NVARCHAR(50) NOT NULL,
    lname NVARCHAR(50) NOT NULL,
    email NVARCHAR(50),
    address NVARCHAR(50),
    city NVARCHAR(50),
    state CHAR(2),
    zip NVARCHAR(10),
    extra NVARCHAR(150)
);

CREATE TABLE customer (
    id INT PRIMARY KEY IDENTITY (1,1),
    fname NVARCHAR(50) NOT NULL,
    lname NVARCHAR(50) NOT NULL,
    email NVARCHAR(50),
    address NVARCHAR(50),
    city NVARCHAR(50),
    state CHAR(2),
    zip NVARCHAR(10)
);

CREATE TABLE personal_code (
    id INT PRIMARY KEY IDENTITY (1,1),
    customer_id INT NOT NULL,
    mailing_id INT NOT NULL,
    personal_code NVARCHAR(50) NOT NULL,
    email NVARCHAR(50),
    FOREIGN KEY (customer_id) REFERENCES customer(id)
);

CREATE TABLE personal_code_extra (
    personal_code_id INT PRIMARY KEY,
    extra NVARCHAR(150),
    FOREIGN KEY (personal_code_id) REFERENCES personal_code(id)
);

A tabela de tarefas é preenchida por um processo externo que está envolvido na criação das listas de endereços para as quais a correspondência será enviada. Em seguida, chama o SP que desejo otimizar (espero), passando o job_num. O SP então lê todos os registros da tabela de trabalho com esse job_num e insere os dados armazenados nas outras três tabelas.

A tabela de clientes é, como esperado, onde os dados dos vários destinatários de correspondência são armazenados.

A tabela personal_code é um armazenamento para dados nos códigos pessoais associados às correspondências. Cada código está vinculado a uma correspondência específica, bem como a um cliente específico. Atualmente, o SP abre um cursor para a tabela de tarefas. Em seguida, itera por cada linha e, para cada uma, faz o seguinte:

1. Se address não for nulo, ele definirá @customer_id igual a customer.id onde fname, lname e address correspondem; caso contrário, ele o define igual a customer.id onde fname, lname e email correspondem e o endereço é nulo ou vazio.
2. Neste ponto, se @customer_id for nulo, não houve registro correspondente e um novo registro será adicionado ao cliente. @customer_id é definido como scope_identity().
3. Um registro é inserido em personal_code.
4. Se job.extra não for nulo, um registro é inserido em personal_code_extra com (é claro) o id gerado pela inserção na Etapa 3.

customer e personal_code são as maiores tabelas do banco de dados, com 40 milhões e 64 milhões de registros, respectivamente. Mesmo que as consultas no cliente tenham índices de cobertura, fazer uma pesquisa separada para cada linha no trabalho deve atrasar as coisas. Eu gostaria muito de abandonar o cursor e substituir essa abordagem RBAR por uma abordagem baseada em conjunto. O que está me impedindo de fazer isso é ter que usar um customer_id recém-criado para a inserção de personal_code, bem como um personal_code_id recém-criado para a tabela "extras". Se não fosse por isso, eu poderia fazer algo como

INSERT INTO personal_code (fields)
SELECT (fields)
FROM job j
INNER JOIN customer c ON j.fname = c.fname AND j.lname = c.lname AND j.address = c.address
WHERE j.address IS NOT NULL;

INSERT INTO personal_code (fields)
SELECT (fields)
FROM job j
INNER JOIN customer c ON j.fname = c.fname AND j.lname = c.lname AND j.email = c.email
WHERE j.address IS NULL;

INSERT INTO personal_code (fields)
SELECT (fields) -- but won't have a value for customer_id !
FROM job j
LEFT JOIN customer c ON either_address_or_email
WHERE c.id IS NULL;

E como lidar com a inserção do material "extra" em uma abordagem baseada em conjunto, não tenho ideia no momento. Agradeço antecipadamente quaisquer ideias.

EDIT: Código do cursor adicionado por solicitação. Isso é simplificado, mas tem todos os elementos essenciais - espero que minhas edições sejam precisas.

DECLARE job_cur CURSOR FOR
SELECT mailing_id, personal_code, email, fname, lname, address, city, state, zip, extra
FROM job
WHERE job_num = @job_no;

OPEN job_cur;

FETCH NEXT FROM job_cur INTO @mailing_id, @personal_code, @email, @fname, @lname, @address, @city, @state, @zip, @extra;

WHILE @@FETCH_STATUS = 0
BEGIN

    IF ISNULL(@address, '') != ''
    SET @customer_id = (
        SELECT id 
        FROM customer 
        WHERE fname = @fname
        AND lname = @lname
        AND address = @address
    );
    ELSE
    SET @customer_id = (
        SELECT id 
        FROM customer 
        WHERE fname = @fname
        AND lname = @lname
        AND email = @email
        AND (address IS NULL OR address = '')
    ); 

    IF @customer_id IS NULL
    BEGIN

        INSERT INTO customer (
            fname, lname, address, city, state, zip, email
        )
        VALUES (
            @fname, @lname, @address, @city, @state, @zip, @email
        );

        SET @customer_id = SCOPE_IDENTITY();

    END

    INSERT INTO personal_code (
        customer_id, mailing_id, personal_code, email
    )
    VALUES (
        @customer_id, @mailing_id, @personal_code, @email
    );
    SET @personal_code_id = SCOPE_IDENTITY();

    IF @extra IS NOT NULL           
        INSERT INTO personal_code_extra (
            personal_code_id, extra
        )
        VALUES (
            @personal_code_id, @extra
        );

    FETCH NEXT FROM job_cur INTO @mailing_id, @personal_code, @email, @fname, @lname, @address, @city, @state, @zip, @extra;

END

Estamos executando o SQL Server 2008. Ao examinar os resultados de uma consulta que executei nas exibições sys.dm_db_missing_index_*, vejo uma sugestão curiosa para uma coluna incluída. Tenho as duas tabelas a seguir:

create table foo (
    id int not null identity(1,1) primary key
    /* many more fields, not relevant to question */
)

create table bar (
    id int not null identity(1,1) primary key
    foo_id int not null,
    param_name nvarchar(50),
    param_value nvarchar(255)
)

A tabela foo tem uma relação 1:M com bar; bar é usado para armazenar miscelânea associada a alguns dos registros em foo.

Voltar para a consulta sys.dm_db_missing_index_*. Várias vezes ao dia, o otimizador procura um índice na barra com foo_id em equal_columns e id em included_columns. Minha pergunta é: qual é o objetivo de incluir a chave de índice clusterizado da tabela em um índice não clusterizado? Como o nível folha do índice não clusterizado inclui o valor da chave do índice clusterizado de qualquer maneira, não é redundante incluir a chave do índice clusterizado em um índice não clusterizado?

EDIT: O único índice existente na barra é o índice clusterizado com o PK como chave.

Desde já, obrigado.

Estou escrevendo um procedimento armazenado que usa um nome de banco de dados como argumento e retorna uma tabela dos índices desse banco de dados e seu nível de fragmentação. Este procedimento armazenado viverá em nosso banco de dados DBA (o banco de dados que contém as tabelas que os DBAs usam para monitorar e otimizar as coisas). Os sistemas em questão são todos SQL Server 2008 R2, se isso fizer diferença.

Eu tenho a consulta básica elaborada, mas estou tentando fornecer os nomes reais dos índices. Pelo que sei, essas informações estão contidas na exibição sys.indexes de cada indivíduo. Meu problema específico é tentar fazer referência a essa exibição programaticamente do procedimento armazenado de outro banco de dados.

Para ilustrar, esta é a parte da consulta em questão:

FROM sys.dm_db_index_physical_stats(@db_id,NULL,NULL,NULL,NULL) p
INNER JOIN sys.indexes b ON p.[object_id] = b.[object_id] 
    AND p.index_id = b.index_id 
    AND b.index_id != 0

A consulta funciona bem quando executada a partir do banco de dados identificado por @db_id, porque está usando a exibição sys.indexes adequada. Se eu tentar chamar isso do banco de dados DBA, no entanto, tudo ficará nulo, pois a exibição sys.indexes é para o banco de dados errado.

Em termos mais gerais, preciso ser capaz de fazer algo assim:

DECLARE @db_name NVARCHAR(255) = 'my_database';
SELECT * FROM @db_name + '.sys.indexes';

ou

USE @db_name;

Tentei alternar bancos de dados ou fazer referência a outros bancos de dados usando combinações de concatenação de strings e funções OBJECT_NAME/OBJECT_ID/DB_ID e nada parece funcionar. Eu apreciaria qualquer ideia que a comunidade possa ter, mas suspeito que terei que reequipar este procedimento armazenado para residir em cada banco de dados individual.

Agradecemos antecipadamente por quaisquer sugestões.