1
\$\begingroup\$

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
\$\endgroup\$
  • 2
    \$\begingroup\$ More importantly, do you have the proper indexes on your tables? What is the result when you prepend ANALYZE to your query? \$\endgroup\$ – 200_success Oct 3 '13 at 21:09
  • 1
    \$\begingroup\$ 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 \$\endgroup\$ – mitomed Oct 3 '13 at 21:17
  • 1
    \$\begingroup\$ 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." \$\endgroup\$ – user8128167 Oct 17 '13 at 1:57
  • 1
    \$\begingroup\$ Probably the biggest gain you could gain in performance would be to ensure all your tables have a clustered index. \$\endgroup\$ – user8128167 Oct 17 '13 at 3:23
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Dave Oct 29 '13 at 22:39
4
+50
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ I don't think we are going to get much more answer than this, thank you for taking a look at it! \$\endgroup\$ – Malachi Oct 31 '13 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.