Deutsch

Hintergrund

Beispiel SQL-Tabelle als Ausgangssituation

Anlage eines benutzerdefinierten SQL-Tabellen-Typ

Anlage einer SQL-Datenbankprozedur für den MERGE

.net-Implementierung mittels "System.Data.DataTable"

Ausführung der Datenbankprozedur

English

Background

Example SQL table as initial situation

Creation of a user-defined SQL table type

Creation of an SQL stored procedure for the MERGE

.net implementation using "System.Data.DataTable"

Executing the stored procedure from .net

Deutsch

Hintergrund

Man möchte massenhaft Daten möglichst schnell in den Microsoft SQL-Server in eine Tabelle importieren.

Dabei gibt es jedoch die Besonderheit , dass ein MERGE stattfinden muss , d.h. ein simples "berühmtes" BULK-INSERT ist ausgeschlossen.

Es muss für jeden Datensatz effizient geprüft werden , ob dieser schon existiert oder nicht.

Dennoch sollen möglichst alle Daten effizient und schnell von dem lokalen Client per SQL in den SQL-Server "wandern".

Nachfolgender Lösungsansatz ist leicht zu verstehen und dennoch extrem performant.

Beispiel SQL-Tabelle als Ausgangssituation

Nachfolgende Tabelle hat einen massiven zusammengesetzten Schlüssel und soll mit zig Tausenden von Metadaten aus einer lokalen Quelle auf einem Client aktualisiert werden.

Bei bestehenden Datensätzen muss lediglich ein Info-Feld aktualisiert werden (UPDATE)

Bei komplett neuen Datensätzen (die neue Metadaten bedeuten) muss jedoch ein neuer Datensatz angelegt werden (INSERT).

CREATE TABLE [dbo].[SAGPASoftwareProductsMetaDataFields]
(
[IDSoftwareProduct] [binary](100) NOT NULL,
[IDSoftwareProductString]  AS (('{'+CONVERT([nvarchar](100),CONVERT([uniqueidentifier],[IDSoftwareProduct],(0)),(0)))+'}'),
[SoftwareProductContextId] [int] NOT NULL,
[SoftwareProductVersionMajor] [int] NOT NULL,
[SoftwareProductVersionMinor] [int] NOT NULL,
[SoftwareProductVersionRevision] [int] NOT NULL,
[TableName] [nvarchar](128) NOT NULL,
[FieldName] [nvarchar](128) NOT NULL,
[FieldTypeInformation] [nvarchar](255) NULL,
[FieldDetails] [nvarchar](max) NULL,
[FieldDetailsPlainText] [nvarchar](max) NULL,
[FieldDetailsVendor] [nvarchar](max) NULL,
[TableNameInfo] [nvarchar](255) NULL,
[FieldNameInfo] [nvarchar](255) NULL,
[FieldDefaultDataSourceName] [nvarchar](200) NULL,[CreatedOn] [datetime] NULL,
[CreatedBy] [nvarchar](128) NULL,
[UpdatedOn] [datetime] NULL,
[UpdatedBy] [nvarchar](128) NULL,

CONSTRAINT [PK_SAGPAProductMetaDataFields] PRIMARY KEY CLUSTERED ( [IDSoftwareProduct] ASC,
[SoftwareProductContextId] ASC,
[SoftwareProductVersionMajor] ASC,[SoftwareProductVersionMinor] ASC,
[SoftwareProductVersionRevision] ASC,
[TableName] ASC,
[FieldName] ASC ) 
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF ) ON [PRIMARY] ) ON [PRIMARY] 

GO

Anlage eines benutzerdefinierten SQL-Tabellen-Typ

Man legt sich nun einen benutzerdefinierten SQL-Tabellen-Typ an , der genau die relevanten Felder beinhaltet , die bei der Massenaktion behandelt werden sollen.

