Hacker News Reader

Comment by orf | Hacker News Reader

This comment is really interesting to me as I'm working on getting essentially this functionality added to Django[1].

The case statement is slightly different, more like this (usually with a couple of hundred ID's):

    UPDATE TABLE place 
    SET rating = (
       CASE WHEN id = 1 THEN 25
       CASE WHEN id = 2 THEN 94
       CASE WHEN id = 3 THEN 16
    ) 
    WHERE id IN (1, 2, 3)

Members of the Django community, myself included, have seem huge performance benefits using this technique. I had an update loop go from taking 90 seconds to 3.

As a seasoned DBA you see any huge downsides with this technique? I agree that if you're updating the whole table using a CASE statement is not a great idea but if you want to update 1000 rows with differing values and you have to issue 1000 individual queries then this can take some time (including whatever overhead Django has).

1. https://github.com/django/django/pull/9606

View on HN

I'd probably use a temp table for that, rather than a case.

  begin;
  select id, new_rating into temp_ratings values (1,25),(2,94),(3,16);
  update table place set rating=new_rating
    from temp_ratings
    where place.id=temp_ratings.id;
  drop table temp_ratings;
  commit;

Scales well, and almost all db engines know how to do that join. You can index the id, or use a temp table, or whatever depending on the shape and quantity of your data.

(edit -- actually finished the post)

This is really bad advice:

  1) This is not really a temporary table in the traditional
     sense; you're creating a real table and then dropping it.
     This has problems with concurrency (you can't have two
     "real" tables with the same name), among other issues.
  2) Temporary tables in Postgres suck because they bloat
     the catalogs.  Creating temporary tables regularly is
     better avoided.
  3) There's no real need to create a table of any kind.  A
     regular VALUES would do.  (Though you might have to encode
     some type information into the query.)

> (usually with a couple of hundred ID's)

> I had an update loop go from taking 90 seconds to 3.

How is the performance with prepared statements? It seems like repeatedly executing the same statement with different values is what prepared statements are made for.

The ORM likely does use prepared statements, it's that most ORMs by default makes 100 round-trip IO operations (one for each update). The code would need to be rewritten without the ORM to send all the updates as one database call. The query isn't expensive to the database, but to the application itself just the IO of the database call adds up quickly.

Unfortunately Django does not use prepared statements. There are some big challenges to work around before support can be added. In any case I'd be very, very surprised if a significant portion of the 87 seconds using a single statement saved was saved also saved by using a prepared statement.

I don't think using a prepared statement 100 times will have anywhere close to the same performance improvement as sending one concatenated bulk update or one update set case.

No reason why prepared statements and single-call batched-statements can't both be used together. Of course, if one method (batching statements) works well enough, there's no need for another (prepared statements) too.

In every ORM I've ever used, everything is a prepared statement by default, so I've only been comparing 100 separate database call updates vs 100 updates concatenated as a single database call and 100 updates as a single update case when, where all three were already implied prepared statements.

Prepared statements will still be noticeably slower in most deployments. Almost all drivers are synchronous, and each individual query is a round trip to the database. This tends to dwarf parse and planning times unless you have a sub-millisecond ping time to the database.

It can be a lot faster, but it can also be slower. With databases, the answer for almost everything is "it depends." Here, it really depends on how big the table is, how many rows you're updating, what the indexes are, how much RAM is available, etc.

My point is that, in a vacuum, you can't really say anything about what the performance difference will be. Yes, you can certainly find instances where the performance is better, but it may not be much.

My point is that the complexity of writing the statement is non-trivial, and unless you have a good reason to you shouldn't use CASE. It shouldn't be your first choice, is all I'm saying. For example, the example you provided here is not even valid SQL. It should be:

    UPDATE TABLE place 
    SET rating = (
        CASE WHEN id = 1 THEN 25
            WHEN id = 2 THEN 94
            WHEN id = 3 THEN 16
        END
    ) 
    WHERE id IN (1, 2, 3)

Keep in mind, I also said: "If performance becomes a problem on this query, you can fix it later when you can focus on just that one issue and understand the specific problem much better."

There are lots of reasons writing a query one way might be faster than another. Running in parallel might be one reason. If you had say 20 separate cases, and you compared that to 20 separate queries one after another, they'd be waiting to run till the previous one ran, and so there's no overlap when run separately. I bet 20 parallel separate queries would have been much faster. Depending on your index, how much of the whole table did the query scan to create the update set - if it had to scan most of it for each query, that could explain why a single query was better.

I agree with others that usually it's best to write queries that are simple to understand and let the optimizer pick a good query. Under higher load and with higher data, the indexes matter more of course (and amount of ram).

I can't say the above case is more likely to be better or not, it depends on the database schema.

what's the diff when running 100 updates in a single transaction? that's typically where I see a lot of benefits with multiple updates.

So I was actually wondering if I was correct after seeing some comments here. This is what my quick benchmarking says:

I made a postgres table with two columns, id and num. I filled it with 1000000 rows in sequential order (just a generate_series()), so the first row was (1,1), the second was (2,2) etc.

I ran two queries to update the first 1000 'num' columns with random values. The first was as described above:

    UPDATE test SET num = (CASE WHEN id = 1 THEN 123 CASE WHEN id = 2 THEN 356 ...) WHERE id IN ...

This took about 1.5 seconds (on my completely un-optimized postgres instance)

The second uses 1,000 individual queries in a transaction (BEGIN, COMMIT):

    BEGIN
    UPDATE test set num = 123 WHERE id = 1
    ....
    COMMIT

This is still running, so it's taken over 3 minutes so far.

I'm guessing it's because even though the statements are in a single transaction the database still issues 1,000 individual table scans for a single record, and does 1,000 individual updates. Doing 1,000 updates using a single CASE statement seems to take the same time as updating a single one with the traditional way.

Edit: Query returned successfully in 21 min 39 secs.

Did you have an index on the table? If you didn't, you had to scan the whole table on each "separate query", whereas in the single query you scanned it once.

But like someone said above, it depends on the best way, it will be different. Also when dividing into 1000 separate queries, they have to wait for the previous one to run, and then they'll need to be optimized. A more interesting comparison might be (1) create index on the id column, (2) run the 1000 updates in parallel, maybe 30 at a time?

There was an index on the PK column and the value column as well.

Edit: Oh no, there was not. My bad :/ I added an index and the query time dropped to 5 seconds or so. A lot, lot faster, but 4x as slow as the CASE version.

Parallel queries would probably perform better, but I'm looking at a more general case. Django supports a lot of backends and not all of them support parallel queries, and indeed there is no way to make parallel queries like this in Django.

If we are talking about Postgres specifics, there is some better syntax for this that lets you join on a list of Values you can construct, which I intend to add support for later.

But if using 1 case statement is generally faster than issuing N individual synchronous queries then it's a big win for applications that have to do this kind of thing:

    for instance in queryset.all():
      instance.field = some_function()
      instance.save()

While this isn't a super common pattern it does crop up from time to time and it's annoying to work around (if you even can).

thanks for the details.

4-5 seconds sort of makes sense, depending on hardware. 3 minutes made no sense.

I think he was saying that concatenating 100 updates into one database API call is faster and/or better than using a case statement like that.

Both methods will be faster than sending 100 separate update. postgre API calls.

how about:

  UPDATE place
  SET rating = new.rating
  from (values (1,25),(2,94),(3,16)) new(id,rating)
  where place.id = new.id;

Reply on news.ycombinator.com