Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0
This is the forum to discuss the Wrox book ASP.NET 2.0 Website Programming: Problem - Design - Solution by Marco Bellinaso; ISBN: 9780764584640
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 18th, 2007, 12:40 PM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 1 Time in 1 Post
Default 'AddedBy' NVARCHAR field - inefficient?

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...
  #2 (permalink)  
Old January 18th, 2007, 07:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Atlanta, Georgia, USA.
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #3 (permalink)  
Old January 18th, 2007, 07:38 PM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 1 Time in 1 Post
Default

Thanks a lot Eric. Most useful, as always.

 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert from nvarchar(25) to smalldatetime aspless SQL Language 6 September 14th, 2007 09:21 AM
convert nvarchar to money smatyk SQL Server 2000 2 October 26th, 2006 06:37 PM
change nvarchar to varchar vincentc SQL Server 2000 3 May 24th, 2005 10:56 PM
Convert int to nvarchar bekim SQL Language 1 August 12th, 2004 06:33 AM
retrieve numerice value from nvarchar mateenmohd SQL Server 2000 6 July 21st, 2003 03:52 AM



All times are GMT -4. The time now is 03:15 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.