MS使用存储过程插入高性能数据

Question

我正在寻找一个非常高性能的可能性，将数据插入到MS数据库中。数据是具有关系的对象的(相对较大的)构造。出于安全考虑，我希望使用存储过程而不是直接的表访问。

假设我有这样一个结构：

• Device

• Document
  • MetaData
    • 用户

​

- Content  
    - ContentItem[0]  
        - SubItem[0]
        - SubItem[1]
        - SubItem[2]

​

    - ContentItem[1]  
        - ...

​

    - ContentItem[2]  
        - ...

​

​

​

现在，我想创建一个大型查询，执行如下操作(只是伪代码)：

EXEC @DeviceID = CreateDevice ...;
EXEC @UserID = CreateUser ...;
EXEC @DocID = CreateDocument @DeviceID, @UserID, ...;

EXEC @ItemID = CreateItem @DocID, ...
EXEC CreateSubItem @ItemID, ...
EXEC CreateSubItem @ItemID, ...
EXEC CreateSubItem @ItemID, ...
...

但是，这是最佳的性能解决方案吗？如果没有，还有什么更好呢？把它分成更多的查询？将所有数据交给一个大的存储过程以减少查询的大小？还有其他表演线索吗？

我还考虑给一个存储过程多个项目，但我认为不可能给一个存储过程一个非静态的项目数量。因为'INSERT INSERT A值(B，C)，(C，D)，(E，F)比3个单个插入的性能更好--我想我可以在这里获得一些性能。

谢谢你的暗示，马克

Answer 1

尽可能使用一个存储过程：

INSERT INTO MyTable(field1,field2)
SELECT "firstValue", "secondValue"
UNION ALL
SELECT "anotherFirstValue", "anotherSecondValue"
UNION ALL

如果您不确定插入了多少项，就可以构造SQL查询，然后在sproc上使用它执行它。下面是我编写的用于获取组的CSV列表并将它们的关系添加到用户实体的过程：

ALTER PROCEDURE [dbo].[UpdateUserADGroups]
@username varchar(100),
@groups varchar(5000)
AS
BEGIN
DECLARE @pos int,
@previous_pos int,
@value varchar(50),
@sql varchar(8000)

SET @pos = 1
SET @previous_pos = 0
SET @sql = 'INSERT INTO UserADGroups(UserID, RoleName)'

DECLARE @userID int
SET @userID = (SELECT TOP 1 UserID FROM Users WHERE Username = @username) 

WHILE @pos > 0
BEGIN
 SET @pos = CHARINDEX(',',@groups,@previous_pos+1)
 IF @pos > 0
 BEGIN
 SET @value = SUBSTRING(@groups,@previous_pos+1,@pos-@previous_pos-1)
 SET @sql = @sql + 'SELECT ' + cast(@userID as char(5)) + ',''' + @value + ''' UNION ALL '
SET @previous_pos = @pos
END
END

IF @previous_pos < LEN(@groups)
BEGIN
  SET @value = SUBSTRING(@groups,@previous_pos+1,LEN(@groups))
  SET @sql = @sql + 'SELECT ' + cast(@userID as char(5)) + ',''' + @value + ''''
END
print @sql
exec (@sql)

END

这比单独的插入要快得多。

此外，确保您只是在主键上的单个聚集索引，更多的索引将减缓插入速度，因为它们需要更新。

然而，您的数据集越复杂，您就越不可能完成上述操作，因此您只需做出逻辑上的妥协。实际上，我最后打电话给上面的例程大约8000次。