first of all, sorry for the non descriptive title, I'm just too rushed so I couldn't come up with a better one.
The problem with the above query is that, it doesn't return any article with contributorID null. I understand this is because of the join on the contributors table. What should I do in such a case?
To get articles with ContributorID = null, make the join to the Sources table a LEFT JOIN.
You can look at the query plan in 2008, and it will suggest indexes but here are some starting ideas.
Users_Contributors (UserID, ContributorID) -- if USERID is always going to be 3 -- consider a filtered index that only contains rows where USERID = 3.
Articles (UserID, SourceID, ContributorID) -- same comments about filtered index
Sources (SourceID, Name)
By the way, how is join at the bottom supposed to work? What column in the contributors table itself are you joining to? Assuming you meant Contributors.ContributorID instead of Sources.SourceID, then
Contributors (ContributorID, Name)
Never cut and past bits and pieces. You should always show the code that is actually running as a whole. A join to a table that references no column in that table makes no sense.
Inner Join CONTRIBUTORS On Articles.ContributorId = Sources.SourceID
answered Nov 23 '09 at 10:58 AM
If you need all columns of ARTICLES matching or not CONTRIBUTORS, then use a LEFT JOIN instead of INNER JOIN. Use the same rule for ARTICLES if it applies.
The first performance tip i can give is to replace your IN() by EXISTS or even LEFT JOINS:
Basic Indexes should be all ids with clustered index, nonclustered index on Users_Contributors( ContributorId, UserID) and Users_Sources( SourceId, UserID ).
For paging, i would suggest row_numer() function, its something like:
Let me know if this helps...
answered Nov 23 '09 at 11:15 AM