In Access 2.0 and mySQL Products.ProductID is indexed but tblInvTransaction.ProductID is not. I tried to set tblInvTransaction.ProductID as indexed but it still took a long time.
>>> Daniel Kasak <[EMAIL PROTECTED]> 1/26/2004 12:18:46 PM >>> Jacque Scott wrote: >I have a fairly simple query where I feel it takes too long to run. > >SELECT Products.NSIPartNumber,Products.Cost, Products.ModelNo, >Products.USPrice, Products.VendorPart, Products.Description , >Products.ProductID, Sum(tblInvTransaction.Qty) AS SumOfQty FROM Products >LEFT JOIN tblInvTransaction ON Products.ProductID = >tblInvTransaction.ProductID Where ((NSIPartNumber Like "%02-001%") AND >(Obsolete<>-1)) Group BY NSIPartNumber; > >By running reduced versions of this query I have narrowed down the >problem to the 'Left Join' clause. There are 6852 records in the >Products table and 45758 records in the tblInvTransaction table. To >run the above query it takes approx. 15 seconds to run in mySQL and if I >run the same query in Access 2.0 it takes approx 5 seconds. > >What am I doing wrong? > > > Have you got indexes on Products.ProductID and tblInvTransaction.ProductID? Dan