CREATE TYPE [dbo].[utSAGTbl_MetaDataFields] AS TABLE
(
[IDSoftwareProduct]          BINARY(100)   NOT NULL,  
[SoftwareProductContextId]   INT           NOT NULL, 
[SoftwareProductVersionMajor] INT          NOT NULL, 
[SoftwareProductVersionMinor] INT          NOT NULL,  
[SoftwareProductVersionRevision] INT       NOT NULL, 
[TableName]                  NVARCHAR(128) NOT NULL, 
[FieldName]                  NVARCHAR(128) NOT NULL, 
[FieldTypeInformation]       NVARCHAR(255) NULL,  
[FieldDetails]               NVARCHAR(MAX) NULL,  
[FieldDetailsVendor]         NVARCHAR(MAX) NULL, 
[FieldDefaultDataSourceName] NVARCHAR(MAX) NULL
)

Anlage einer SQL-Datenbankprozedur für den MERGE

Eine simple SQL-Datenbankprozedur macht dann später die eigentliche Massenarbeit auf dem MS-SQL-Server. Dadurch wird maximale Performance möglich , indem der SQL-Befehl "MERGE" benutzt wird.

Dieser erlaubt genau die Unterscheidung zwischen den beiden Fällen , ob ein Datensatz neu ist (INSERT nötig) oder bereits existiert (UPDATE nötig).

Einfacher geht es nicht!

CREATE PROCEDURE [dbo].[spSAGInsertOrUpdatePASoftwareProductsMetaDataFields]
  @tblFields [dbo].[utSAGTbl_MetaDataFields] READONLY
AS
BEGIN

-- Mass import and update of metadata in "SAGPASoftwareProductsMetaDataFields" !

  SET NOCOUNT ON

  MERGE [dbo].[SAGPASoftwareProductsMetaDataFields] AS Target

  USING @tblFields AS Source
  ON Target.[IDSoftwareProduct] = Source.IDSoftwareProduct] AND Target.[SoftwareProductContextId] = Source.[SoftwareProductContextId] AND Target.[SoftwareProductVersionMajor] = Source.[SoftwareProductVersionMajor] AND Target.[SoftwareProductVersionMinor] = Source.[SoftwareProductVersionMinor] AND Target.[TableName]                     = Source.[TableName] AND Target.[FieldName]                     = Source.[FieldName]

  WHEN MATCHED THEN

    UPDATE SET [FieldTypeInformation] = Source.[FieldTypeInformation]

  WHEN NOT MATCHED BY TARGET THEN

    INSERT
    (

    [IDSoftwareProduct], [SoftwareProductContextId], SoftwareProductVersionMajor], [SoftwareProductVersionMinor],    [SoftwareProductVersionRevision], [TableName], [FieldName], [FieldTypeInformation], [FieldDetails],    [FieldDetailsVendor], [FieldDefaultDataSourceName]

    )

    VALUES
    (

    Source.[IDSoftwareProduct], Source.[SoftwareProductContextId], Source.[SoftwareProductVersionMajor], Source.[SoftwareProductVersionMinor], Source.[SoftwareProductVersionRevision], Source.[TableName], Source.[FieldName], Source.[FieldTypeInformation], Source.[FieldDetails], Source.[FieldDetailsVendor], Source.[FieldDefaultDataSourceName]

    );

END

⚠️Auffällig ist dabei , dass eben als Parameter für die Datenbankprozedur der benutzerdefinierte Tabellen-Typ "utSAGTbl_MetaDataFields" erwartet wird und übergeben werden kann.

Dies ist der entscheidende Faktor , da somit mit einem einzigen Aufruf der Prozedur alle x tausend Datensätze übergeben werden können.

.net-Implementierung mittels "System.Data.DataTable"

Die Implementierung auf der .net-Seite ist extrem einfach!

Man sammelt sich alle Daten performant in einer großen Tabelle des Typs "System.Data.DataTable".

Das Praktische: Diese gesamte Tabelle kann man "in einem Rutsch" an die zuvor genannte Datenbankprozedur übergeben!

Im konkreten Anwendungsfall wurden 30.000 Datenfeld-Informationen mit einer simplen Schleife auf dem lokalen PC auf der Basis von bereits extrahierten Text-Metadaten ermittelt und zusammengetragen.

