Início / dba / Perguntas / 2569
Accepted
kacalapy
Asked: 2011-05-07 10:27:58 +0800 CST

Como consultar e agregar efetivamente um banco de dados SQL normalizado?

  • 772

Em relação à maneira mais eficiente de retornar vários agregados em um único procedimento armazenado?

Tenho um aplicativo do tipo e-mail e desejo selecionar todas as mensagens (caixa de entrada) para um usuário. O problema é que eu normalizo a parte do cabeçalho dos e-mails no banco de dados de forma que os dados simples entrem em uma tabela de mensagens e de, para, CC, BCC sejam armazenados em outra tabela.

Qual é a melhor maneira de selecionar mensagens (na íntegra - significando desnormalizar a mensagem completa) para que cada registro contenha todos os campos pertinentes à mensagem, incluindo todos os campos da tabela Message e quaisquer registros relacionados da tabela de destinatários relacionados à mensagem de acordo com o PK/ Relacionamento FK.

Uma coisa em que estou colocando muito peso é a eficiência da solução SQL porque este será o código que será executado muitas vezes e provavelmente será o sql mais executado de todo o banco de dados

Para contextualizar, aqui está uma visão do meu esquema de banco de dados.

ESQUEMA DE BD

sql-server-2005 query

2 respostas

  1. Best Answer

    É assim que eu faria. Eu uso regularmente o Coalesce para colocar linhas em campos delimitados e ele sempre funciona e escala bem (contanto que você perceba que uma subconsulta SEMPRE causará algum impacto no desempenho).

    Se você não gostou de executá-lo como um procedimento armazenado, também pode reescrever facilmente como uma função com valor de tabela.

    Outra abordagem seria um CTE, suponho, mas não estou tão familiarizado com essa abordagem para digitar do zero.

    CREATE PROCEDURE GetMessageById
    @pMessageID int
AS
BEGIN

SET NOCOUNT ON;

Declare @pTo varchar(max)
Declare @pCC varchar(max)
Declare @pBC varchar(max)

SELECT @pTo = COALESCE(@pTo + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 1 /** or whatever the id of TO field is */

SELECT @pCC = COALESCE(@pCC + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 2 /** or whatever the id of CC field is */

SELECT @pBC = COALESCE(@pBC + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 3 /** or whatever the id of BCC field is */

SELECT Message.*, @pTo as [ToField], @pCC as [CCField], @pBC as [BCCField], (SELECT TOP 1 [EmailAddress] FROM MessageRecipient Where RecipientTypeID = 0 /**<sender id>*/ AND MessageID = @pmessageID) AS [FromField] FROM Message Where Message.ID = @pMessageID

END
GO

    Você pode se perguntar como o Coalesce funciona quando usado dessa maneira (eu fiz, quando o vi usado pela primeira vez). Basicamente ele cria uma consulta recursiva retornando cada valor não nulo subseqüente do conjunto, por sua vez, até o final do conjunto de retorno. Saindo do outro lado, você obtém uma lista delimitada por vírgulas de todos os resultados como uma única string.

    • 6

  2. Eu criaria uma exibição chamada viewInbox que é desenvolvida por todas as tabelas de relacionamento um-para-um . Esta seria minha visão de consulta principal. Eu usaria essa exibição (viewInbox) para exibir uma lista de todos os itens da caixa de entrada.

    Quando o usuário detalhar a mensagem, eu traria de volta todas as informações, incluindo as relações um-para-muitos dos múltiplos To's, CC's e BCC's.

    • 5

relate perguntas