Page 1 of 1

Queries - When is More More, and When is Less More?

Posted: Fri Oct 29, 2010 12:51 pm
by Jonah Bron
Hello, world!

It this thread:
viewtopic.php?f=1&t=121692

Josh, McInfo, and DigitalMind stated that breaking joins into multiple queries was better, after I asserted the opposite. Their explanation satisfied me. But, I have encountered several places in POEAA (Fowler) that appear to contradict this. Examples are on 266 (paragraph 1), 256 (paragraphs 2 & 3), and 243 (paragraph 3). There are probably other examples that I've forgotten about, and more that I haven't read yet :roll:

I'm sure that both parties know what they are talking about, and there's some subtle difference/performance improvement I haven't caught. Perhaps someone could clarify for me?

Re: Queries - When is More More, and When is Less More?

Posted: Fri Oct 29, 2010 1:14 pm
by Eran
There's no truism regarding queries - each have to be looked at in context. As long as performance is good, it's preferable to fetch all relevant information in one query, as concurrency is always an issue and it usually simplifies application logic. Sometimes, one of the JOINs in a multiple table join really brings down query performance OR makes some aggregate information impossible to calculate (such as SUM(), AVG() etc). In that case it's better to split that troublesome join out, even if it increases application logic complexity somewhat.

Re: Queries - When is More More, and When is Less More?

Posted: Sat Oct 30, 2010 9:27 pm
by Jonah Bron
I see, so basically it varies from case to case.

Re: Queries - When is More More, and When is Less More?

Posted: Sat Oct 30, 2010 9:58 pm
by califdon
Disclaimer: many others here are far better qualified than I am to make judgments on database engine internals and optimization strategies -- but that never prevented me from speaking out before, so I'll add my 2 cents worth (and that may be over pricing it, considering inflation).

I agree with Eran, based mostly on what I've heard others say whom I have reason to believe truly do understand this complex subject, such as an acquaintance who spent 5 years writing the internal code for MySQL at MySQL AB.

Re: Queries - When is More More, and When is Less More?

Posted: Sat Oct 30, 2010 10:30 pm
by Weirdan
Basically there's only one true method for performance optimization:

Code: Select all

do {
  profile();
  makeYourChanges();
  $results = profile();
} while (!$results->satisfiable());

Re: Queries - When is More More, and When is Less More?

Posted: Mon Nov 01, 2010 11:06 am
by Jonah Bron
Heh, that's a pretty good representation. Thanks for the help.