Updating many (like 1000) rows MSSQL vs MySQL (ASP NET MVC 5 vs Django 1.6)

Question

I have need to update large amount of database rows at once. I have "order" column that is going to be updated after user changes it by drag and drop. User probably won't have ability to update "order" of 1000 ojects at once, but lets assume he/she will.

I have tried:

iterating over each object calling object.save() in each iteration (1000 calls one after another of course)
making one big query like "UPDATE name SET order = CASE (...) END WHERE id IN (...)"

I have tried this in Django + Django REST Framework and ASP NET MVC 5 C#. When using first method Django is much faster than ASP NET. Using second method Django is blazing fast (20-30 ms), ASP NET needs for it 400-600ms...

It looks strange as in any other case, like generating view, or returning raw JSON data on api call, updating single or few rows ASP NET is always faster.

My Django code:

def post(self, request):
    from django.db import connection
    photos_order = ""
    ids = ""
    for photo in request.DATA:
        photos_order += " WHEN id = %d THEN %d" % (photo["id"], photo["order"])
        ids += "%s, " % (photo["id"])
    ids = ids[:-2]
    ids = "(" + ids + ")"
    cursor = connection.cursor()
    query = "UPDATE mydb.photos_photo SET `order` = CASE %s END WHERE id IN %s" % (photos_order, ids)
    cursor.execute(query)
    row = cursor.fetchone()

    return Response(json.dumps({"message": _("ok")}), status=status.HTTP_200_OK)

My ASP NET MVC 5 code:

[HttpPost]
[Authorize]
public HttpResponseMessage UpdatePhotosSorting(IEnumerable<Photo> photos)
{
    List<string> photosOrder = new List<string>();
    string photosOrderStrings = "";

    foreach (Photo photo in photos)
    {
        photosOrderStrings += String.Format("WHEN PhotoId = {0} THEN {1} ", photo.PhotoId, photo.Order);
    }

    q = String.Format("UPDATE dbo.Photo SET \"Order\" = CASE {0} ELSE \"Order\" END", photosOrderStrings);
    _db.Database.ExecuteSqlCommand(q);

    return Request.CreateResponse(HttpStatusCode.OK, "ok");
}

Am I doing something wrong or this is just how it works? Is MSSQL Express really that slower than MySQL?

A have to add that Django works under Apache + WSGI + MySQL (but also development runserver does mentioned job in ~35-40ms), and ASP NET was tested on IIS Express 8 + MSSQL Express.

This doesn't answer the question but your code is vulnerable to SQL injection. — Scott Woodall
– Scott Woodall, Commented Feb 28, 2014 at 1:14
Looks like it is. :) Will have to check if I get integers in those request fields. — 6d6268513b
– 6d6268513b, Commented Feb 28, 2014 at 1:20
That won't protect you either. Instead you should bind your variables instead of substituting. Check these docs for information on how to do this safely in Django if not using the ORM. — Scott Woodall
– Scott Woodall, Commented Feb 28, 2014 at 1:23
Ok, I will look into it for sure. Thanks for notifying problem, I would probably forget in this appetite for performance. — 6d6268513b
– 6d6268513b, Commented Feb 28, 2014 at 1:29

Community · Accepted Answer · 2017-05-23 12:10:56Z

Calling save 1000 times is likely a bad idea in general, the underlying framework can "otpimize away" this potential performance issue in some cases though.

TSQL performance when using code like

select/update/etc from table where id in (1,2,3,4,5,6...,1000) is also bad from a performance viewpoint. You ideally replace this where the rows are selected., etc. in a functional manner.

A common high performnce update strategy is to upload all of the ID's to be updated in a XML, blob, text field, etc. load the id's into a temp table (or temp variable), usually in a stored proc

Then do an "update from" or "MERGE" based on this table. You also need to avoid a loop in your tsql code to populate the temp table.

Given your problem definition, I would probably just build an xmlstring in the client with all of the ids and "desired order" and pass this to the stored proc. For a simple update like this, performance should be quite good (assuming you have the appropriate index on ID)

ADDED

Just in case you've not used XML this way before, shredding xml is a nice example of doing just this. This is often known as shredding XML instead of parsing XML. In the general case, using XML in TSQL is a useful member of your toolbox.

Collectives™ on Stack Overflow

Updating many (like 1000) rows MSSQL vs MySQL (ASP NET MVC 5 vs Django 1.6)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related