Hello,

I have the following query:
SELECT
Loans.ID, Loans.IDBarcode, Loans.IDBorrower, Loans.LoanDate, Loans.DueDate, Loans.Notes
FROM Loans
INNER JOIN Barcodes ON Loans.IDBarcode = Barcodes.ID
INNER JOIN Catalogue ON Barcodes.IDCatalogue = Catalogue.ID
INNER JOIN Site ON Catalogue.IDSite = Site.ID
INNER JOIN Locations ON Catalogue.IDLocation = Locations.ID
INNER JOIN ReadingLevels ON Catalogue.IDReadingLevel = ReadingLevels.ID
INNER JOIN MaterialDesignations ON Catalogue.IDMaterialDesignation = MaterialDesignations.ID
INNER JOIN Departments ON Catalogue.IDDepartment = Departments.ID
WHERE Loans.IDBorrower = :IDBorrower

with IDBorrower = [a value] the query returns 3 records

in the tables I have the following number of records:
Loans: 6694 rows
Barcodes: 62335 rows
Catalogue: 38971 rows
Site: 31 rows
Locations: 58 rows
ReadingLevels: 60 rows
MaterialDesignations: 45 rows
Departments: 1 rows

all the ID fields are primary keys, all the IDxxxx fields are foreign keys
If I take out the last join (INNER JOIN Departments ON Catalogue.IDDepartment = Departments.ID) I get the result in less then a second. If I add this join I get the result in 4 seconds.

What I'm doing wrong here?

Hi Beni,

> If I take out the last join (INNER JOIN Departments ON
> Catalogue.IDDepartment = Departments.ID) I get the result in less then a
> second. If I add this join I get the result in 4 seconds.

ElevateDB will usually reorder joins to use the smallest table as the
driving table to try and reduce IO, but may not always optimize the query
the best way.  Try adding NOJOINIPTIMIZE to the end of your query so that it
performs the joins in the order that you created them.  In some instances,
it can make a world of difference.


---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com
---------------------------------------

You saved my day!!!

> You saved my day!!!

Great.  Glad I could help.


---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com
---------------------------------------