3 ms·
> (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 lik
by pritambaral 9y ago
> (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.
- Can_Not 9y agoThe 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.
- orf 9y agoUnfortunately 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.
- Can_Not 9y agoI 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.
- pritambaral 9y agoNo 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.
- Can_Not 9y agoIn 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.
- stubish 9y agoPrepared 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.