That time a senior dev showed me my loops were killing the database

I was sitting in a code review at a startup in Portland and a guy 20 years older than me pointed out I was making 400 queries per page load instead of 4, so does anyone have a good rule of thumb for when to switch from loops to bulk queries?

2 comments

2 Comments

allen.cole1mo ago

300 queries is nothing compared to what a properly indexed database can handle, honestly. I've seen people brag about cutting down queries only to end up with massive join monsters that lock tables for seconds at a time. The real win is keeping your queries simple and fast, not just few. Sometimes those loops are the practical choice when you're dealing with complex business logic that doesn't map cleanly to a single SQL statement. You trade query count for code that doesn't require a senior dev with 30 years of Oracle experience to touch. Bulk queries are great until you hit edge cases where one batch fails and you're left wondering which 50 of 5000 records actually updated. So have you actually benchmarked the difference in real load or are you just going by the "ew too many queries" gut feeling?

stellawood1mo ago

You hit on something with the "complex business logic that doesn't map cleanly" part. I've been burned by that exact thing where I tried to force everything into one big query and ended up with a 200 line SQL monster that nobody could debug. The transaction isolation stuff gets ugly fast too. So for those edge cases where batch updates fail, do you just manually audit every time or have you built some kind of retry logic with partial rollbacks?