A little background, I have a site where people can come on and exchange goods and services with one another, without money being exchanged. This page I'm working on is where I want the users to be able to see a list of all of their exchanges with other users, similar to an inbox. Once they click on an individual record, I was a "full detail view" to appear. This includes information about each person, along with info about what they want to offer. I came up with the following sql that pulls all of the data:
select e.exchangeID, e.initialProposeDate,
proposeByID = u1.userID, proposedBy = u1.displayName, proposeByCategory = c1.category, s1.service, proposeByItem = o1.offer, fileID = isNull( uo1.fileID, 0 ),
proposedTo = u2.displayName, proposeByCategory = c2.category, s2.service, proposeByItem = o2.offer, fileID2 = isNull( uo2.fileID, 0 )
from tblExchanges e
inner join tblUserOffers uo1 on uo1.userOfferID = e.proposedByItem
inner join tblUserOffers uo2 on uo2.userOfferID = e.proposedToItem
inner join tblOffers o1 on uo1.offerID = o1.offerID
inner join tblOffers o2 on uo2.offerID = o2.offerID
inner join tblCategories c1 on o1.categoryID = c1.categoryID
inner join tblCategories c2 on o2.categoryID = c2.categoryID
inner join tblServices s1 on c1.serviceID = s1.serviceID
inner join tblServices s2 on c2.serviceID = s2.serviceID
inner join tblUsers u1 on u1.userID = e.proposedBy
inner join tblUsers u2 on u2.userID = e.proposedTo
where e.exchangeID = @exchangeID;
my concerns are that after awhile, this route may slow down as data accumulates in all of the tables because of all of the inner joins. A brief explanation of the different tables:
tblExchanges - the table where exchanges between two users are stored.
tblUserOffers - the table where each user stored data about each individual offer.
tblOffers / tblCategories / tblServices - the tables where general data is stored about each offer. When a user is setting up what they want to offer, they are presented with dropdowns of how to categorize the offer, as well as a dropdown of a list of offers they can offer. that data is stored in these tables
tblUsers - the table where user info is stored
Even though only one record will ever be returned, is there a better way to write it to ensure speed? I'll be adding indexes to the tables, are there any tips / suggestions?
Thx in Advance