SQL with several joins

Question

Is this query acceptable in terms of performance (I get the correct data) or can it be optimized?

SELECT
 bankInstitution.name,
 person.firstName,
 person.surname,
 offer.offerId,
 bank.personId,
 bank.bankOtherName,
 bank.sortCode,
 bank.number,
 loan.loanId
 offer.campaignId,
FROM bank
 JOIN loan ON bank.personId = loan.personId AND bank.isCurrent = 1
 JOIN offer ON loan.loanId = offer.loanId
 JOIN person ON person.personId = bank.personId
 JOIN bankInstitution ON bankInstitution.bankInstitutionId = bank.bankInstitutionId
WHERE offer.CampaignId = 1 AND offer.Processed Is NULL

• 200_success: More importantly, do you have the proper indexes on your tables? What is the result when you prepend ANALYZE to your query?
• mitomed: Well, didn't even know about this ANALYZE. Will do it in the morning as I don't have the laptop with me. Thanks all
• user8128167: If you execute the command "SET SHOWPLAN_XML ON;" then execute your query, and click on the text link displayed, it will give you show you the query plan execution, and how much time it is spending on each step. When you are finished, type "SET SHOWPLAN_XML OFF;" This will show you were to focus your optimization efforts. Alternatively, you could highlight your query, then right-click and choose "Display Estimated Execution Plan."
• user8128167: Probably the biggest gain you could gain in performance would be to ensure all your tables have a clustered index.
• Dave: The next step is to make sure your joins are configured the same as the indexes. For maximum speed those joins would be exactly the indexes. For multiple fields in an index it is preferable to join on the index in the same order as the index.

Answer 1

There is an illegal comma before FROM.

I would put bank.isCurrent = 1 in the WHERE-clause instead of as a join condition.

For consistency, I would reverse the equalities in the join conditions:

• JOIN loan ON loan.personId = bank.personId
• JOIN offer on offer.loanId = loan.loanId

... to match ...

• JOIN person ON person.personId = bank.personId
• JOIN bankInstitution ON bankInstitution.bankInstitutionId = bank.bankInstitutionId

None of the above affects performance. What you have is a very ordinary joining of tables, and your query expresses it in the usual way. There's not much more that can be done for performance by tweaking the query.

What you should do, though, is ensure that all of the joins are being performed using indexes, not full-table scans. You can verify that by prepending ANALYZE to your SELECT, which will give you the query execution plan. If any of the joins is being performed with a full-table scan, create an index on the relevant column. If your schema is properly defined, with the PRIMARY KEYs and FOREIGN KEYs declared, then you should already be fine, I think.

• Malachi: I don't think we are going to get much more answer than this, thank you for taking a look at it!