Changing the sorting order

On SQL, you can just put the SETCURRENTKEY of the order in which you want the record and put your SETRANGE/SETFILTER. SQL-server uses the SETCURRENTKEY to order the data, but it doesn't use the to decide the key to be used to get the data fast.

So for SQL-server, this would be:

TableWarehouseActivityLine.RESET;
TableWarehouseActivityLine.SETCURRENTKEY("Activity Type","No.","Line No.");
TableWarehouseActivityLine.SETRANGE("Whse. Document No.",TableWarehouseShipmentHeader."No.");
TableWarehouseActivityLine.SETRANGE("Whse. Document Type",TableWarehouseActivityLine."Whse. Document Type"::Shipment);
TableWarehouseActivityLine.SETRANGE("Activity Type",TableWarehouseActivityLine."Activity Type"::Pick);
IF TableWarehouseActivityLine.FIND('-') THEN
  REPEAT
   ...
  UNTIL TableWarehouseActivityLine.NEXT = 0;

This of course, would go very bad on a Navision-server.
In this case (you can use this code also on SQL-server):

TableWarehouseActivityLine.RESET;
TableWarehouseActivityLine.SETCURRENTKEY("Whse. Document No.","Whse. Document Type","Activity Type");
TableWarehouseActivityLine.SETRANGE("Whse. Document No.",TableWarehouseShipmentHeader."No.");
TableWarehouseActivityLine.SETRANGE("Whse. Document Type",TableWarehouseActivityLine."Whse. Document Type"::Shipment);
TableWarehouseActivityLine.SETRANGE("Activity Type",TableWarehouseActivityLine."Activity Type"::Pick);
IF TableWarehouseActivityLine.FIND('-') THEN
  REPEAT
    TableWarehouseActivityLine.MARKED(TRUE);
  UNTIL TableWarehouseActivityLine.NEXT = 0;

TableWarehouseActivityLine.MARKEDONLY(TRUE);
TableWarehouseActivityLine.SETCURRENTKEY("Activity Type","No.","Line No.");
IF TableWarehouseActivityLine.FIND('-') THEN
  REPEAT
    ...
  UNTIL TableWarehouseActivityLine.NEXT = 0;

Each FIND launches a new query on the server.

On the client, you have an Object-cache. This cache serves to cache the objects. When you run for example the customer-form the first time, the Navision client has to get the objects (form, table, other objects in those 2 and other objects in previous object, and so on ...) from the server. The client then keeps them in the object-cache so it doesn't need to get them the next time you access those objects. (at least if the object-cache is big enough to contain all of them and other objects you have run in the mean-time).

On the server, you have a DB-cache to avoid that SQL- or Navision-DB-server has to read always from the disk. I noticed with a Navision-DB-server with enough DB-cache that about 90% of the reads come from the DB-cache and 10% come from the disks.

DeSp wrote:

I need to sort in order by "Line No.".
This code sorts it right but is not effective as the key is not suitable for filters. If I use the key like "Line No.","Whse. Document No.","Whse. Document Type" it also sorts right, but is it more effective for SQL Server? I guess it is not, as there is no filter set for field "Line no.".

For this, create an index "Line No.", BUT DO NOT MAINTAIN IT IN SQL! It serves only for the Navision-SETCURRENTKEY.

For the other part, this code is even better, because it read every record only once from the DB and then puts it in a temptable from where reading is a lot faster.

TableWarehouseActivityLine.RESET;
TableWarehouseActivityLine.SETCURRENTKEY("Whse. Document No.","Whse. Document Type","Activity Type");
TableWarehouseActivityLine.SETRANGE("Whse. Document No.",TableWarehouseShipmentHeader."No.");
TableWarehouseActivityLine.SETRANGE("Whse. Document Type",TableWarehouseActivityLine."Whse. Document Type"::Shipment);
TableWarehouseActivityLine.SETRANGE("Activity Type",TableWarehouseActivityLine."Activity Type"::Pick);
IF TableWarehouseActivityLine.FIND('-') THEN
  REPEAT
    tmpTableWarehouseActivityLine := TableWarehouseActivityLine;
    tmpTableWarehouseActivityLine.INSERT(FALSE);
  UNTIL TableWarehouseActivityLine.NEXT = 0;

tmpTableWarehouseActivityLine.RESET;
tmpTableWarehouseActivityLine.SETCURRENTKEY("Line No.");
IF tmpTableWarehouseActivityLine.FIND('-') THEN
  REPEAT
    ...
  UNTIL tmpTableWarehouseActivityLine.NEXT = 0;