Use of temporary tables to speed up search of ever increasing number of records

The problem I am having is with a codeunit that is used to create the Purchase Orders from the Sales Lines. It is called from the Sales Header. There is a Vendor No. for each line and could be many Vendor No.s, hence many Purchase Orders. The sort by Vendor No. is critical.

As the number of sales order lines increases, the time to create the POs is getting much too long. For 26 lines, it took 5.5 minutes where when not so many lines were in the sales lines table several months ago it would take about 3 seconds.
As time goes by, this problem will get worse. I am wondering how to speed things up and thought perhaps that if I use a setrange to get ALL the lines in the curent order (fast) and put them into a temporary table and then sort that by Vendor No. etc.
Is this possible or should I have a new table and work from that, or can you suggest any other way to speed up the process?

This is my first attempt but I am getting “no lines found” so presumably the code is rubbish.

WITH SalesHeader DO BEGIN
TESTFIELD(“Document Type”,“Document Type”::Order);

SalesLine.LOCKTABLE;
IF NOT RECORDLEVELLOCKING THEN
LOCKTABLE(TRUE,TRUE); // Only version check
/// new code
SalesLine.setrange(“document no.”,“No.”);
if salesline.find(’-’) then
REPEAT
TempSalesLine.COPY(SalesLine); ///***is this correct??
TempSalesLine2.COPY(SalesLine);
UNTIL SalesLine.NEXT = 0;

TempSalesLine.SETCURRENTKEY(“Vendor No.”); ///***is this possible in a Temporary table?
TempSalesLine.SETRANGE(“Document Type”,“Document Type”);
TempSalesLine.SETRANGE(“Document No.”,“No.”);
TempSalesLine.SETRANGE(“Drop Shipment”,TRUE);
TempSalesLine.SETRANGE(“Purch. Order Line No.”,0);
TempSalesLine.SETRANGE(Type,TempSalesLine.Type::Item);
TempSalesLine.SETFILTER(“Outstanding Quantity”,’<>0’);
TempSalesLine.SETFILTER(“No.”,’<>%1’,’’);
TempSalesLine.SETFILTER(“Vendor No.”, ‘<>%1’, ‘’);

IF NOT TempSalesLine.FIND(’-’) THEN
ERROR(Text000,“No.”) ///currently no records are found
ELSE BEGIN
(remainder of code)

Many thanks,
Colin

setting keys on temp tables is possible afaik but, if you have a lot of records, first bottleneck is copying all those records to temp tables (twice! what for is TempSalesLines2?). another thing is that you set key on “vendor no.” but you applied vendor filter at the end, after all other filters. Besides, I’m pretty sure that there isn’t such a key on SalesLines (if you didn’t add it).

I added the Vendor No. key as you surmised.
The order the setrange or setfilter is not important as far as I know.

We only need to copy the lines associated with that particular sales header, so max number of lines is about 100

The second temp table is for another part of the code where I need to get the related extended text lines copied to the purchase line. Normally, Navision assumes (incorrectly) that all you want is what is set up for the Item. If you have for example 2 doors to order, 1 has a complex specification different to the other, what I do is copy the extended text lines as is and also allow the user to add more ‘related’ lines.

now, let’s talk about algorithm.
You have N sales orders with M sales lines for all sales orders.

Some of them (or all of them?) you want to transfer to purchase orders?
I assume that, at the end, only some of them will be transfert to PO (you don’t want some description lines, or resources lines, etc…).
But you transfer all of the lines and than you apply filters. Why don’t you apply filters on SalesLines than copy to tempatbles only right ones?

Secondly, you said that for 100 lines (or orders?) it takes few minutes to copy all of them? 100 lines isn’t too much even for such number of filters that you are applying… 100 orders with e.g. 10 lines, iz 1000 lines and even that number is low though copying of 1000 lines to 2 tables is worth of thinking.

