Import Excel file to Update Purchse Order Lines performance issue

(0) Share

Report

Posted on by Community Member

Hello, mates.

I want to import an Excel file and update the Purchase Line Prices of a Purchase Order. to do that I get three columns from an Excel file (I know I can do it via DIXF. but I have preferred to do it with the import of an excel file)

lineNumber

PurchId

PurchPrice

Then with the following listing I am updating the lines. The problem is weird. sometimes it does it very fast and fine. sometimes it is extremely slow. I tried to debug that and I found that for the first 20 records, it does it very fast. but suddenly it sticks and does the rest one by one. each of them takes about one minute to update.

The problem is weird. sometimes it does it very fast and fine. sometimes it is extremely slow. I tried to debug that and I found that for the first 20 or 30 records, it does it very fast. but suddenly it slows down and does the rest one by one. each of them takes about one minute to update.

public static void main(Args _args)
{
    Dialog          dialog;
    Filename        filename;
    DialogField     dialogFilename;

    container      conFilter = ["Microsoft Excel 97-2003 Worksheet (.xls)" ,"*.xlsx"];

    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;

    int row = 1; //Excel File has Header
    int doneCntr=0;

    LineNumber lineNumber;
    PurchId    purchId;
    PurchPrice purchPrice;
    PurchLine  purchLine;


    dialog = new dialog();
    dialog.caption("select a file");
    dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen));
    //To filter the files while selecting
    dialog.filenameLookupFilter(conFilter);
    dialog.run();

    if(dialog.closedOk())
        {
            filename = dialogFileName.value();

            application = SysExcelApplication::construct();
            workbooks = application.workbooks();
            //specify the file path that you want to read
            filename = filename;
            try
            {
              workbooks.open(filename);
            }
            catch (Exception::Error)
            {
              throw error("File cannot be opened.");
            }

            workbook = workbooks.item(1);
            worksheets = workbook.worksheets();
            worksheet = worksheets.itemFromNum(1);
            cells = worksheet.cells();
            
                do
                {
                    row++;
                    lineNumber = cells.item(row, 1).value().double();
                    purchId =    cells.item(row, 2).value().bStr();
                    purchPrice =  cells.item(row, 3).value().double();
                    type = cells.item(row+1, 1).value().variantType();

                    select forUpdate purchLine
                    where purchLine.PurchId == purchId
                    && purchLine.LineNumber == lineNumber;

                    if (purchLine)
                    {
                            purchLine.PurchPrice = purchPrice;
                            purchLine.LineAmount = purchLine.PurchPrice * purchLine.QtyOrdered;
                            purchLine.update();
                            doneCntr++;
                        info(strFmt("Line Number %1 updated",lineNumber));
                    }

                }
                while (type != COMVariantType::VT_EMPTY);
            
            application.quit();
         }

    info(strFmt("%1 item(s) out of %2 updated",doneCntr,row-1));
}

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

The best advice I can give you is do not use Excel. Save it as CSV, and read in the values in a batch job executing server-side with System.IO.StreamReader .Net class.

Also you could try to do bulk updates for example with Unit of Work and see if that gives better performance or not compared to the regular updates: blogs.msdn.microsoft.com/.../ax-2012-unitofwork-performance-series-part-5

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you for your reply. and Thanks for great post about UnitOfWork.

would you please help me about the first paragraph of your answer?

"read in the values in a batch job executing server-side with System.IO.StreamReader .Net class." I didn't understand this part!

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

If you write import in an X++ job, that executes client-side, and since you are doing database operations, it is slower. What you want is make a RunBaseBatch or SysOperation framework class, and execute your import logic server-side.

For file operations to read content I like to use System.IO.StreamReader class, use google to find examples with AX.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hello, Vilmos. Thanks for your help. I tried everything to figure out why it is so slow when updating a PurchLine Price.

Finally, I ran Trace Parser and find the following.

I have no Idea why these codes take so long to execute and also don't know whether I can fix them or even bypass them. I just want to update the PurchLine Price.

P.S. There is no customization in these classes.

Was this reply helpful? Yes No