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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
