Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
How much data do you expect in the message table? –  gbn May 6 '11 at 19:02
    
@gbn: millions of records. thus at least two records for each message record will be had in recipients table (from, to) as a minimum. –  kacalapy May 6 '11 at 19:07
    
Do you want your output to be a single text string like the original email? If so you'll need some kind of string concatenation aggregate such as the methods discussed in this SO question –  Jack Douglas May 7 '11 at 7:36
4  
is it just me or this schema looks way too normalized? –  Marian May 7 '11 at 8:18
    
@Marian: the normilization is due to the recipient table being able to capture messages from outlook where a single message can have multiple TO, CC, and BCC listings. –  kacalapy May 9 '11 at 17:34

2 Answers 2

up vote 6 down vote accepted
+25

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.

share|improve this answer
    
Just got a chance to actually try this code at home and realized I needed to debug a little. –  RThomas May 10 '11 at 4:55
1  
That's a pretty neat trick with COALESCE. Where did you learn that? –  Cape Cod Gunny May 13 '11 at 16:34
    
Wish I could say I figured it out myself but alas... I found an article awhile back on alternatives to cursors. This was one of the tricks described. I think it was a SqlServerCentral article. –  RThomas May 13 '11 at 16:40

I would create a view called viewInbox that is devised by all the one-to-one relationship tables. This would be my main query view. I'd use this view (viewInbox) to display a list of all the inbox items.

When the user drills down into the message, I would then bring back all the information including the one-to-many relationships from the multiple To's, CC's and BCC's.

share|improve this answer
    
More performant for sure, and probably how I would do it as well, but doesn't meet request to "denormalize". –  RThomas May 12 '11 at 17:56
2  
@LazyDBA - I would consider the viewInbox to be "denormalized". –  Cape Cod Gunny May 12 '11 at 18:26
    
"normalized" and "denormalized" refer to the design of tables. To denormalize a schema means a change to the schema. So when there's a request to "denormalize" the data using a select statement, I suspect the word is being used to mean "include redundant data" in essence, joining tables into a single resultset. –  Michael J Swart May 13 '11 at 17:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.