what is the best practice for pushing enormous data into external SQL database using x++ code

(0) Share

Report

Posted on by Community Member

I have a temp table with 1000+ records and would like to insert into external SQL data base and I designed my logic as below. I am looping the tables and creating insert and hitting SQL database 1000 times.

Please advise best practice?

void pushDataToSql()

{

LoginProperty lloginProperty;

OdbcConnection lOdbcCon;

Statement lstatement;

SqlStatementExecutePermission lSqlPermission;

str lConStr = "";

;

lConStr = this.getConnStr();

if (!lConStr)

{

throw error("Database connection setup not found !!");

}

try

{

lloginProperty = new LoginProperty();

lloginProperty.setOther(lConStr);

lOdbcCon = new ODBCConnection(lloginProperty);

if(lOdbcCon)

{

lOdbcCon.ttsbegin();

while select gTOSqlImportTMPTable

{

lCtrlMgrSQLQry = '';

lCtrlMgrSQLQry += "XXXXXX \n";

lSqlPermission = new SqlStatementExecutePermission(lCtrlMgrSQLQry);

lSqlPermission.assert();

lstatement = lOdbcCon.createStatement();

lstatement.executeUpdate(lCtrlMgrSQLQry);

CodeAccessPermission::revertAssert();

}

while select gBTOrderMasterTMPTable

{

lOrdMasterSQLQry = '';

lOrdMasterSQLQry += "XXXXX \n";

lSqlPermission = new SqlStatementExecutePermission(lOrdMasterSQLQry);

lSqlPermission.assert();

lstatement = lOdbcCon.createStatement();

lstatement.executeUpdate(lOrdMasterSQLQry);

CodeAccessPermission::revertAssert();

}

while select gBTOrderDetailsTMPTable

{

lOrdDetailsSQLQry = '';

lOrdDetailsSQLQry += "XXXXXXXX \n";

lSqlPermission = new SqlStatementExecutePermission(lOrdDetailsSQLQry);

lSqlPermission.assert();

lstatement = lOdbcCon.createStatement();

lstatement.executeUpdate(lOrdDetailsSQLQry);

CodeAccessPermission::revertAssert();

}

lOdbcCon.ttscommit();

lstatement.close();

}

catch

{

if (lSqlPermission)

CodeAccessPermission::revertAssert();

if (lstatement)

lstatement.close();

throw error("Failed to transfer data to SQL server !!");

}

*This post is locked for comments

All responses (4)

Answers (0)

Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

RE: what is the best practice for pushing enormous data into external SQL database using x++ code

It seems you could probably used a Linked server to connect to your remote SQL Server from your local (hosting AX) SQL Server, and then perhaps do an INSERT INTO .. SELECT statement to transfer all of the records in one operation.

Or, you could concatenate 10 or 20 statements together and execute them at one time, i.e. INSERT INTO .. ; INSERT INTO ..; INSERT INTO.., and while that's a little more complex code (and sloppier) it does manage to reduce the round trips to the database in cases where that matters.

I'm rather fond of the Linked server approach. I read and write data to external data sources (SQL Server, MySQL, AS/400, etc.) all the time, and sometimes at a scale of millions of records (and only takes seconds or minutes in the worst case).
Martin Dráb 233,553 Most Valuable Professional on at

Like (0)

Report

RE: what is the best practice for pushing enormous data into external SQL database using x++ code

You can't ignore questions about where the SQL Server runs and whether you can connect into it. For example, if it's in an on-premises system hidden behind a firewall and you can't connect to it directly, you must think about a relay, a connection from the on-premises system to AX or to some middle-tier storage or something...

Actually, my questions are in a parallel thread on the topic (but they're very relevant here as well). When almost the same thing is discussed at two places in exactly the same time, one easily gets confused.
Community Member on at

Like (0)

Report

RE: what is the best practice for pushing enormous data into external SQL database using x++ code

Hi Martin,

Thanks for the response.

I am designing an interface based on scheduled job in ax it has to read the data from a different table and pull them into TEMP and then push into SQL server for an external system to read.

I want to execute 1000 statements at one go. Please do needful
Martin Dráb 233,553 Most Valuable Professional on at

Like (0)

Report

RE: what is the best practice for pushing enormous data into external SQL database using x++ code

1000 isn't any large number in the context of relational databases, but running 1000 statements is a different problem. It's clearly not efficient.

What technique you should use depends on your particular requirements and limitations. For example, you could export data to a file and import it with bcp utility. You could utilize SSIS. You could use SqlBulkCopy via .NET Interop. You could use a cross-database INSERT INTO query. And so on.