The first query that you wrote took 5.93 seconds with count of 523. The second query took so long I aborted it. The way I figured it was the 'Left Join' was by running the following query. #1: This takes out the join and sets 'Products.ProductID = tblInvTransaction.ProductID' to join the two tables. This took 1.23 seconds. I only get 15 rows back which with a left join I get 88. SELECT Products.NSIPartNumber,Products.Cost, Products.ModelNo, Products.USPrice, Products.VendorPart, Products.Description , Products.ProductID, Sum(tblInvTransaction.Qty) AS SumOfQty FROM Products, tblInvTransaction Where ((NSIPartNumber Like "%02-001%") AND (Obsolete<>-1) and Products.ProductID = tblInvTransaction.ProductID) Group BY NSIPartNumber; Am I wrong in thinking that it is the join? I just figured it was the 'join' since the difference in time was so drastic. We did run the Access query from the main location where all of our users have access. I can't be 100% sure that someone else was using the DB at that time but I am 98% sure they were.
>>> Brent Baisley <[EMAIL PROTECTED]> 1/26/2004 2:03:10 PM >>> How did you determine the bottleneck was the left join? I would have thought it was the WHERE clause. I'd be curious what the times are on these queries: SELECT count(*) FROM Products LEFT JOIN tblInvTransaction ON Products.ProductID = tblInvTransaction.ProductID Where ((NSIPartNumber Like "%02-001%") AND (Obsolete<>-1)) and SELECT count(*) FROM Products LEFT JOIN tblInvTransaction ON Products.ProductID = tblInvTransaction.ProductID As far as Access comparison, you must compare it to a multi-user Access database hosted on the network with at least 1 other client having the database open, but it can be idle. Comparing it to a local Access database is not a comparable setup. On Jan 26, 2004, at 1:35 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? > -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
