efficient query for many to many table

Question

Hi all,

I'm writing a unit test of my mock-up repositoy. While the real repository lays on the linq2sql, the mock-up only use list of linq2sql objects. When it comes to the many to many tables, the efficency is rather low.

Here is the senario.

I have a many2many table which is tablePersonInCommunity, stores a CommunityID and a PersonID. And also a many2many table tablePersionInContact, stores a ContactID and a PersionID. The ContactID points to a entity of the Contact table. which have the following structure:

ContactID int, ContactType int(Business or Home), Address1 string, Address2 string, ...

Now, I wanna to get a record looks like this: PersionID, BusinessContact, HomeContact

What is the most efficent solution? my simple test including 100 Persons' infomation caused a time-out when Im testing against the list mock-up.

Thanks in advance!

Answer 1

The answer will be in making sure you have the right indexing strategy for your tables.

So... on your many-to-many table, have an index on (ContactID, PersonID), and another on (PersonID, ContactID), so that your query can cope with looking up people by contact or contacts by person.

Rob