What is the fastest way of pulling data from a foreign database

Question

I need to pull data from a single table stored in a MSSQL database. The data then gets stored in a staging table in AX, from where it is processed according to the business logic.

Performance is a key factor in this project.

Now I was doing some research of what are the possibilities to retrieve data from an MSSQL database with X++ code and found following blog:

Connecting to Databases through X++

Basically there are different ways which can be used for this endeavour:

• ODBC - Open Data Base Connectivity
• OLEDB - Object Linking and Embedding
• ADO - ActiveX Data Objects
• Connection Class

Now I was hoping that someone could give input of which one is preferrable and why (especially in regards to performance).

Any input is appreciated.

Answer 1

The connection class isn't a valid option because it connects to the same database AX is connected to, so your choices boil down to ODBC, OLEDB and the ADO/.NET way.

Personally I would go for the .NET integrated way (called OleDB in your link) but would use System.Data.SqlClient instead of System.Data.OleDb because it's natively written in .NET. If your code is compiled to CIL that should give you best results.

No matter what option for the retrieval you pick, you should really be building a recordinsertlist and perform a set based insert instead to optimize insert performance.