advice on stored procedure - is it optimized?

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;

Open in new window


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
Josh
LVL 34
Big MontyWeb Ninja at largeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
We need to see your execution plan.  Specifically you want to make sure all of your joins are using index seeks.  Within SSMS you can display the actual execution plan by hitting ctrl-m and then running the query.
Big MontyWeb Ninja at largeAuthor Commented:
will this show anything with only a few rows of data in the tblExchanges table?
Big MontyWeb Ninja at largeAuthor Commented:
also, i haven't added indexes yet (something I need to research how to do), will that vastly affect the execution plan?

i'm just starting to build this functionality so not all pieces are in place yet
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Brian CroweDatabase AdministratorCommented:
Yes indexes are the biggest factor in optimizing a query like this.  You have to keep in mind that each index adds overhead any time you insert or delete records in a table so you have to weigh them accordingly.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
what is your sql server version\edition\sp?

how many records do you have in each table?
What is their growth forecast?
What are the involved in this query tables - columns data types?

Will you use another searches (probably 'yes')?  post some examples.


for now-- > review for possibility to add clustered indexes to the tables
and
NONCLUSTERED as well:
for example:

CREATE NONCLUSTERED INDEX [IDX_ tblExchanges _exchangeID] ON [dbo].[ tblExchanges]
(
      exchangeID
)
--

more:
SQL Server Index Design Guide
https://technet.microsoft.com/en-us/library/jj835095(v=sql.110).aspx
Vitor MontalvãoMSSQL Senior EngineerCommented:
If all tables has primary keys and foreign keys (FK) then you just need to assure that all FK are indexed.
Big MontyWeb Ninja at largeAuthor Commented:
thx everyone for the feedback, I paln on looking at this more in depth tonight and will report back in the next day or so
Big MontyWeb Ninja at largeAuthor Commented:
thanks for the feedback!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.