Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Help optimise a query |
Tue, Feb 26 2013 1:06 PM | Permanent Link |
Beni | 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? |
Tue, Feb 26 2013 1:33 PM | Permanent Link |
Terry Swiers | 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 --------------------------------------- |
Tue, Feb 26 2013 1:38 PM | Permanent Link |
Beni | You saved my day!!!
|
Tue, Feb 26 2013 3:35 PM | Permanent Link |
Terry Swiers | > You saved my day!!! Great. Glad I could help. --------------------------------------- Terry Swiers Millennium Software, Inc. http://www.1000years.com http://www.atrex.com --------------------------------------- |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |