Timeout expired error on SQL Server query with subquery

hi,
i have an ASP page that is querying an SQL database, and a SQL timeout expired '80040e31' error occurs on the recordset.open line.

The query is resonably standard, except there is a correlated subquery to do a COUNT of 'other users' who registered lately against the same school. Is there a way to make the query more efficient, (uncorrelate the subquery or something) so that the timeout error does not occur?

I could take out the subquery, and then do a seperate query on each userID to see if there are 'other users' for that user, but i think that might be less efficient than using the subquery (only about 1 in 10 users has 'other users').

The users table has about 40000 records, and no field is very large, varchar 300 would be about the biggest.

Thanks for your help, hopefully ive given enough information! :-)

Heres the query:
SELECT Users.UserID, Users.FirstName, Users.LastName1, Users.Email, User_school_link.StartYear, User_school_link.FinishYear, Schools.SchoolID,
Schools.SchoolName,
(SELECT COUNT(U.UserID) AS myoutput
FROM User_school_link L INNER JOIN
Users U ON L.UserID = U.UserID
WHERE (L.FinishYear >= User_school_link.StartYear) AND (L.StartYear <= User_school_link.FinishYear) AND (L.AddDateTime >= '1 Dec 2004') AND (L.AddDateTime < '1 Jan 2005') AND (L.SchoolID = Schools.SchoolID) AND (L.UserID <> Users.UserID))
AS NewRegoInfo_COUNT
FROM User_school_link INNER JOIN
Schools ON User_school_link.SchoolID = Schools.SchoolID INNER JOIN
Users ON User_school_link.UserID = Users.UserID
WHERE (User_school_link.UserNotify = 1)
ORDER BY Users.UserID, User_school_link.StartYear

ok, there is not much i could do to the query to improve the efficiency - i didnt need to link on the users table in the subquery, so i took that out and it ran a few seconds faster in query analyser.

But I did a bit of research and i think the 'missing link' was indexes. The only indexes i had on these tables were the primary key indexes that SQL generates automatically when the tables are created. So i added indexes on the other columns in the user_school_link table that were specified in the WHERE clause (user_id, school_id, startyear, finishyear, adddatetime) and now the query runs with no timeouts! i dont know if the changes are completely optimal (not a dba) but it did seem to improve the query performance - only one record at a time is inserted or updated on the website, so the extra work SQL Server needs to perform because of the indexes seems to be minimal compared to performance gains the indexes give to the SELECT queries.

maybe someone who is a dba can offer advice on the best columns to index? (for future reference)

The design of the user_school_link table is as follows:
Name datatype length allow_nulls
LinkID int 4 0
UserID int 4 1
SchoolID int 4 1
FriendID int 4 1
UserNotify bit 1 0
StartYear int 4 1
FinishYear int 4 1
PrimarySchool bit 1 0
AddDateTime datetime 8 1
ModifyDateTime datetime 8 1

In general you should index any primary or foreign keys. Off the top of my head I would think you would want to index

UserID in the "User Table"
SchoolID in the "School" table
FriendID in the "Friends" table

and so on...

Plus any field you maybe using in a WHERE clause.

what about using index on temp table?