If your only tool is an X

to breck.carter@gmail.com
subject Client-side looping from your blog

The problem: Need to find all the records where KEY is between 61000 and 63000. There probably won't be many of these, but we need to find them if they are there.

The solution (more or less):

for (int i = 79000; i <= 81000; ++i) {
    String sql = "select ... from ... where KEY = " + i;
    ResultSet rs = conn.executeQuery(sql);
    if (rs.next()) {
        // save the results from this record
    }
}

Not even the decency to use a PreparedStatement.

Unsurprisingly, this is a fair bit slower than just doing

select ... from ... where KEY between 79000 and 81000

and looping through the results.

[In the programmer's defense, the actual situation was a bit more complex than as seen here, but the fundamental issues boiled down to the above]

Net result of fixing this is that one of our major screens went from 3+ seconds to < 500 ms.

---

from breck.carter@gmail.com
subject Re: Client-side looping from your blog

Ack! ...that's amazing!

I absolutely positively know how "the actual situation was a bit more complex" in the real world, however. Developers often have other things on their mind, rather than performance... and in 80% (90%? 99%?) of the cases their code performs adequately regardless of the technique used. It's the other 1% that allows folks like me (and you) to be heroes when we fix it :)

But a client-side WHERE clause? I've written about it (point 22 in How To Make SQL Anywhere Slow) but it's not something you see very often. Point 22 came from Glenn Paulley, not me.

I guess it's another case of "If your only tool is an X, everything looks like a Y" where (X ,Y) = (Java compiler, Java method) instead of (database server, SQL script) like in Everything Looks Like a Database.

---

to breck.carter@gmail.com
subject Re: Client-side looping from your blog

Technically speaking, I wouldn't call this a client-side where clause. To me, a client-side where clause would be fetching a bunch of stuff you don't need, and then weeding through the results on the client side. In this example, you know what you need up front, and you insist on getting it through 2,000 individual queries, which is WAAAAY slower than one big query.

Your How to Make SA Slow is awesome. I hadn't seen that before. I've seen (and probably done myself) #2 (all our DBs are 32K), #3, #4 (accidentally), #13, #18 (almost all of our keys!), #23, #26, #33, #34, and probably others. Ouch.

BTW, the most awesome (in a good way) X/Y example I've seen in a long time is Eric Farrar's glider gun.

---