bulk insert in Mongoose IDO table

Kirill Kuchumov

Hi,

Is there a way to do bulk insert into IDO table?

There is insert example in IDO development guide:

But it is for single record.

If I have 1000 records I need to call it 1000 times.

In C# there is SqlBulkCopy functionality.

Is there something like this in Mongoose IDO?

Regards,

Kirill

Find more posts tagged with

Accepted answers

Legacy Contributor

You can use the C# SqlBulkCopy in an IDO Extension Class:

using (IDisposable txn = TransactionScopeFactory.Create(TransactionScopeOption.Suppress))
{
using (ApplicationDB appDB = IDORuntime.Context.CreateApplicationDB())
{
using (var copy = new SqlBulkCopy((SqlConnection)appDB.Connection, SqlBulkCopyOptions.Default, null))
{
copy.DestinationTableName = [DestinationTableName];

// Add mappings so that the column order doesn't matter
copy.ColumnMappings.Add(nameof([SourceDataTableColumnName]), [DestinationColumnName]);

try
{
copy.WriteToServer([SourceDataTableObject]);
}
catch (Exception ex)
{
Infobar = ex.Message;
}
}
}
}

Source of data has to be a DataTable with column properties set including matching data types to the SQL tables.

You have to provide default values for columns that require a value.

You have to pre-validate the data being inserted - SqlBulkCopy does not trigger IDO validation or table constraints other than primary keys.

SqlBulkCopy will process 50k records in 10 seconds. Going through the IDO layer? That will take you 6 hours, if it doesn't time out, which it will if done through an external application. Default time out period is 10 minutes for IDO operations.

All comments

Legacy Contributor

In the sample code you found, there is a property called ItemNumber. When you do your Request.Items.Add, The properties you send such as CustNum get added to the object which is inserted when your this.Context.Commands.UpdateCollection... is called. In order to insert multiple records in one IDO call, you put your code in a loop, and do your properties.Add like normal for each object you are inserting, but each time you loop, you increment your ItemNumber property, then once everything has been added to your request object, you call your this.Context.Commandes.UpdateCollection call which inserts them all together as one IDO call. To my knowledge, that is the best that we have in the Mongoose side. It will never be as efficient or fast as SQL Bulk Copy, but we use the tools we have. It will be much faster than multiple UpdateCollection calls with ItemNumber being 1 though for sure.

Legacy Contributor

Depending on the size of your data, you can break the number of inserts into blocks of 200 or 500, or if there aren't too many columns you might get away with the full 1000

Legacy Contributor

You can use the C# SqlBulkCopy in an IDO Extension Class:

// Add mappings so that the column order doesn't matter
copy.ColumnMappings.Add(nameof([SourceDataTableColumnName]), [DestinationColumnName]);

try
{
copy.WriteToServer([SourceDataTableObject]);
}
catch (Exception ex)
{
Infobar = ex.Message;
}
}
}
}

Source of data has to be a DataTable with column properties set including matching data types to the SQL tables.

You have to provide default values for columns that require a value.

You have to pre-validate the data being inserted - SqlBulkCopy does not trigger IDO validation or table constraints other than primary keys.

Kirill Kuchumov

Thanks Tim!

Legacy Contributor

No problem, your welcome.

For anyone cares, the reason IDO operations are so slow are:

Because your properly configured SQL database is configured for full transaction logging in order to be able to do proper backups
Going through the IDO layer triggers validation - for which each validation for each column for each row triggers one transaction database request
More columns = more validation = more transactions
20 columns x 20 transactions x 50k rows = 20,000,000 transactions!!!
Your transaction log has to constantly increase its size which triggers an additional performance penalty and makes your hard drives scream out in I/O pain - even SSDs.
If your storage is IP-based, that also saturates your network, which makes your users scream out in pain
Smaller batches do not make a different - you are just spreading out the same load over a longer period of time. Your transaction log will still hate you.

That said, if you are using SqlBulkCopy frequently, you better have good power redundancy - a power blip will leave your database in a dirty state with partially copied data. So, use with caution.

BradleyBrown

Very nice!

Is this valid code in MT cloud though? My understanding was that direct AppDB update of data in MT was a no-no??

Legacy Contributor

Yes. Just did it in coordination with Infor for a recent customer project. AppDB operations are not recommended because they are DBMS specific operations. Not an issue as long as SQL Server is the DBMS system. But it Infor carries through on their threat to switch to Amazon Aurora, this solution will no longer work, and I am not aware of an Amazon Aurora alternative. That said, Infor is reverting some SP to .NET conversions back to SP's next month because of performance issues, so that change is not likely in the near future.

acl

hypothetically...Infor could switch an existing production system's DBMS (for MT Cloud customer)?

Copyright © 2025 Infor. All rights reserved.