Re: Left Join takes too long
Mike, I used Explain and I don't see anything that says indexes. I use the Control Center for all of my stuff. Would it show in there? 88 rows are returned both in mySQL and Access. >>> 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
Re: Left Join takes too long
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
Re: Left Join takes too long
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left Join takes too long
At 02:18 PM 1/26/2004, you wrote: 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? You can also use "EXPLAIN Select Products. ... " to see if it is using indexes on all the joins. How many rows are returned from the select statement? The same number as MS Abcess? Don't forget that MS Abcess is not a client server database so will tend to run faster for a single user than a C/S database. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left Join takes too long
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
Re: Left Join takes too long
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
