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

Not sure how else to optimize the key used, I will try several variations and let you know if I get a marked improvement.
Not running SQL on site. So the test I did was with both their server and my PC matched at 2.4Ghz and 1GB RAM.

I have restored the company to ensure all tables/keys are optimized.
I have not tested externally re defrag as they have a support contract and have to get that company to deal with anything external to the application.

Yes, the single key Vendor No. gets my grouping needed.

Have you checked code with client monitor?
Have you tried code without executing triggers on INSERT, UPDATE?

I’m really currious about this…

No, but a good idea, I will try that this evening when I log in.
I have not done this without the INSERT and UPDATE as when I had a key that only worked on some records, the result was quite fast so i have been homing in on the initial filter as the source of the problem, also, due to the slowness, you can see the table that is being searched and it repeats for as many lines as there are to be used.

I will let you know about the client monitor later…

What about a key
“Document Type”, Document No.", “Vendor No.”
that will give you the same sorting, andn should be a lot faster.

As I understood Colin, he is looping through all sales lines and making purchase headers/lines. For every unique vendor goes one unique purchase header.
If the first key is Vendor No. than on vendor no. change, he inserts new purchase header. In your case David, he will have to check on every line, does Purchase Header with Vendor No. from the line, already exists and if not, Insert new PH. That search PH’s is fast cause of small number of PHs, but is repeated on every single sales line, which could be a problem.

In Colins case, only thing that he has to do is to insert new PH on “Vendor No.” change in loop. No search through records on every line, etc…


[quote]
        SalesLine.SETCURRENTKEY("Vendor No.");
        SalesLine.SETRANGE("Document Type","Document Type");
        SalesLine.SETRANGE("Document No.","No.");
[/quote]


He is already filtering on Document type and Document No so adding this to key will not change the sorting of the filtered set.

Indeed, I missed the part where he’s filtering by “Document No.”…

In that case, yes, it would be, probably, .faster to make a key as your suggestion.

I still agree with you thugh that there is probably more to this issue than just the bad key, this order of performance degradation does not seem right.

Still, Colin try the key, and see if it helps.

.Thanks David, I will look at that.

In the meantime I have confirmed the clients server does not need defrag. It is already being done regularly.

I am now waiting on the clients response to them getting an additional server just for NAV and have a pal of mine go in and do a health check.

Due to the huge difference in running the code on my PC compared to their server, I no longer have the same sense of urgency to ‘fix’ something that is not broken but may benefit from improvement.

Have run the client monitor and posted the result if you wish to see. It is in .xls format and about 1.5Mb.

http://colin.justfixit.co.uk/Client Monitor result from create PO from SO.xls

I have added some comments to the xls, in particular, why does it show a posted invoice number? This was a new sales order with no postings. Maybe it is picking up something from another record? I really cannot understand why there are so many references to tables I would not have thought as being part of the process.

I have to admit, this is my first time using the Client Monitor and I am not at all sure I understand the results so no hope of interpreting them.

Any help appreciated.[:$]

Tried the key with the Document Type, Document No., Vendor No. but did not see any improvement. Will leave it in as I agree, it is probably beneficial.

Have changed the priority on the server from Background to Applications.
Now getting excellent speed, 7 purchase orders created from 28 lines in about 10 seconds.

Testing done after hours with just me in, so will be really happy when the client has all his users logged in and working mails etc next week before I get too excited but it is looking good.

Thanks for all your patience and helpful suggestions.

Hm… Than they were experiencing bad overall performance. I think you fixed a greater problem than those that you had with this batch.

Anyhow, great that you’ve find a source problem.

Hi,
I don’t know if I understood your problem but if you need to create as much purchase orders as much vendors you have in the sales lines, you can do this: create a temporary variable based on the Vendor table (call it TempVendor) then do this:

SalesLine.FIND(’-’);
REPEAT
IF NOT TempVendor.GET(SalesLine.“Vendor No.”) THEN BEGIN
TempVendor.INIT;
TempVendor.“No.” := SalesLine.“Vendor No.”;
TempVendor.INSERT;
END;
UNTIL SalesLine.NEXT = 0;

IF TempVendor.FIND(’-’) THEN
REPEAT
PurchHeader.INIT;
PurchHeader.“Document Type” := PurchHeader.“Document Type”::Order;
PurchHeader.“No.” := ‘’;
PurchHeader.INSERT(TRUE);
PurchHeader.VALIDATE(“Buy-from Vendor No.”,TempVendor.“No.”);
PurchHeader.MODIFY;
UNTIL TempVendor.NEXT = 0;

In this way you can read the sales lines in any order you want beacuse in any case you will create as much purchase orders as much vendors you have.

Hope this helps you
Marco

Hi Marco,

Thanks for the suggestion, I will look at that if the performance becomes an issue again. For the moment, the existing code works OK, the problem was the client’s server that was causing the problem, that plus a 4 year old not very fast server.

hi colin,

maybe what you can do is “pre-processing”. you can create 2 new tables (or 1 depending on your choice…). And these 2 new tables will store the Purchase Header and Purchase Line that will be created by your batch job.

which means, whenever a sales line is created, and vendor no. is specified, you are suppose to populate these 2 tables. So later on, what your batch job need to do, is just to loop through the 2 new tables and create the purchase orders, without filtering etc.

it takes the load of filtering on purchase line table.