Just to add, reconsider that key that you make on SalesLines. Try to add some more fields to it, I think it is not a problem cause, SalesLines is usually filled with “approx the same” and probably low number of records… If you don’t need sumindex on it, disable them (probably you need this only for sorting purposes, to make POs by vendors(

Always 1 order only.
So, max number of lines in that single order is not more than 100

I have (of course) updated the lines for that single order once the purchase order lines have been created.

I do only wish to get the lines for that single order, copy them to a temporary table and then sort them by Vendor no and create several purchase orders with appropriate lines.

I have to say again, the system works perfectly now.
I copy the extended text lines, again, all is working perfectly.

The only problem is the length of time it is taking now versus when the number of overall sales lines were low.

This question is only about tweaking it to get the speed up to an acceptable level, not to change the end result.

These questions could be helpfull…

Do you have to copy all of the lines to two temptables?
Could you apply filters on SalesLines than copy filtered lines to two temptables?

could you use only one temptables?
could you add more fields to that key for faster filtering?

and second,
are you sure that the problem is in this part of code and not in the remaining part (that you didn’t paste here)?

The reason to copy all the lines is I need the Type::Item and Type::’’ and that is the majority if not all of the lines.

My code example should get ALL lines for that order, that is the point of the single filter in the first instance.

Using 2 temptables is required but as yet I do not have any resulting entries so there must be something wrong with the code snip I showed you.

The key is critical for the temptable, I have to sort by Vendor No., if I add other fields to the key, the sorting does not work, I must group by Vendor No. or else I get several purchase orders for the same vendor and not a single purchase order per vendor.

The remaining code will not help much I think, the only change is in the first part and the questions remain, 1) is the code correct (I think not) and where is the fault and 2) is there a better way to think about this.

The real problem is that the current code has to check every record in the sales line table to find records matching the filter.

It has to do this for each line, so if there are say 5,000 sales oorder line records to check I am sure if i can get just the records for that order, using the primary key first, say 20 lines, then the iteration must be faster.

I will see if I can put the existing (working) codeunit up in pdf format onto a server for anyone to download.

the code can be found:
http://colin.justfixit.co.uk/Codeunit50003CreatePOfromSO.pdf

also in txt format

http://colin.justfixit.co.uk/Codeunit50003CreatePOfromSO.txt

not sure if that is normal.

This is the existing codeunit that is working OK but very slow.

“The page cannot be found”.
Something is wrong with links…

What do you meen by “Check lines one by one for filters”?

When I sad, add fields to Vendor, I meant, Add more fields in addition to vendor No.
In that case, you will not loose Vendor grouping, just will have additional sorting by, let’ say “No.” or “Outstanding Amount” so, your filters will react faster.

I really, don’t see anything wrong with this code if we talk for about 100 of lines. Keys aren’t so importart (regarding speed of execution) if there’s 100 lines. Try not to do COPY but TRANSFERFIELDS instead. I’m really not sure which one is faster.

Sorry, the link works now I have put the files in the correct place.

I think you are asking just how to speed up the process, not to change it. So here are some considerations that you need to take into account when sorting/selecting.

  1. You need a key that mimics the selects that you are going to do. At least most of them.

So your setcurrentkey(“vendor no.”) is not suffiecient based upon all the setfilter/setranges you are doing. you need to add a key that has “Document Type”, “Document No.”, “Drop Shipment”, “Order Line No.”, Type, “Outstanding Quantity”, “No.”, “Vendor No.”.

by not including these fields in the key, then the search engine will not be able to use the key fully and still have to do a hard record search for the reamaing values.

  1. You need to be sure that your order of the key and the setrange/filter is in the exact same order.

This is important on any search!!

(i.e. Place vendor no at the beginning of your fiter/range code if it is at the beginning of your key)

I also agree you should be setting the key on the purchase line to get the records to populate the temptable then they will aready be in the correct order not changing the temptable order after it is created.

I’m kind of confused now regarding links you gave us. You are trying to tune this code (and are doing that with temp tables) or you give up of temptables and now trying to tune this code further?

There is a lot of code here, few updates, inserts with validation triggers “on”, etc…
Maybe not a code that needs few minutes to run, but certainly some significant amount of time.
One thing that I notices is LOCK inside CreateHeader function. I would rather lock on the beginning and only there cause, once it locks the record it will not release that lock until the end of transaction (and transaction will end upon the end of codeunit), right?

