Loop in stored procedure or in client code

Question

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.

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.

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.

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.

Answer 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

Answer 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.

Answer 3

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

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

1. You can easily put a progress bar in the application
2. You can easily abort the process in the middle of the loop...like if the user clicks cancel.

Answer 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.