fast data import from Excel

(0) Share

Report

Posted on by Community Member

Hello,

I have developed a class which basically initializes a excel file that contains thousands of rows of data for items in AX, user uploads the same data into AX to generate a report that contains the uploaded information.

Class works fine and uploads the data, the problem is the speed. I am using 2 temporary tables (one regular type temp table and the other one is memory temp table). In memory table all the data imported from excel is populated, then copied into regular temp table using insert_recordset. I had to go with this approach to save AOS trip to sql server. I do not see any speed difference. Can anyone has any better approach to increase the data import speed?

Thanks in advance.

Sid

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (0)

Suggested answer

Community Member on at

Like (0)

Report

Consider utilizing DMF (Data Import Export Framework)

This framework is said to have performance efficient as it utilises SSIS packages for bulk data import.

DMF supports Batch processing and multi task processing of data

Please verify and let me know if you more details about DMF

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ty Nitesh,

I am new to DMF so you are saying I can use DMF to insert records in a temp table from excel data sheet if that is faster sure I can. How can I import via DMF through my X++ code. Meaning when a user is interested to upload latest excel sheet data, he or she clicks on a menu item to upload or import data.

Can you elaborate more please also I am new to jobs. I have developed a class which perfectly does its job but it takes long time.

Waiting for your reply.

Regards,

sid

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

To utilize DMF, you need to create entity for the target table in Dynamics AX

once you create Entity using Custom Entity Wizard, it will create staging table and class and target query

in DMF staging table is something like temporary table

as per Data processing in DMF you need to move the data to staging table

you will have option to validate the data in staging table

and then move the data to target tables

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Please follow following link for more details about DMF

technet.microsoft.com/.../jj225591.aspx

Please verify and let me know if you need more details

Was this reply helpful? Yes No
Suggested answer

PA-22040759-0 6,194 on at

Like (0)

Report

DIXF / DMF is a good suggestion.

You can also investigate if your import runs from the client or server tier. For best performance you shoulcd have the import to run from the AOS tier, not from the client. Also I don't think your solution with the two temporary tables does anything good for performance. Skip one of these.

Was this reply helpful? Yes No
Saurabh P Singh 890 on at

Like (0)

Report
DIXF is and should be your first choice for this activity. But in case you are not able to implement this, then you can follow some steps that can be helpful in increasing the performance:

Use RecordInsertList when you are importing data to the table, this link will hep you with that http://msdn.microsoft.com/en-us/library/aa879372.aspx.

Run the code at server site, as the number of records are way high for the client to handle.

Minimize the use of while loops (obvious, but very important)

If possible, use CSV instead of excel.

Regards,

Saurabh

Was this reply helpful? Yes No
André Arnaud de Cal... 301,035 Super User 2025 Season 2 on at

Like (0)

Report

Hi Sid,

You got some great answers from Nitesh, Palle and Saurabh. I think DIXF, running on AOS tier and use CSV format will increase the performance.

When you use DIXF in combination with the batch framwork, you can also specify the number of workers to divide the workload on multiple threads.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

DMF is to do migration of lots of data and should be built to handle the import of data efficiently (I repeat 'should' because until now the whole DMF code is not quite optimized for performance 'as is') It is using SQL Server to import the data into staging tables which should be faster than Excel or AIF.

Was this reply helpful? Yes No
KyleLeBarre 747 on at

Like (0)

Report

When you say regular type temp table, do you just mean a regular table? Or a tempDB temp table?

If you're talking tempDB, then I remember there being some wonkiness regarding that. If there was no difference from switching from row by row inserts to an insert_recordset, then the insert_recordset has probably reverted to using row by row operations. I think you need to use the .skipDataMethods(true) as well as some other arbitrary .skip... methods on the tempDB tables for the insert_recordset to actually use the set based operation.

You can tell if it is doing a set based operation or row by row operation by using the tracing cockpit and analyzing the trace file. You should just see one sql update operation on the destination table.

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

If you want exceptional import performance from Excel, use the Excel COM object model only on the client to extract an entire Worksheet object at once as an XML Spreadsheet 2003 object and transfer that XML document to the server for XML parsing using the System.XML or XMLDocument object model. We reliably get 100x performance improvement over Excel using this method.

msdn.microsoft.com/.../ff195193(v=office.15).aspx

msdn.microsoft.com/.../ff837565(v=office.15).aspx

You want the xlRangeValueXMLSpreadsheet value of the xlRangeValueDataType enumeration to produce the XML Spreadsheet 2003 document.

Was this reply helpful? Yes No