4 ms·

> If you use an ORM-like coding style, you get orderly code but performance is terrible because (a) every row is handled in its own transaction and (b) even if

by vosper 9y ago

> If you use an ORM-like coding style, you get orderly code but performance is terrible because (a) every row is handled in its own transaction and (b) even if it wasn’t there are multiple network round trips per row. What ORM are you using? I've only really used SQLAlchemy, and I'm pretty sure it doesn't end up working like that. I've seen the SQL it outputs and it almost always is what I would have written (I haven't done really complicated stuff, though)

oneweekwonder 9y ago
> If you use an ORM-like coding style Not the op but for me the defining word here is "coding style". Would be interesting to see if their is any serious ORM frameworks that recommends ops steps. That kind of steps you normally see in novice code, that does not understand sql or the orm framework. So for a novice a fetchall into memory, loop over each row in memory, and after each modification commit, not a issue, ignorance is bliss. And it will most probably stay a non issue until performance hits are taken, then optimization can take place. This time maybe with a senior that will look at the sql logs and show the junior the error in his ways. At least with the junior code you could move the product forward... but maybe it a case of one step forward, two step back.
kbenson 9y ago
There are ways to mitigate this if your ORM is good. I made the poor decision of moving to a much more powerful but geographically distant DB a while back, and while some operations benefit greatly from the extra hardware and memory, you really learn where your inefficiencies are when there's a 50 millisecond round trip involved and you find you're hitting the DB a few hundred or thousand times for this task. Pre-fetching relations is amazingly useful in that context, as are alternate input methods that allow multiple inserts in a single glue opening (but be prepared to divide.and conquer if one or more items might fail to insert, such as when a unique key has a duplicate). Updates are generally the biggest problem, but if you're lucky you can reduce those by grouping similar updates into single update statements. Many of these have more specific solutions depending on the particular RDBMS you are using, but ORM support for those features is generally poor. If you're lucky your ORM is extensible enough that you can hack some in without too much effort and while being fairly clean.
- AlphaSite 9y ago
  From what I remember batch operations are a part of the JDBC core.
solidsnack9000 9y ago
But I bet you are not doing something like: for item in items { ... item.save() }

by vosper 9y ago

oneweekwonder 9y ago

> If you use an ORM-like coding style Not the op but for me the defining word here is "coding style". Would be interesting to see if their is any serious ORM frameworks that recommends ops steps. That kind of steps you normally see in novice code, that does not understand sql or the orm framework. So for a novice a fetchall into memory, loop over each row in memory, and after each modification commit, not a issue, ignorance is bliss. And it will most probably stay a non issue until performance hits are taken, then optimization can take place. This time maybe with a senior that will look at the sql logs and show the junior the error in his ways. At least with the junior code you could move the product forward... but maybe it a case of one step forward, two step back.

kbenson 9y ago

There are ways to mitigate this if your ORM is good. I made the poor decision of moving to a much more powerful but geographically distant DB a while back, and while some operations benefit greatly from the extra hardware and memory, you really learn where your inefficiencies are when there's a 50 millisecond round trip involved and you find you're hitting the DB a few hundred or thousand times for this task. Pre-fetching relations is amazingly useful in that context, as are alternate input methods that allow multiple inserts in a single glue opening (but be prepared to divide.and conquer if one or more items might fail to insert, such as when a unique key has a duplicate). Updates are generally the biggest problem, but if you're lucky you can reduce those by grouping similar updates into single update statements. Many of these have more specific solutions depending on the particular RDBMS you are using, but ORM support for those features is generally poor. If you're lucky your ORM is extensible enough that you can hack some in without too much effort and while being fairly clean.

AlphaSite 9y ago
From what I remember batch operations are a part of the JDBC core.

solidsnack9000 9y ago

But I bet you are not doing something like: for item in items { ... item.save() }