I'm going to go out on a limb and ask if the lack of joins is really
the reason for the performance problems.
No, I am not saying that I think that they made a good design decision. Rather, I am saying that I can think of many additional further mistakes which they could make which could result in the same symptoms, but without which their performance might have been still OK. Here is a sample:
- Is there a good one-to-one mapping between database-backed objects and application level screens? If there is then you have surprisingly little need for joins. You can't always aim for this, of course. But when you get it in a web-based environment, you literally can't make much use of joins.
- Did they have appropriate indexes? Indexes aren't only used for joins. If you ask for all accounts that are represented by representative X, having an index on account_rep_id will be useful whether or not you are joining.
- Did they use placeholders or an equivalent? From your description we can answer that in the negative since the WHERE clause was passed in as a string. I believe this to be a worse decision than the lack of joins. First there are the security reasons stated in Use placeholders. For SECURITY!. But secondly some databases (Oracle would be a good example) go out of their way to try to cache used queries, and contention for that cache will make them fall over fast if you force them to parse everything. (Oracle specifically has a setting that makes it rewrite everything with placeholders. This doesn't solve the security issue, but might help their performance issues.)
- Were they using Enterprise Java beans? Multiple Java experts of my aquaintance have told me that performance with beans ranges from bad to worse. I have heard of plenty of projects with good performance using direct DAO. I've heard of none with beans.
And my point is that just because you know that they did one thing wrong, and had predictable problems, doesn't actually prove that the mistake you see is what actually went wrong.
is exactly right. Updated.
Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
Want more info? How to link or
or How to display code and escape characters
are good places to start.