Performance - Insert to temporary table

bbrownbbrown Member Posts: 3,268
I have a Repeat\Until loop that builds records and inserts them into a temporary table. The table's primary key is "Entry No." with the records being inserted in increasing "Entry No." order.

Creating and inserting 250 records takes about 2.5 minutes. If I comment out the INSERT statement, but let all the record create\validate code run, the process only takes about 1 second to loop for the 250 records.

Any thoughts on where the performance bottleneck is? And how it could be improved?

More info:

The table has no event subscribers, and is not tracked in change log. Change log is not even active.

This is NAV 2017
There are no bugs - only undocumented features.

Answers

  • txerifftxeriff Member Posts: 492
    Well it is hard to say since we dont know how that data is populated.

    when you say a temporary table is it a record with TEMPORARY=yes right? make sure that is enabled, lol.

    I would suggest to debug it and see what is doing after insert etc.
  • bbrownbbrown Member Posts: 3,268
    Thanks for the reply.

    Yes, the table is set with TEMPORARY=yes. I have debugged. That's how I was able to isolate this as a problem. The problem is the time the insert itself takes. As the problem goes away if the insert is commented out. Thus I'd say what it's doing after the insert is not relevant. Of course, that code does not run since the temporary table is now empty.
    There are no bugs - only undocumented features.
  • bbrownbbrown Member Posts: 3,268
    txeriff,

    Thanks again for the reply. Your reply got me thinking and taking a closer look at the code. This was a process that was written several years ago and the customer has been using since. But with much smaller transaction sets. Only with their recent increase in transaction size, have the underlying performance issues become apparent.

    I tracked this down to some code that was doing some unneeded processing. it was running after each record was inserted to the temporary table. Rather than only once after all the records were added. This resulted in that code running 250 times instead of just once. Correcting this got the processing of the 250 records down to just 8 seconds.

    I got a few more things to look at, but I think this was a big part of the problem. Again, thanks for the reply.
    There are no bugs - only undocumented features.
Sign In or Register to comment.