Composite Operations vs User-Defined Table Type from BizTalk Server

Posted on by

Normally, when I integrate SQL Server with BizTalk server, I prefer to use stored procedures. This because I believe that a database within an integration scenario should not expose data models but should be a service that exposes methods.

When you need to insert data sets into a SQL Server through a stored procedure, you can use a composite operation or develop a stored procedure that accept a user-defined table type as a parameter.

For more information about the use of a composite operation, refer to the article “https://pellitterisbiztalkblog.wordpress.com/2013/07/01/insert-master-detail-data-structure-on-sql-server-in-a-single-transaction-using-biztalk-server”.

Develop a stored procedure that accepts a user-defined table type as a parameter is very simple. Simply create a user-defined table type as shown below.

CREATE TYPE dbo.Lines AS TABLE
(
    lineId INT NOT NULL,
    lineField1 VARCHAR(10) NULL,
    lineField2 VARCHAR(10) NULL,
    PRIMARY KEY (lineId)
);

Then create a stored procedure that uses the type previously created.

CREATE PROCEDURE dbo.BizTalkSample
    @headerId INT,
    @headerField1 VARCHAR(10),
@headerField2 VARCHAR(10),
@lines Lines READONLY
AS
BEGIN
    INSERT INTO sampleHeader
   (
        headerId,
        headerField1,
        headerField2
   )
   VALUES
   (
       @headerId,
       @headerField1,
       @headerField2
   );
    INSERT INTO sampleLines
   (
       lineId,
       headerId,
       lineField1,
       lineField2
   )
   SELECT
       lineId,
       @headerId,
       lineField1,
       lineField2
   FROM
       @lines;
END

At this point, it is sufficient to generate a schema from a BizTalk project.

There are different contexts in which it is preferable to use a method or the other. The main difference is that a composite operation invokes the stored procedure several times while a stored procedure that accepts a user-defined table type as parameter is called only once.

If the need is to send a dataset to SQL Server, the second method can introduce significant advantages in terms of performance. The BizTalk message is a few percent smaller, but the main point is that the number of communications between BizTalk Server and SQL Server is drastically reduced and SQL Server performs the transaction much faster.

I conducted several tests using a 100MB file which contained about 8 million lines. The improvement was amazing. The time has decreased significantly from about 20 minutes using a composite operation in just over three minutes by running a single stored procedure that accept a user-defined table type as a parameter.

Leave a comment