Beispiel für einen Datensatz:

var dt = new System.Data.DataTable();

dt.Columns.Add( "IDSoftwareProduct" , typeof( byte[] ) );

dt.Columns.Add( "SoftwareProductContextId" , typeof( int ) );

dt.Columns.Add( "SoftwareProductVersionMajor" , typeof( int ) );

dt.Columns.Add( "SoftwareProductVersionMinor" , typeof( int ) );

dt.Columns.Add( "SoftwareProductVersionRevision" , typeof( int ) );

dt.Columns.Add( "TableName" , typeof( string ) ); 

dt.Columns.Add( "FieldName" , typeof( string ) );

dt.Columns.Add( "FieldTypeInformation" , typeof( string ) );

dt.Columns.Add( "FieldDetails" , typeof( string ) );

dt.Columns.Add( "FieldDetailsVendor" , typeof( string ) );

dt.Columns.Add( "FieldDefaultDataSourceName" , typeof( string ) );

...

dt.Rows.Add( softwareProductId , softwareProductContextId , targetErpVersionMajor , targetErpVersionMinor , targetErpVersionRevision , tableDef.Name , fieldDef.Name , fieldTypeInfoHelper.ToString() , fieldDef.Description.ToStringEnhanced().Trim() , string.Empty , string.Empty );

...

Ausführung der Datenbankprozedur

Ausführen der Datenbankprozedur aus .net

Es gilt die übliche Vorgehensweise.

Man benötigt eine SQL-Verbindung und ruft dann entsprechend die Datenbankprozedur auf.

Im vorliegenden Fall wurde als Technologie "System.Data.SqlClient" gewählt.

Dabei kommt uns zu Hilfe , dass "System.Data.SqlClient.SqlCommand" es erlaubt , auch eigene benutzerdefinierte Typen zu übergeben.

Einzige Voraussetzung ist , dass man den passenden "SqlDbType" verwendet.

Dies ist "System.Data.SqlDbType.Structured".

Außerdem muss man zwingend den Typen-Namen mitgeben (am besten voll-qualifiziert) , damit der SQL-Server das Ganze auflösen kann.

Das Ergebnis ist simplel und beeindruckend.

Die 30.000 Datensätze wurden innerhalb von Sekunden vollständig vom SQL-Server verarbeitet!

Hätte man sich für eine imperative Iteration entschieden , die jeden Datensatz einzeln vom Client zum Server sendet , wäre die Laufzeit extrem länger.

using ( var cmd = new System.Data.SqlClient.SqlCommand( "spSAGInsertOrUpdatePASoftwareProductsMetaDataFields" , sqlConnection ) )
{

  cmd.CommandType = System.Data.CommandType.StoredProcedure;

  cmd.CommandTimeout = TimeSpan.FromMinutes( 30 ).TotalSeconds.ToInt32();

  var tvpParam = cmd.Parameters.AddWithValue( "@tblFields" , tvpTable );

  tvpParam.SqlDbType = System.Data.SqlDbType.Structured;

  tvpParam.TypeName = "dbo.utSAGTbl_MetaDataFields"; // Important: You must set the type here! 

  cmd.ExecuteNonQuery();
}

English

Background

One wants to import a massive amount of data as quickly as possible into a Microsoft SQL Server table.

However, there is the special requirement that a MERGE must take place, i.e. a simple "famous" BULK INSERT is ruled out.

It must be efficiently checked for each data record whether it already exists or not.

Nevertheless, ideally all data should "migrate" efficiently and quickly from the local client to the SQL Server via SQL.

The following approach is easy to understand and yet extremely performant.

Example SQL table as initial situation

The following table has a massive composite key and is to be updated with tens of thousands of metadata records from a local source on a client.

For existing data records, only an info field needs to be updated (UPDATE).

For completely new data records (which mean new metadata), however, a new data record must be created (INSERT).

