Deutsch

Hintergrund

Lösungsansatz

English

Background

Solution

Deutsch

Hintergrund

Sehr häufig muss man in der Praxis möglichst schnell sehr viele Datensätze aktualisieren. Idealerweise liegen die zu aktualisierenden Datensatz-Schlüssel in einer Art vor , die ein schnelles Join beim Update oder Ähnliches erlaubt.

Nicht immer ist dies möglich.

Häufig hat man in der Praxis imperativ sehr viele Datensätze angefasst , z.B. in einer Iteration Tausende von Datensätzen gezielt durchlaufen , weil gewisse Anforderungen es verlangen.

Man hat dann eine Sammlung von Datensatzschlüsseln und möchte alle Datensätze , die als Primärschlüssel diese ID haben , möglichst performant mit einem Status-Update versehen.

Sollte man dabei auf einfache native SQL-Datentypen beschränkt sein und keine komplexeren benutzerdefinierten Datentypen verwenden können , wird es komplexer. Beispielsweise ist es beim Umgang mit den SQL-Daten im ERP-System "Sage 100" von Sage nicht möglich , eigene benutzerdefinierte Typen wie Tabellentypen , Auflistungen oder dergleichen zu übergeben.

In solchen Fällen empfiehlt sich folgender Lösungsansatz , der auch z.B. in älteren Microsoft SQL-Server-Versionen wie dem SQL-Server 2014 problemlos funktioniert.

⚠️Sehen Sie davon ab , Abertausende von Schlüsseln mit "OR" oder einem "IN"-Operator in SQL zu setzen! Der SQL-Optimierer von Microsoft stößt dabei sehr schnell an Limits! Was viele auch nicht wissen: Selbst für SQL-Parameter-Übergaben gibt es Limits. Bei den meisten SQL-Servern wird es ab 2000+ Parametern kritisch!

Tipp: Siehe auch https://www.officium-inservio.com/ms-net-1/sqlmassmergeimport für performanten BULK-MERGE von Datensätzen.

Lösungsansatz

Man schreibt die vorliegenden Schlüssel in eine Dummy-XML. Der MS-SQL-Server hat einen sehr effizienten XML-Parser , den man sich bei Update-Anweisungen zunutze machen kann.

var xmlTextHelper = new System.Text.StringBuilder();

foreach ( var recordId in exportedOpenInvoiceRecordIds )
{
  xmlTextHelper.Append( "<x>" );
  xmlTextHelper.Append( recordId.ToStringEnhanced() );
  xmlTextHelper.Append( "</x>" );
} 

// The trick is to pass the dummy XML string as parameter

const string sqlTextForUpdate = "DECLARE @xmlData AS XML = CAST( @sXmlIds AS XML ); UPDATE O SET [USER_SAGReWeIntExported] = -1 FROM [dbo].[KHKOpNebensatz] AS O WITH(ROWLOCK) INNER JOIN @xmlData.nodes('/x') AS T(c) ON O.[Mandant] = @nMandatorId AND O.[ID] = T.c.value('.', 'INT'); ";

// ... Usually , you have some sort of parameter sets available to pass to the SQL execution (keep everything SQL-injection safe!) - we have our own library for this

dataAccessor.AddSqlParameter( parametersForSql , "nMandatorId" , System.Data.SqlDbType.SmallInt , false , "Mandant" , mandator.Id );

// Pass the actual data keys as a huge XML string
dataAccessor.AddSqlParameter( parametersForSql , "sXmlIds" , System.Data.SqlDbType.NVarChar , false , "XmlIds" , xmlTextHelper.ToString() );

// Now , we only need to execute the SQL
Exception errorState;

int affectedRows;

dataAccessor.ExecuteSql( sqlTextForUpdate , parametersForSql , null , null , out affectedRows , out errorState );

if ( affectedRows != exportedOpenInvoiceRecordIds.Count )
{
  // Something is fishy!
}

English

Background

Very often in practice , one needs to update a large number of records using a key as quickly as possible. Ideally , the record keys to be updated are available in a format that allows for a quick join during the update or something similar. This is not always possible.

Frequently in practice , one has handled a large number of records imperatively , e.g. specifically iterating through thousands of records , because certain requirements demand it.

One then has a collection of record keys and wishes to apply a status update as performantly as possible to all records that have this ID as their primary key. If one is limited to simple native SQL data types and cannot use more complex user-defined data types , it becomes more complicated.

For example, when dealing with the SQL data in the ERP system "Sage 100" by Sage , it is not possible to pass custom user-defined types such as table types, collections , or the like.

In such cases, the following approach is recommended, which also works flawlessly in older Microsoft SQL Server versions such as SQL Server 2014.

⚠️Refrain from setting thousands upon thousands of keys with "OR" or an "IN" operator in SQL! The Microsoft SQL optimiser reaches its limits very quickly when doing this! What many are also unaware of: there are limits even for passing SQL parameters. With most SQL servers, it becomes critical from 2000+ parameters onwards!

Tip: See also https://www.officium-inservio.com/ms-net-1/sqlmassmergeimport for a BULK-MERGE of records in a most performant way.

Solution

One writes the available keys into a dummy XML. MS SQL Server has a very efficient XML parser, which one can utilise during update statements.

var xmlTextHelper = new System.Text.StringBuilder();

foreach ( var recordId in exportedOpenInvoiceRecordIds )
{
  xmlTextHelper.Append( "<x>" );
  xmlTextHelper.Append( recordId.ToStringEnhanced() );
  xmlTextHelper.Append( "</x>" );
} 

// The trick is to pass the dummy XML string as parameter

const string sqlTextForUpdate = "DECLARE @xmlData AS XML = CAST( @sXmlIds AS XML ); UPDATE O SET [USER_SAGReWeIntExported] = -1 FROM [dbo].[KHKOpNebensatz] AS O WITH(ROWLOCK) INNER JOIN @xmlData.nodes('/x') AS T(c) ON O.[Mandant] = @nMandatorId AND O.[ID] = T.c.value('.', 'INT'); ";

// ... Usually , you have some sort of parameter sets available to pass to the SQL execution (keep everything SQL-injection safe!) - we have our own library for this

dataAccessor.AddSqlParameter( parametersForSql , "nMandatorId" , System.Data.SqlDbType.SmallInt , false , "Mandant" , mandator.Id );

// Pass the actual data keys as a huge XML string
dataAccessor.AddSqlParameter( parametersForSql , "sXmlIds" , System.Data.SqlDbType.NVarChar , false , "XmlIds" , xmlTextHelper.ToString() );

// Now , we only need to execute the SQL
Exception errorState;

int affectedRows;

dataAccessor.ExecuteSql( sqlTextForUpdate , parametersForSql , null , null , out affectedRows , out errorState );

if ( affectedRows != exportedOpenInvoiceRecordIds.Count )
{
  // Something is fishy!
}