Sql-server – Inner Join or direct data save in SQL Server

performanceselectsql-server-2008

I am developing a social network site and expecting very good response from people so expecting huge users (maybe more than million in years). My website is holding user information table and all its related information like

Location

Country
State
City
Location

Education

School
College
University
etc

So finally user table will join with these and will fetch information through views. I am maintaining index properly. However thinking for future and want to make best solution for Select query.

My question is what is best process for faster select query, shall I do inner join among thees tables or should I store direct location type information in user table as these changes very rare. All location fields will be nvarchar type and length around 100-250 char.

Best Answer

I believe you are going to want to have things split out into multiple tables as you've described. Everything in the user table, and don't name it user lest you want to spend your application's lifetime writing [user], is going to result in every access to the database to hit one table. Imagine it's the lunch hour and everyone logs in to check their SumanSpace social network site. .5M users all reading that user table at once and then you have people creating new accounts as well as updating their existing data. The less wide you can make that base table, the better as you're going to be able to stuff more entries in an 8k page.

Plus, things like updates to Education or Location won't need to lock the main user table which could cause contention. Those locks would be on, what I assume, are less frequently accessed tables.

Specific to your tables. Education - I would look to design it as 2 - 3 tables. EducationType would be a lookup table describing the type of institution (grade school, middle school, junior high, high school, community college, military college, college, university, etc). EducationLocation to associate an institute of learning with a physical space. Something to consider here are universities with multiple locations like DeVry-is there business value in building out a location hierarchy with the parent in Downer's Grove and then all the satellite campuses listed? EducationObtained could be another lookup table, indicating the type of education obtained. For things less than the collegiate level, it'd have a value of graduated but the university level could have Bachelors, Masters, PhD, JD, Divinity (however abreviated) in Sciences, Arts, Fine Arts, etc. And of course, a cross reference table tying a user to their tenure (start and stop dates) at an institute.

Finally, you won't want an INNER JOIN as people may not have or may not choose to record their education or location information.

Those are my top thoughts before I go and get cleaned up. I'm sure @DennyCherry has some insight into the matter based on his time at MySpace.

Related Solutions

Sql-server – T-SQL INNER JOIN Optimisation

The reason the performance is better is that the smaller data types have much less working set - see the execution plan (http://www.red-gate.com/our-company/about/book-store/assets/sql-server-execution-plans.pdf)

I expect you get the largest benefit from the nvarchar(4000) to nvarchar(50) - that's a reduction of 80x - and nvarchar(4000) can use up to 8K! of space. For a key, that is not a good idea.

In addition, the fact that there are no foreign keys probably mean you don't have a very good indexing strategy either. If you did have indexes (even for ridiculously large columns), you would probably find they could outperform the cast since it probably wouldn't spool as much.

In general, you don't want any operations on your keys in the join if at all possible, especially for large data sets.

SQL Server – How to Fix Slow COUNT(*) with INNER JOIN

If the foreign key is not nullable, then there is no reason for the join at all, is there? So why not let SQL Server scan the most efficient index available instead of computing a join that will result in the exact same count?

SELECT COUNT(*) FROM dbo.messages;

That said, you can do this much more efficiently:

SELECT SUM(rows) FROM sys.partitions
  WHERE [object_id] = OBJECT_ID('dbo.messages')
  AND index_id IN (0,1);

This will be slightly inaccurate due to in-flight transactions, but it also won't block (or be blocked by) any activity against the table, so pick your poison.

Best Answer

Related Solutions

Sql-server – T-SQL INNER JOIN Optimisation

SQL Server – How to Fix Slow COUNT(*) with INNER JOIN

Related Question