Re: advice on loading and searching large map in memory
Arved Sandstrom wrote:
I agree with you in principle.
I agree with you in practice.
However, if I can write SQL that generates one
SELECT that returns a result set with 1000 rows, as opposed to having, say, a
loop that generates 1000 SELECTs each returning one row, I don't even consider
that a performance optimization, I call that writing good SQL. I'll do it
without thinking twice.
Indeed. Somewhere around chunking IN clauses I believe we cross the line into
excessive effort prior to performance measurement. Similarly, architecting
for multiple app instances in order to hold an entire database in memory just
to avoid an unproven bottleneck without consideration of normal approaches
such as those you suggested bodes poorly for the project.
In practise I've seen N+1 SELECT situations, whether caused by poor SQL and/or
by deliberate loop constructs with fine-grained SQL, cause so many performance
problems that I simply make sure that kind of code doesn't exist, without
waiting for complaints from the field. And besides, this is simple to do. To
put it another way, this is simply making a decent initial implementation
choice as opposed to making a bad one: performance optimization shouldn't be
about replacing bad choices with good ones, but rather about making good ones
even better. IMHO.
You can elevate that from opinion to demonstrable fact.
The problem was that the OP's formulation doesn't fit the "decent initial
implementation choice" description. They should rather follow what you proposed.
Like I said, in principle I agree with you. I wouldn't launch into a
performance optimization that involved serious effort unless it was called for.
As I put forward elsethread, and you endorse here IIUC, up-front good
algorithmic and structural choices aren't so much "optimization" as "good
sense". The dark side is that bad architectural choices, such as abandonment
of modern DMBSes' optimizations at the expense of standard good practices and
algorithms whilst increasing maintenance and deployment effort just to handle
a potentially non-existent problem without actual evidence, must be rejected
early on.
--
Lew
Honi soit qui mal y pense.