Sql-server – How to effectively query and aggregate a normalized SQL db

querysql-server-2005

In relation to Most efficient way to return multiple aggregates in a single stored proc?

I have an email type application and want to select all the messages (inbox) for a user. The problem is that I normalize the header part of the emails into the DB such that the flat data goes into a Message table and the from, to, CC, BCC get stored to another table.

What's the best way to select messages (in full – meaning denormalize the full message) so that each record contains all the message pertinent fields including all the Message table fields and any related records from the recipient table related to the message as per the PK/ FK relationship.

One thing I am placing much weight on is the efficiency of the SQL solution because this will be code that gets executed many many times over and will likely be the most run sql of the entire DB

For context here is a view of my DB schema.

DB SCHEMA

Best Answer

This is how I would do it. I regularly use Coalesce to place rows into delimeted fields and it always performs and scales well (as long as you realize that a subquery is ALWAYS going to cause some performance hit).

If you didn't like running it as a stored procedure you could also easily rewrite as a table valued function.

Another approach would be a CTE I suppose but I am not as familiar with that approach to type from scratch.

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

You might ask yourself how Coalesce works when used in this way ( I did, when I first saw it used ). Basically it creates a recursive query returning each subsequent non-null value in the set, in turn, until the end of the return set. Coming out the other end you get a coma delimited list of all results as a single string.

Related Question