In a sproc, I want to return some rows from a table, but I also want to return a couple of values from a specific row which won't always show in the results, should I use union for that (select that row then union with the rest of the results) or use the sproc output parameters, performance-wise which is better?


If you want the exact details, please read below:

I'm creating a website mainly based on forums and I'm using a MySQL database. In the database I have two tables, one for forums and the other one is for forum posts (which includes both threads and replies) with the following structure (some fields were removed for the sake of simplicity):

CREATE TABLE Forums
(
ForumID INT NOT NULL,
ForumName VARCHAR(50) NOT NULL),
ForumDescription VARCHAR(500) NULL,
PRIMARY KEY (ForumID)
)

CREATE TABLE ForumPosts
(
PostID INT NOT NULL,
ThreadID INT NULL,
ForumID INT NOT NULL,
Subject VARCHAR(500) NULL,
PostText VARCHAR(5000) NOT NULL,
PostDate DATETIME NOT NULL,
ReplyCount INT NULL,
PRIMARY KEY (PostID),
INDEX (ForumID),
INDEX (ThreadID)
)

As you can see in the ForumPosts table, I have three nullable columns, ThreadID which will be null for threads and will contain the PostID of the thread if the row is a reply, and also subject and replycount which should only contain data if the row is a thread (BTW, in the real application I'm not using nullable columns for the sake of performance, in thread rows I use 0 for ThreadID and in reply rows I use an empty string for Subject and 0 for ReplyCount, I only used nulls here to make things clearer).

Now to the real question, in my application I have a page, showthread.aspx?threadid=?, used for showing threads. What I'm trying to do in my application is to avoid mutiple trips to the database (we need the thread data and all the replies). This shouldn't be a problem if I don't use paging in my application (ie. the thread with all replies are displayed in one page) but this is sure unrealisitc as you know, so the problem here is how can I get the thread details and the replies in one trip to the database, I figured out that I could either use union to always return the thread row as the first row or I could use sproc output parameters to return the subject and reply count field, so, as I mentioned in the start of this post, which alternative gives better performance?

Your help is really appreciated...

What on earth is wrong with NULLs?!

Why are you storing ReplyCount - this is redundant (and could end up innacurate) because you can calculate it with a very simple count at any time.

Have you heard of JOINs? They allow you to link two tables together based on a common criterion, say... ThreadID

Hi George, thanks for your reply, actually there's nothing wrong with nulls, the whole point is that I'm trying to design a highly scalable application so every tiny tweak or trick is done even if it doesn't make a big difference, it's just that I'm trying to squeeze every drop of performance from the server .. actually nulls can slightly affect indexes if used in them, you may read about this at www.mysql.com, again I'm saying that I'm trying to squeeze the server, so, if the difference is very slight it still counts .. same is for the ReplyCount, it's there for the sake of performance .. regarding joins, I'm not really sure what you're talking about, I have all the data in one table, sure I can use self join but I don't think it makes much sense in my case, unless you're suggesting that I split the posts table into two tables threads and posts, is this what you meant?