4GL vs. ODBC Performance Backwards?

[ChezJfrey]

An SQL statement run through ODBC in MSAccess takes 20 seconds to return a result set. My equivilant in Progress takes 35 minutes to iterate the records in a 'FOR EACH'. Both are displayed below. Can anyone pinpoint possible syntactical oversights that may be hindering the Progress performance? I even eliminated all the joins from the Progress code and left only the criteria for the ProductionTran table and it makes no difference. . . it still takes far too long.

SQL statement in MSAccess through ODBC:

SELECT DISTINCTROW ProductionTran.[System-ID], [system-file].[Company-Name], ProductionTran.[Dept-ID], Department.[Dept-Desc], ProductionTran.Shift, ProductionTran.[Work-Center-ID], WorkCenter.[Work-Center-Desc], ProductionTran.[Operation-ID], Operation.[Operation-Desc], ProductionTran.[Employee-ID], Sum(ProductionTran.Hours) AS SumOfHours, Sum(ProductionTran.[Qty-Gross]) AS [SumOfQty-Gross], Sum(ProductionTran.[Qty-Net]) AS [SumOfQty-Net]
FROM (((ProductionTran
INNER JOIN [system-file] ON ProductionTran.[System-ID] = [system-file].[System-ID])
INNER JOIN Department ON (ProductionTran.[System-ID] = Department.[System-ID]) AND (ProductionTran.[Dept-ID] = Department.[Dept-ID]))
INNER JOIN WorkCenter ON (ProductionTran.[System-ID] = WorkCenter.[System-ID]) AND (ProductionTran.[Work-Center-ID] = WorkCenter.[Work-Center-ID]))
INNER JOIN Operation ON (ProductionTran.[System-ID] = Operation.[System-ID]) AND (ProductionTran.[Operation-ID] = Operation.[Operation-ID])
WHERE (((ProductionTran.[Date-Actual]) Between #2/10/2002# And #2/16/2002#) AND ((ProductionTran.[System-ID])="001") AND ((ProductionTran.[Operation-ID])<"700" Or (ProductionTran.[Operation-ID])>"799"))
GROUP BY ProductionTran.[System-ID], [system-file].[Company-Name], ProductionTran.[Dept-ID], Department.[Dept-Desc], ProductionTran.Shift, ProductionTran.[Work-Center-ID], WorkCenter.[Work-Center-Desc], ProductionTran.[Operation-ID], Operation.[Operation-Desc], ProductionTran.[Employee-ID];

Progress 4GL code:

FOR EACH ProductionTran
FIELDS (System-ID
Dept-ID
Shift
Work-Center-ID
Operation-ID
Shift
Hours
Employee-ID
Qty-Gross
Qty-Net
Date-Actual)
WHERE ProductionTran.Date-Actual >= 02/10/2002
AND ProductionTran.Date-Actual <= 02/16/2002
AND ProductionTran.System-ID = "001"
AND (Production.Operation-ID < '700'
OR Production.Operation-ID > '799') NO-LOCK:

END.

 

[ChrisPaulson]

Try :-


FOR EACH ProductionTran
WHERE
(ProductionTran.Date-Actual >= 02/10/2002
AND ProductionTran.Date-Actual <= 02/16/2002
AND ProductionTran.System-ID = "001"
AND (Production.Operation-ID < '700')
or
(ProductionTran.Date-Actual >= 02/10/2002
AND ProductionTran.Date-Actual <= 02/16/2002
AND ProductionTran.System-ID = "001"
AND (Production.Operation-ID > '799')

NO-LOCK:

END

or two for each loops based around each or.

 

[ChezJfrey]

Oversights abound

Thanks for the reply. It's amazing what a night's sleep and some helpful advice will do. I must admit that the 4GL code I posted was not actually what I was running. I had the Operation-ID exlusions in a preprocess string and when I checked my code this morning I noticed that the 'OR' clause was not encapsulated in parens. The braces for my preprocessor directive made this oversight easy. . . they resembled parens. Your suggestion made this very clear to me this morning. Added them and voila, fast as can be. Thanks for the help, sorry to say I was jumpin' the gun so to speak.