A Need for Speed....

[LarryP]

I have a query (full SQL below) that's used in a procedure which promotes
records through a sequence of "gates" involved in a material handling
process. Sometimes, a promotion can bypass gates, e.g., item(s) can move
from gate 2 directly to gate 5. This query looks through a table and finds
records where (a) the batch flag is on and (b) the "to" gate is > 1 above the
"from" gate. (I can't just assume that because 100 items were flagged, all
of them involve a bypass; some may be moving only one gate, others may jump
three or four). Whenever those conditions are met, it creates a new record
in a comments table identifying the fact that certain gates were bypassed.

I have it running fine, but it's a bit slower than I like when mucho records
are flagged. I ascribe that to the fact that it has to run a function twice
against each record. The function determines (via a DLookup) what the "from"
gate is, which is needed both for the selection criterion and to build the
"gate x to y" comment string for the output.

Would welcome any ideas on ways this can be streamlined to run faster.

******************** SQL *****************************

INSERT INTO tblLICommentLog ( LIIDNo, GateNo, LogonID, CommentDateTime,
Remarks )
SELECT tblContractLIData.LIIDNo, GetBatchPromoteTargetGate() AS Expr1,
Environ("UserName") AS Expr2, Date() AS Expr3, "LI BYPASSED GATE(S) " &
GetCurrentActiveGate([LIIDNo])+1 & " THRU " & GetBatchPromoteTargetGate()-1
AS Expr4
FROM tblContractLIData
WHERE (((tblContractLIData.BatchFlag)=True) AND
(([CurrentActiveGate]<(GetBatchPromoteTargetGate()-1))=True));

 

[John W. Vinson]

I have it running fine, but it's a bit slower than I like when mucho records
are flagged. I ascribe that to the fact that it has to run a function twice
against each record. The function determines (via a DLookup) what the "from"
gate is, which is needed both for the selection criterion and to build the
"gate x to y" comment string for the output.

Well, calling the function without any argument will give unexpected and
perhaps wrong records in any case; to "save time" Access will call it once and
once only at the start of the query, not call it on every row.

And the DLookUp is certainly going to be a major drag on performance!

What's the structure of your table? Why do you need a Function, much less a
DLookUp, rather than some sort of join? Could you post some field definitions
and perhaps some sample data with the desired result?

John W. Vinson [MVP]

 

[LarryP]

Thanks, John, I think I've resolved this. (Slapping my forehead here...)
The function doing the DLookup was returning a value that's already sitting
right there in a field in the table! The other function (I didn't describe
it that way, but there were actually TWO different functions being run for
each record) is no biggie, it's just returning a number stored in a global
variable. Six minutes the old way, six seconds or less the new way for
8000+ records. Guess it just took staring at it long enough. Lesson
learned, though -- avoid Domain Aggregate functions when they have to be fun
against a slew of records.