CREATE TABLE [dbo].[SAGPASoftwareProductsMetaDataFields]
(
[IDSoftwareProduct] [binary](100) NOT NULL,
[IDSoftwareProductString]  AS (('{'+CONVERT([nvarchar](100),CONVERT([uniqueidentifier],[IDSoftwareProduct],(0)),(0)))+'}'),
[SoftwareProductContextId] [int] NOT NULL,
[SoftwareProductVersionMajor] [int] NOT NULL,
[SoftwareProductVersionMinor] [int] NOT NULL,
[SoftwareProductVersionRevision] [int] NOT NULL,
[TableName] [nvarchar](128) NOT NULL,
[FieldName] [nvarchar](128) NOT NULL,
[FieldTypeInformation] [nvarchar](255) NULL,
[FieldDetails] [nvarchar](max) NULL,
[FieldDetailsPlainText] [nvarchar](max) NULL,
[FieldDetailsVendor] [nvarchar](max) NULL,
[TableNameInfo] [nvarchar](255) NULL,
[FieldNameInfo] [nvarchar](255) NULL,
[FieldDefaultDataSourceName] [nvarchar](200) NULL,[CreatedOn] [datetime] NULL,
[CreatedBy] [nvarchar](128) NULL,
[UpdatedOn] [datetime] NULL,
[UpdatedBy] [nvarchar](128) NULL,

CONSTRAINT [PK_SAGPAProductMetaDataFields] PRIMARY KEY CLUSTERED ( [IDSoftwareProduct] ASC,
[SoftwareProductContextId] ASC,
[SoftwareProductVersionMajor] ASC,[SoftwareProductVersionMinor] ASC,
[SoftwareProductVersionRevision] ASC,
[TableName] ASC,
[FieldName] ASC ) 
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF ) ON [PRIMARY] ) ON [PRIMARY] 

GO

Creation of a user-defined SQL table type

One now creates a user-defined SQL table type, which contains exactly the relevant fields.

CREATE TYPE [dbo].[utSAGTbl_MetaDataFields] AS TABLE
(
[IDSoftwareProduct]          BINARY(100)   NOT NULL,  
[SoftwareProductContextId]   INT           NOT NULL, 
[SoftwareProductVersionMajor] INT          NOT NULL, 
[SoftwareProductVersionMinor] INT          NOT NULL,  
[SoftwareProductVersionRevision] INT       NOT NULL, 
[TableName]                  NVARCHAR(128) NOT NULL, 
[FieldName]                  NVARCHAR(128) NOT NULL, 
[FieldTypeInformation]       NVARCHAR(255) NULL,  
[FieldDetails]               NVARCHAR(MAX) NULL,  
[FieldDetailsVendor]         NVARCHAR(MAX) NULL, 
[FieldDefaultDataSourceName] NVARCHAR(MAX) NULL
)

Creation of an SQL stored procedure for the MERGE

A simple SQL stored procedure does the actual bulk work on the SQL Server.

CREATE PROCEDURE [dbo].[spSAGInsertOrUpdatePASoftwareProductsMetaDataFields]
  @tblFields [dbo].[utSAGTbl_MetaDataFields] READONLY
AS
BEGIN

-- Mass import and update of metadata in "SAGPASoftwareProductsMetaDataFields" !

  SET NOCOUNT ON

  MERGE [dbo].[SAGPASoftwareProductsMetaDataFields] AS Target

  USING @tblFields AS Source
  ON Target.[IDSoftwareProduct] = Source.IDSoftwareProduct] AND Target.[SoftwareProductContextId] = Source.[SoftwareProductContextId] AND Target.[SoftwareProductVersionMajor] = Source.[SoftwareProductVersionMajor] AND Target.[SoftwareProductVersionMinor] = Source.[SoftwareProductVersionMinor] AND Target.[TableName]                     = Source.[TableName] AND Target.[FieldName]                     = Source.[FieldName]

  WHEN MATCHED THEN

    UPDATE SET [FieldTypeInformation] = Source.[FieldTypeInformation]

  WHEN NOT MATCHED BY TARGET THEN

    INSERT
    (

    [IDSoftwareProduct], [SoftwareProductContextId], SoftwareProductVersionMajor], [SoftwareProductVersionMinor],    [SoftwareProductVersionRevision], [TableName], [FieldName], [FieldTypeInformation], [FieldDetails],    [FieldDetailsVendor], [FieldDefaultDataSourceName]

    )

    VALUES
    (

    Source.[IDSoftwareProduct], Source.[SoftwareProductContextId], Source.[SoftwareProductVersionMajor], Source.[SoftwareProductVersionMinor], Source.[SoftwareProductVersionRevision], Source.[TableName], Source.[FieldName], Source.[FieldTypeInformation], Source.[FieldDetails], Source.[FieldDetailsVendor], Source.[FieldDefaultDataSourceName]

    );

