Re: Inconsistent performance with LIKE and bind variable on long-lived connection

On Sat, Jun 10, 2017 at 1:46 AM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
> I notice that you're declaring your ResultSet variable inside the loop,
which means that you create and destroy it frequently. I've been told that
this is a pattern that the GC has trouble keeping up with (although that
was around the Java 5 era), so you might be seeing the effects of memory
churn in your client instead of in the database.

Sadly, no, it doesn't help. I'd be pretty surprised if that was it, though,
given that it consistently happens with the bind variable and never happens
without; surely the result set's GC behavior would be the same in either
case?

For grins, I tried running with the -verbosegc option to log GC activity:

Two bind variables 48 16 13 8 6 5 6 7 5 115 110 109 132
108 110 113 109 113 108 108
Equality bind variable 5 6 6 5 6 6 6 6 7 6 8 8 5
6 4 5 5 5 5 5
[GC (Allocation Failure) 65536K->2293K(251392K), 0.0035075 secs]
LIKE bind variable 5 5 6 5 6 5 5 6 6 110 107 112 116
118 107 112 115 105 104 104
No bind variables 5 5 4 5 5 4 5 5 4 6 5 6 5
8 4 4 4 4 5 4

So there's only one GC run and it takes about 3 milliseconds. That result
is the same whether the ResultSet is declared inside or outside the loop.

-Steve