3 ms·
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. B
by Latteland 9y ago
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?
- orf 9y agoThere 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).
- mgkimsal 9y agothanks for the details. 4-5 seconds sort of makes sense, depending on hardware. 3 minutes made no sense.