does not work, everything is nice and fast but I only get some of the lines for the same vendor into a Purchase Order.
Anytime I change the key from Vendor No. only to anything else, I get the same problem.

Agree about the LOCK, I have removed it with no appreciable benefit so have left it for the moment.
Have tried to use a key that is identical to the main set of filters:

SalesLine.SETCURRENTKEY(“Vendor No.”,“Document Type”,“Document No.”,
“Drop Shipment”,“Purch. Order Line No.”,
Type,“Outstanding Quantity”,“No.”);
SalesLine.SETFILTER(“Vendor No.”, ‘<>%1’, ‘’);
SalesLine.SETRANGE(“Document Type”,“Document Type”);
SalesLine.SETRANGE(“Document No.”,“No.”);
SalesLine.SETRANGE(“Drop Shipment”,TRUE);
SalesLine.SETRANGE(“Purch. Order Line No.”,0);
SalesLine.SETRANGE(Type,SalesLine.Type::Item);
SalesLine.SETFILTER(“Outstanding Quantity”,’<>0’);
SalesLine.SETFILTER(“No.”,’<>%1’,’’);
This has the unfortunate result of not grouping by vendor No. and then only some of the lines for a specific Vendor will be used on the PO.

I have not abandoned the idea of the temporary tables, just that if I cannot get speed improvements any other way, they may be worth looking at.

What do you meen “not grouping by Vendor No.”.
If your first field is Vendor No. than you have grouping by it.

Another idea:
have you used client monitor to check, at least, on which part is major processing time? if lucky, you could narrow to single function that is making a lot of processing (or I/O).

Btw, just to be sure, try to optimize those tables first (through Database => tables tool).
The suspicious function that may cause lot of processing is INSERT(TRUE). I think you have it on PurchaseHeader, PurchaseHeader.INSERT(true). That trigger has a lot of code behind. If you have CheckLog and Dimensions turned on, that can add some time too.
Bottom line is, every validation trigger on insert/modify, on sales lines/headers, purchase lines/headers, fires up a lot of calculation (and other) code…

You should not skip those triggers but just for a test, try to run it with FALSE instead of TRUE.

Yes using a Temporary table will speed things up. Though we can never be sure by how much. The issue is that because you really have no key, the more sales lines in the system the slower the function will be. Try the code above, it should work (changes in bold.)

But one question, why don’t you use the standard Navision function to create drop ships, it does basically wha you are doing here?

As far as I know, you cannot run a function in the Sales Header to create several purchase orders at once and one for each vendor (in standard navision).
You can use the requisition worksheet, but that is a lot more work for the operator who needs to “take a sales order and create several linked purchase orders from that sales order”.

The other option is to Get Sales Lines from the Purchase Order, not really useful when you have many sales lines, takes a lot of time to go from the sales order, open the purchase header, F3 etc.

Thanks for the code suggestions David, I will have a look at that.
In the meantime, I have restored the customer database onto my local PC and created 7 Purchase orders from 21 lines on the slaes order in 15 seconds.
This is taking 4 to 6 minutes on the customers site. Not a big one, just 6 users at any one time normally.
I now need to establish why such a big difference. My PC is not a mighty beast but does have 1Gb RAM.
Maybe my code is not so inefficient after all. Still, it will be worth the time to see if i can improve it.
Will report back in due course. Thanks for all the suggestions so far.
[:)]

By the way, I really don’t think the code I posted is ideal, and definitely its not the way I would do it. I think that if you don’t want to work witht he Req Work Sheet, then you should look more at the earlier suggestions about using a better key. By the way, is there any chance that the client is running on SQL? and you are locally testing on Native DB? With a key like the one you are using, and filtering like that it will cause problems.

@Colin,
problem could be cause of non-optimized tables at customer site, defragmented files, etc, that causes low i/o operations…

@David,
i think the main reason for Vendor No. key at first Colin’s code is Grouping by Vendor No…