Re: Ping Jim Janney - sizing your snippet store
On Fri, 2 Apr 2010, Jim Janney wrote:
So:
Number of entries probably < 1000 (but who knows what people will do?)
Keys are strings, 20 to 30 characters.
Values are strings, maybe 50 to 500 characters.
Frequent reads, occasional writes, very few deletes.
Maybe 5 to 20 users.
Every time someone displays a screen, 20 to 100 reads need to happen
in well under a second. Most of these will be misses.
My gut feeling is that this is not a big ask, and that an RDBMS will be
able to deal with this without any trouble. The first performance-related
thing i'd do (possibly even upfront, before i had hard data that
performance was actually a problem) would be to batch the reads: collect
together all the keys you're going to need, send them over the wire all at
once, and have the far end do a query like:
SELECT key, value
FROM annotation
WHERE key IN (?, ?, ?, ?)
Plugging each key into one of the parameters in the IN-set.
There's a question over how many parameters you put in that IN-set, and
what you do if your number of keys is different. I think i'd construct the
PreparedStatement on the fly with exactly the right number of parameters,
so the question is moot. Since there will only be a small number of
different numbers of parameters (not more than one per form in your app),
the database's PreparedStatement cache should happily hold all of them,
and you won't be incurring a lot of preparation overhead.
As I said in another message, the real problem here is probably network
latency, and switching databases won't help that. I think I'll have to
abandon the brute force approach and try something more clever.
Probably. Robert Klemme's suggestion of building a toy version to get some
data on the performance numbers is an excellent one; in my part of the
world, we call this a 'spike':
http://www.extremeprogramming.org/rules/spike.html
tom
--
It's never too late to change the future.