Is there a more efficient way to update the sort order of an entire table?

Question

I'm using client side drag and drop sort ordering for a table of data. The sort order is persisted in the database as a simple integer value. The schema looks like the following:

Id | Title | SortOrder
1  | A     | 0
2  | B     | 1
3  | C     | 2

Data can just be pulled out with a simple ORDER BY and all is well.

The client side drag and drop I've implemented posts an array of the IDs in the new order, which is then persisted to the database using Dapper as follows (removing service layers for brevity):

[HttpPost]
public ActionResult UpdateSort(int[] ids)
{
    if (ids != null && ids.Length > 0)
    {
        using (var con = Connection.OpenSql())
        {
            for (var i = 0; i < ids.Length; ++i)
                con.Execute("UPDATE Table SET SortOrder = @sort WHERE Id = @id", new { sort = i, id = ids[i] });
        }
    }
    return new HttpStatusCodeResult(200);
}

This works fine, but I wondered if there was a better way to handle the update assuming the client side of things can't change. We have one SQL statement being executed per row at the moment, is there any way to keep it parametrized and batch them? Is it possible to remove the loop entirely?

The number of records being used with this code is small, so it's not a huge problem as it is - the question is really just one of curiosity as I couldn't think of a way to easily order some SQL by an arbitrary array of identity values

I hope this question doesn't break site rules - I did have a check and I think it's ok. I also realize the answer may well just be a 'No', but that being the case I'd still be interested in the confirmation.

You can move logic to procedure but idea will be same - you still need to loop. — Renatas M., Nov 11 '14 at 12:15
The logic is already in the service layer, I just put it in the action here to make the question more concise. — Polynomial, Nov 11 '14 at 12:32
Yes, but also [you can pass DataTable](http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure) (created from array) as parameter in 2008 and newer SQL server. — Renatas M., Nov 11 '14 at 12:46

score 1 · Accepted Answer · answered Nov 11 '14 at 12:53

Assuming the Ids wont change , These are the options that i think you have.

Below is the Summary of changes you will need.

Move the update inside a stored procedure.(SP)
As a parameter to the SP either pass in a XML of id's or pass a table type in. (http://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx).
If you are using an xml to pass in the values, Insert the XML entries into table variable.Keep an identity column here so that this will eventually become the sort order.
Join the table you have with the temporary table with the id.
Write an update statement with the Join which basically updates the SortOrder field with IdentityColumn Data.

eg

UPDATE Tab
SET SortOrder = [Idnetityfield]
FROM Table Tab
INNER JOIN TempTable Temp ON Temp.id=Tab.id

I think this is a way in which you can approach your scenario.

Is there a more efficient way to update the sort order of an entire table?

1 Answers1