Speed up read/write in buffer

Gonzalo
Member Posts: 19
Hi there,
I've developed a process which has to read/write millions of records and unluckily takes centuries. (Its well known that NAV work with cursos and some cases is not really effective)
This is my code: (it's a easy loop)
IF Header.FINDFIRST THEN
REPEAT
BufferHeader.TRANSFERFIELDS(Cta);
BufferHeader.INSERT;
Lines.SETRANGE("Header No.",Header."No.");
IF Lines.FINDFIRST THEN
REPEAT
BufferLine.TRANSFERFIELDS(Lines);
BufferLine.INSERT;
UNTIL Lines.NEXT = 0;
UNTIL Header.NEXT = 0;
IF PostedHeader.FINDSET THEN
REPEAT
BufferHeader.TRANSFERFIELDS(PostedHeader);
BufferHeader.INSERT;
PostedLine.SETRANGE("Header No.",PostedHeader."No.");
IF PostedLine.FINDFIRST THEN
REPEAT
BufferLine.TRANSFERFIELDS(PostedLine);
BufferLine.INSERT;
UNTIL PostedLine.NEXT = 0;
UNTIL PostedHeader.NEXT = 0;
As you can see I'm inserting the records in a buffer. Do you know if its possible to speed this up? Maybe storing in the buffer table directly from sql, do you recommend me that??
Thanks
I've developed a process which has to read/write millions of records and unluckily takes centuries. (Its well known that NAV work with cursos and some cases is not really effective)
This is my code: (it's a easy loop)
IF Header.FINDFIRST THEN
REPEAT
BufferHeader.TRANSFERFIELDS(Cta);
BufferHeader.INSERT;
Lines.SETRANGE("Header No.",Header."No.");
IF Lines.FINDFIRST THEN
REPEAT
BufferLine.TRANSFERFIELDS(Lines);
BufferLine.INSERT;
UNTIL Lines.NEXT = 0;
UNTIL Header.NEXT = 0;
IF PostedHeader.FINDSET THEN
REPEAT
BufferHeader.TRANSFERFIELDS(PostedHeader);
BufferHeader.INSERT;
PostedLine.SETRANGE("Header No.",PostedHeader."No.");
IF PostedLine.FINDFIRST THEN
REPEAT
BufferLine.TRANSFERFIELDS(PostedLine);
BufferLine.INSERT;
UNTIL PostedLine.NEXT = 0;
UNTIL PostedHeader.NEXT = 0;
As you can see I'm inserting the records in a buffer. Do you know if its possible to speed this up? Maybe storing in the buffer table directly from sql, do you recommend me that??
Thanks
0
Answers
-
Hey Gonzalo,
First of all I will do some tiny changes in your code.
1. Use Findset instead of Findfirst.
2. Why are you not initializing before Transferfields??
Now if you just need to transfer records from 1 table to another and if it has many records then obviously it will takes time as there is one to one transmission.
I will suggest to write SQL Stored procedure in that case and call it from NAV. It will do it in chunk of seconds.Thanks
Blog - rockwithnav.wordpress.com/
Twitter - https://twitter.com/RockwithNav
Facebook - https://facebook.com/rockwithnav/0 -
Thanks for your help, I'll try to write SQL stored procedure.0
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
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions