Slow search

Adri

Working with NAV 2009.

I have a query on Item ledger entry which is slow.
It seems, that the first 2 iterations take a lot of time (about 1 minute), but after that it is fast (within the second).
The code I use:

ILE.RESET;
ILE.SETRANGE(ILE."Item No.", "Item No.");
ILE.SETRANGE(ILE.Open, TRUE);

ILE.SETCURRENTKEY("Item No.",Open,"Variant Code","Location Code","Item Tracking","Lot No.","Serial No.");

IF ILE.FINDSET
THEN REPEAT
<create temp table records>
UNTIL (ILE.NEXT = 0);

When I close the program and reopen it there is no delay. But when Navision is closed and reopened the same problems pops up.

Slawek_Guzek

There are 3 other keys on Item Ledger Entry table starting from "Item No.",Open fields - have you tried another one?

Yury

Adri, put SETCURRENTKEY before SETRANGE

Slawek_Guzek

How many entries do you have in your Item Ledger Entry?
How many enties, on average, are you expecting to copy to temp table in your loop?

Adri

about 4.5 milion

Slawek_Guzek

Are you on SQL or Native?

Adri

SQL

Slawek_Guzek

There are 3 other keys on Item Ledger Entry table starting from "Item No.",Open fields - have you tried another one?

Adri

Thank you for your help!

Yury

Adri, put SETCURRENTKEY before SETRANGE