END

⚠️What is noticeable here is that the user-defined table type "utSAGTbl_MetaDataFields" is expected exactly as a parameter for the stored procedure.

.net implementation using "System.Data.DataTable"

The implementation on the .net side is extremely simple!

One collects all data performantly in a large table of the type "System.Data.DataTable".

The practical part:

This entire table can be passed "in one go" to the aforementioned stored procedure!

In the specific use case, 30,000 data field records were determined and compiled with a simple loop on the local PC based on previously extracted text metadata.

Example for a data record:

var dt = new System.Data.DataTable();

dt.Columns.Add( "IDSoftwareProduct" , typeof( byte[] ) );

dt.Columns.Add( "SoftwareProductContextId" , typeof( int ) );

dt.Columns.Add( "SoftwareProductVersionMajor" , typeof( int ) );

dt.Columns.Add( "SoftwareProductVersionMinor" , typeof( int ) );

dt.Columns.Add( "SoftwareProductVersionRevision" , typeof( int ) );

dt.Columns.Add( "TableName" , typeof( string ) ); 

dt.Columns.Add( "FieldName" , typeof( string ) );

dt.Columns.Add( "FieldTypeInformation" , typeof( string ) );

dt.Columns.Add( "FieldDetails" , typeof( string ) );

dt.Columns.Add( "FieldDetailsVendor" , typeof( string ) );

dt.Columns.Add( "FieldDefaultDataSourceName" , typeof( string ) );

...

dt.Rows.Add( softwareProductId , softwareProductContextId , targetErpVersionMajor , targetErpVersionMinor , targetErpVersionRevision , tableDef.Name , fieldDef.Name , fieldTypeInfoHelper.ToString() , fieldDef.Description.ToStringEnhanced().Trim() , string.Empty , string.Empty );

...

Executing the stored procedure from .net

The usual procedure applies.

One needs an SQL connection and then calls the stored procedure accordingly.

In the present case, "System.Data.SqlClient" was chosen as the technology.

It comes to our aid here that "System.Data.SqlClient.SqlCommand" allows passing custom user-defined types as well.

The only prerequisite is that one uses the appropriate "SqlDbType".

This is "System.Data.SqlDbType.Structured".

Furthermore, one must strictly provide the type name (ideally fully qualified), so that the SQL Server can resolve the whole thing.

The result is simple and impressive.

The 30,000 data records were completely processed by the SQL Server within seconds!

Had one opted for an imperative iteration, which sends each data record individually from the client to the server, the runtime would have been extremely longer.

using ( var cmd = new System.Data.SqlClient.SqlCommand( "spSAGInsertOrUpdatePASoftwareProductsMetaDataFields" , sqlConnection ) )
{

  cmd.CommandType = System.Data.CommandType.StoredProcedure;

  cmd.CommandTimeout = TimeSpan.FromMinutes( 30 ).TotalSeconds.ToInt32();

  var tvpParam = cmd.Parameters.AddWithValue( "@tblFields" , tvpTable );

  tvpParam.SqlDbType = System.Data.SqlDbType.Structured;

  tvpParam.TypeName = "dbo.utSAGTbl_MetaDataFields"; // Important: You must set the type here! 

  cmd.ExecuteNonQuery();
}