Changing the sorting order

DeSp

.......
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 BEGIN
        TableWarehouseActivityLine.SETCURRENTKEY("Activity Type","No.","Line No.");
       IF TableWarehouseActivityLine.FIND('-') THEN;
.......

I have a dataset received by filtering on three fields and ordered by the key fileds, then I need to sort this dataset in order of primary key (Activity Type,No.,Line No.). Does changing the current key to primary run another query for retreiving another dataset with the filters already set but ordered by primary key or it use the same dataset and just reorder it? If it runs another query, this query won't be optimal as the key is not suitable for filters and SQL Server won't be able to retreive data quickly as it'll have to scan through entire table, will it? Is it a right way of changing sort orders?

Thanks in advance.

kriki

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;

DeSp

Thank you, kriki.

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

I thought that as we already had the required dataset, the next query for sorting would simply use this dataset from client cache - so there is no need to query the data again with another key and it just sorts records within this dataset according to the new key.
Why do we have a cache then?

kriki

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

I need to sort in order by "Line No.".

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); 
IF TableWarehouseActivityLine.FIND('-') THEN

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.".

kriki

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;

DeSp

Thanks for you help, kriki.