The biggest database performance problem that I have seen time and time again over the 20+ years I've been using sql databases now is caused by people not understanding that databases are
relational (ie designed to perform efficiently over tables, not individual rows) and so running code that essentially causes hundreds/thousands of individual queries where they could just do one big query. Any time you are doing a select query in a loop you should sit down and take a long hard look at your code.
Almost always
Do big expensive slow query to get everything I need in one hit
for each row of result
do something with it
is going to be much faster than
for each thing in some collection in my program
get the database row that corresponds to just that one thing
do something with it
Round trip io, query parse time, cache effects etc are just going to totally destroy the performance of the second one, and optimising the db or programming language perf is going to do almost nothing to improve things if you're doing it this way. Lots of ORM type abstraction layers make it hard sometimes to see that's what you're doing, so people blame db performance but they're actually doing an insanely inefficient algorithm.
My biggest win ever from this was taking an 8 hour operation down to 15s just by moving some sql out of a loop as per the above.
Almost always
is going to be much faster than Round trip io, query parse time, cache effects etc are just going to totally destroy the performance of the second one, and optimising the db or programming language perf is going to do almost nothing to improve things if you're doing it this way. Lots of ORM type abstraction layers make it hard sometimes to see that's what you're doing, so people blame db performance but they're actually doing an insanely inefficient algorithm.My biggest win ever from this was taking an 8 hour operation down to 15s just by moving some sql out of a loop as per the above.