Hi,

I have expanded the Polls module quite extensively for a project I am working on.

A feature I want to implement is to only show Polls that a user has not voted on yet. I am doing this as follows using SQL Server 2005:

Each time a user votes on a poll, that action is added to a 'Votes' table, and the User ID is recorded in an 'AddedBy' NVARCHAR field in that table.
When I want to get a list of Polls that a user hasn't voted on, I do something like this:

SELECT DISTINCT p.PollID
FROM vs_Polls p
WHERE p.PollID NOT IN
(SELECT DISTINCT p.PollID
FROM vs_Polls p
LEFT JOIN vs_PollOptions o
    ON p.PollID = o.PollID
JOIN vs_Votes v
    ON o.OptionID = v.OptionID
WHERE (v.AddedBy = 'marco'))
ORDER BY p.PollID

There is a Sub-Query, where the Poll table is joined to the PollOptions table, which is then joined to the Votes table.
There is also a 'WHERE' clause which operates on the 'AddedBy' field.

It is this WHERE clause which causes me some concern. Imagine a situation whereby there were thousands of polls, and many more thousands of votes on those polls. At this sort of scale the difference between using an INT vs. an NVARCHAR field could make a difference.

I understand why Marco chose to do it this way but at the scales I am looking at I think it might become a problem.

What do you think? Should I add an INT Primary Key to the ASP.NET generated 'aspnet_Users' table, and rewrite everything to work off this? Or am I worrying about it for nothing?

I suppose the question comes down to: Just how inefficient is 'WHERE'ing and Indexing on an NVARCHAR field compared with an INT?

Any views would be really welcome.

Thanks...

First, I can't imagine thousands of polls! But regardless of how many you have, if you modify an ASP.NET standard table you're taking a risk regarding future compatibility. SQL Server is pretty efficient in handling character data and a difference of a few bytes per record isn't going to be noticed.

I mentioned in another thread here that I'm not terribly happy to bring the ASP.NET tables into our own data model in the form of joins, and the thought of modifying their table is unthinkable to me unless you take control of the tables by writing your own providers. I strongly advise against it unless you have a compelling need.

You could speed up the overall site more noticably by looking closely at the TBH table schemas and indexes, and the DAL/BLL design. Sometimes a few small tweaks can make a big difference in performance. But this much effort should only be made on your own final design, since you'll probably deviate from TBH to meet your own design specifications.

Eric

Thanks a lot Eric. Most useful, as always.