应该进行多个insert调用还是传递XML？

Question

我有一个帐户创建过程，基本上当用户注册时，我必须在多个表中输入，即用户，个人资料，地址。用户表中有1个条目，配置文件中有1个条目，地址表中有2-3个条目。因此，最多将有5个条目。我的问题是，我是应该将这个XML传递给我的存储过程并在其中解析它，还是应该在我的C#代码中创建一个transaction对象，保持连接打开并在循环中逐个插入地址？

您如何处理此场景？多次调用会不会在连接打开的情况下降低性能？

Answer 1

如果要在多个表中插入记录，则使用XML参数是一种复杂方法。在sql server中，用.net创建可扩展标记语言，并从可扩展标记语言中提取三个不同表的记录是一件复杂的事情。

在事务中执行查询是一种简单的方法，但是在.net代码和sql server之间切换会降低一些性能。

最好的方法是在table过程中使用表参数。在.net代码中创建三个数据表，并在存储过程中传递它们。

--为每种类型的表创建TargetUDT1、TargetUDT2和TargetUDT3类型，其中包含需要插入的所有字段

CREATE TYPE [TargetUDT1] AS TABLE
             (
             [FirstName] [varchar](100)NOT NULL,
             [LastName] [varchar](100)NOT NULL,
             [Email] [varchar](200) NOT NULL
             )

--现在用下面的方式写下sp。

 CREATE PROCEDURE AddToTarget(
     @TargetUDT1 TargetUDT1 READONLY,
     @TargetUDT2 TargetUDT2 READONLY,
     @TargetUDT3 TargetUDT3 READONLY)
     AS
 BEGIN
       INSERT INTO [Target1]
       SELECT * FROM @TargetUDT1

       INSERT INTO [Target2]
       SELECT * FROM @TargetUDT2

       INSERT INTO [Target3]
       SELECT * FROM @TargetUDT3
 END

在.Net中，创建三个数据表并填入数值，然后正常调用sp。

Answer 2

无意冒犯，但你想太多了。

收集您的信息，当您拥有所有这些信息时，创建一个事务并逐个插入新行。这里不会影响性能，因为事务将是短暂的。

一个问题是，如果您在连接上创建事务，插入用户行，然后等待用户输入更多的配置文件信息，插入该行，然后等待他们添加地址信息，然后插入该，这是一个不必要的长时间运行的事务，并且会产生问题。

然而，您的场景(您拥有所有数据)是正确使用事务，它确保了数据的完整性，不会给数据库带来任何压力，也不会自己造成死锁。

希望这能有所帮助。

另外，xml方法的缺点是增加了复杂性，你的代码需要知道Xml的模式，你的存储过程也需要知道Xml模式。存储过程增加了解析xml，然后插入行的复杂性。对于一个简单的短时间运行的事务，我真的看不到额外复杂性的优势。

Answer 3

例如，假设您的xml如下所示

<StoredProcedure>
<User>
 <UserName></UserName>
</User>
<Profile>
 <FirstName></FirstName>
</Profile>
<Address>
 <Data></Data>
 <Data></Data>
 <Data></Data>
</Address>
</StoredProcedure>

这将是您的存储过程

INSERT INTO Users (UserName) SELECT(UserName) FROM OPENXML(@idoc,'StoredProcedure/User',2)
WITH ( UserName NVARCHAR(256))

其中，这将提供idoc变量值，而@doc是存储过程的输入

DECLARE @idoc INT

--Create an internal representation of the XML document.        
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

使用类似的技术，您将在单个存储过程中运行3次插入。请注意，它是对数据库的一次调用，并且将在对此存储过程的一次调用中插入多个地址元素。

更新

我不想误导您，这里是一个完整的存储过程，因为您确实了解要执行的操作

USE [DBNAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO  
CREATE PROCEDURE [dbo].[procedure_name]
    @doc [ntext]
WITH EXECUTE AS CALLER
AS
DECLARE @idoc INT  
DECLARE @RowCount INT
SET @ErrorProfile = 0

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

BEGIN TRANSACTION

INSERT INTO Users (UserName)
SELECT UserName FROM OPENXML(@idoc,'StoredProcedure/User',2)
WITH ( UserName NVARCHAR(256) )

-- Insert Address

-- Insert Profile


SELECT @ErrorProfile = @@Error                              

IF @ErrorProfile = 0
    BEGIN
            COMMIT TRAN
    END
ELSE
    BEGIN
            ROLLBACK TRAN
    END

EXEC sp_xml_removedocument @idoc