0

I've come across an efficiency problem for when a stored procedure is called to update multiple records in a single table. The actual problem contains hundreds of parameters, but here is a simplified problem.

CREATE PROCEDURE UpdateData
    @ID1 int, @Value1 int,
    @ID2 int, @Value2 int,
    @ID3 int, @Value3 int
AS
BEGIN
    -- Update record with ID1
    -- Update record with ID2
    -- Update record with ID3
END

I see three methods of doing this:

  1. Create an update query for each ID-Value pair
  2. Create while loop to go through all the inputs and pass them through a single update query
  3. Insert the inputs into a local table and do an update in a single query with use of the table created

I'm unsure at what point creating the table would be more or less efficient than the other options, as I know databases are good at doing things in parallel, but creating a temporary table also uses time.

How can I compare these three methods aside from running and timing them?

Is there a method you recommend?

SamuelSVD
  • 112
  • 2
  • 9
  • You can `UPDATE` via a select and join on some specific identity column, instead of passing in each ID as a parameter, you can use XML or TABLE parameter as your stored procedure parameter. Please see these related posts (https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server), (https://dba.stackexchange.com/questions/89428/how-to-update-a-table-with-a-stored-procedure-with-xml-parameter-in-sql-2008), (https://stackoverflow.com/questions/45670062/insert-and-update-in-sql-using-user-defined-table-type) – Ryan Wilson Jun 21 '19 at 13:49
  • You missed option 4: replace these repeated values with a table-valued parameter or XML/JSON blob. Of course, that requires the ability to modify the client code. – Jeroen Mostert Jun 21 '19 at 13:50
  • Unfortunately I am limited to using parameters as such and can't use a table-valued parameter or XML/JSON blob – SamuelSVD Jun 21 '19 at 13:51
  • @SamuelSVD Can you explain why you can't use table-valued parameter or XML/JSON? – Ryan Wilson Jun 21 '19 at 13:52
  • The current setup does not allow for them. I agree that would be a more efficient method, but we can't change the interface. – SamuelSVD Jun 21 '19 at 13:54
  • 2
    On SQL Server 2016+, even without an interface change you still have the option to use an in-memory table variable, which will cut down on the possible overhead of a temp table. Even if you don't, you'll find a temp table is going to be superior to an explicit loop in almost every case, such that it's not worth special casing for it. About the only special case I'd consider is an update of a single row, since I imagine that might be quite common. If you can quickly test for that, I'd give that its own branch with a single `UPDATE`, and use a table for everything else. – Jeroen Mostert Jun 21 '19 at 13:54
  • 2
    For inserting in the table (variable or otherwise), ideally do not use a loop either -- you can use a `VALUES` clause to pass in up to 1000 rows. An enormous sequence of separate `UPDATE` statements (with a transaction) may or may not be better -- compiling an enormous procedure like that is no fun, but I imagine it would actually run quite well once it's there. You'll have to test. – Jeroen Mostert Jun 21 '19 at 13:56
  • This whole problem seems like poor design in the interface. – Ryan Wilson Jun 21 '19 at 14:04

0 Answers0