循环存储过程或客户端代码

From an efficiency and best practices point of view, I appreciate everyones thoughts.

从效率和最佳实践的角度来看,我欣赏每个人的想法。

I have a stored procedure that makes (n) copies of a row and inserts the row into the necessary table.

我有一个存储过程,它生成(n)行的副本并将行插入必要的表。

For example. Make a copy of an inventory item and add the copies to inventory.

例如。制作库存物料的副本并将副本添加到库存。

I see two options for this type of stored procedure.

我看到这种类型的存储过程有两个选项。

Option 1:

CREATE PROCEDURE CopyInventory
@InventoryID int
AS

BEGIN

INSERT INTO Inventory (fieldOne, fieldTwo, FieldThree)
(SELECT FieldOne, FieldTwo, FieldThree FROM Inventory WHERE InventoryID = @InventoryID)

END

Using option one I would call the stored procedure multiple times from within a while loop in the client application code.

使用选项一,我将在客户端应用程序代码中的while循环内多次调用存储过程。

Option 2:

    CREATE PROCEDURE CopyInventory
    @InventoryID int,
    @CopyCount int
    AS

    BEGIN

    DECLARE @counter int

    SET @counter = 0

    WHILE @counter < @CopyCount

    INSERT INTO Inventory (fieldOne, fieldTwo, FieldThree)
    (SELECT FieldOne, FieldTwo, FieldThree FROM Inventory WHERE InventoryID = @InventoryID)

    END
END

Using option two I would call the stored procedure once from the client application code and pass in a copy count as a parameter.

使用选项2,我将从客户端应用程序代码调用存储过程一次,并将副本计数作为参数传递。

Option two seems to be the best option to me since it results in only one database call.

选项二似乎是我的最佳选择,因为它只导致一个数据库调用。

I appreciate your thoughts on why you would prefer one over another or neither along with any best practice recommendations.

我很感激你对为什么你喜欢一个而不是另一个或者没有任何最佳实践建议的想法。

4 个解决方案

#1

Of course do it on the server side.

当然可以在服务器端进行。

This will save you lots of round-trips between client and server.

这将节省您在客户端和服务器之间的大量往返。

Will be even better if you make your loop set-based:

如果你基于循环设置会更好:

WITH hier(cnt) AS
        (
        SELECT  1 AS value
        UNION ALL
        SELECT  cnt + 1
        FROM    hier
        WHERE   cnt < @count
        )
INSERT
INTO    Inventory (fieldOne, fieldTwo, FieldThree)
SELECT  FieldOne, FieldTwo, FieldThree
FROM    hier, Inventory
WHERE   InventoryID = @InventoryID

#2

In this situation, I would say do it on the server side.

在这种情况下,我会说在服务器端这样做。

Normally though, I would say that set based operations should be done server side - as that's what SQL Server is especially good at, and iterative operations should be done client side, as that's what compiled languages are good at.

通常情况下,我会说基于集合的操作应该在服务器端完成 - 因为这是SQL Server特别擅长的,并且迭代操作应该在客户端完成,因为这是编译语言所擅长的。

#3

If it's just performance you are going for, then option 2 is the way to go.

如果它只是你想要的表现,那么选项2就是你要走的路。

If you can get away with keeping the Connection on the client open, then there are a couple of reasons for taking option 1.

如果你可以让客户端上的Connection保持打开状态,那么选择1有几个原因。

You can easily put a progress bar in the application

您可以轻松地在应用程序中放置进度条

You can easily abort the process in the middle of the loop...like if the user clicks cancel.

您可以轻松地在循环中间中止该过程...就像用户单击取消一样。

#4

It really needs profiling, but my first thought is that server-side would be best. The key would be whether the overhead of the DB engine running the loop is greater than the overhead of repeated client-server calls to issue the command repeatedly, and this will vary depending on DB (one thought I have is that it may be possible for the engine to provide better concurrency for the stored procedure if it is made with multiple calls rather than a single stored procedure... purely an implementation detail).

它真的需要分析,但我首先想到的是服务器端是最好的。关键是运行循环的数据库引擎的开销是否大于重复发出命令的重复客户端 - 服务器调用的开销,这将取决于数据库(一个人认为我有可能是如果使用多个调用而不是单个存储过程来为存储过程提供更好的并发性的引擎...纯粹是一个实现细节。

As a suggestion, why not create both? Implement the count version in terms of the single version, and then you can more easily profile both options side by side and see which provides better performance for your situation.

作为一个建议,为什么不创造两者?根据单个版本实施计数版本,然后您可以更轻松地并排分析这两个选项,并查看哪种选项可以为您的情况提供更好的性能。

#1