Re: [GENERAL] Inconsistent performance with LIKE and bind variable onlong-lived connection

On Sat, Jun 10, 2017 at 1:46 AM, Alban Hertroys <haramrae@gmail.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