Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 304 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Performance - Insert to temporary table
bbrown
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
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.
0
Answers
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.
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.
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.