Thread: isSingleXXX in AbstractJdbc1Statement

isSingleXXX in AbstractJdbc1Statement

From
"Chris Smith"
Date:
G'morning (or as appropriate for your time zone),

I'm working on refactoring some code from AbstractJdbc1Statement, and there
are comments and design all over the place indicating or assuming that the
isSingleStatement, isSingleDML, and isSingleSelect methods are terribly
expensive.  As far as I can see, though, they aren't!  isSingleStatement is
implicitly calculated when parsing the query, and the other two involve a
single calls to each of String.trim, String.toLowerCase, and
String.startsWith.  I suppose the String.toLowerCase might be an issue for
absolutely gigantic queries, but it looks like we could deal with that by
simply doing a substring to grab the first seven letters before doing the
compare.

Anyone disagree?

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation


Re: isSingleXXX in AbstractJdbc1Statement

From
Oliver Jowett
Date:
Chris Smith wrote:
> G'morning (or as appropriate for your time zone),
>
> I'm working on refactoring some code from AbstractJdbc1Statement, and there
> are comments and design all over the place indicating or assuming that the
> isSingleStatement, isSingleDML, and isSingleSelect methods are terribly
> expensive.  As far as I can see, though, they aren't!  isSingleStatement is
> implicitly calculated when parsing the query, and the other two involve a
> single calls to each of String.trim, String.toLowerCase, and
> String.startsWith.  I suppose the String.toLowerCase might be an issue for
> absolutely gigantic queries, but it looks like we could deal with that by
> simply doing a substring to grab the first seven letters before doing the
> compare.
>
> Anyone disagree?

When I put that code in I was trying to keep the cost of queries that
didn't need to be transformed (due to non-query reasons e.g. state of
autocommit) to a minimum -- the current approach is certainly cheaper
and probably generates less garbage than the more obvious approach, but
I don't know by how much. At the time it was a fairly big change and I
was trying to preempt objections :)

Also please don't reparse on each execution (which is the other thing
that isSingleSelect and friends try to avoid) .. my use case for this is:

    prepare statement once
    execute statement 100k times with different parameters
    keep statement around for later reuse

or the equivalent via batch updates. This currently chews a *lot* of CPU
on the Java side while executing -- on the order of a 50/50 split
between Java and the backend. I've been trying to reduce the JDBC
overhead via things like isSingleSelect.

However this use case seems to be common elsewhere:

    prepare statement
    execute statement with some parameters
    discard statement

and we need to support both.

-O

Re: isSingleXXX in AbstractJdbc1Statement

From
"Chris Smith"
Date:
Oliver Jowett wrote:
> When I put that code in I was trying to keep the cost of queries that
> didn't need to be transformed (due to non-query reasons e.g. state of
> autocommit) to a minimum -- the current approach is certainly cheaper
> and probably generates less garbage than the more obvious approach,
> but I don't know by how much. At the time it was a fairly big change
> and I was trying to preempt objections :)

Okay.  As far as I can see, the calculation of all of these things requires
the following:

1. Two comparisons per query character.
2. A constant 7 assignments.
3. A constant 28 comparisons.
4. Once extremely short-lived String object, that shares it data via
substring).

Frankly, that should be negligible cost on anything faster than a 6052
processor.

> Also please don't reparse on each execution (which is the other thing
> that isSingleSelect and friends try to avoid)

Definitely.  I don't plan to repeat the calculation; only to avoid deferring
it.  I think the deferred calculations introduce unnecessary work on every
execution, just to avoid about 100 processor cycles or so when first parsing
the query.

> This currently chews a *lot* of
> CPU on the Java side while executing -- on the order of a 50/50 split
> between Java and the backend.

I'm interested in your ideas on why this is the case.  I suspect a lot of it
may have something to do with building the text form of the parameters to
inject them into the query (in which case I'm working on solving exactly that
problem by making these changes).

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation


Re: isSingleXXX in AbstractJdbc1Statement

From
Oliver Jowett
Date:
Chris Smith wrote:

>>Also please don't reparse on each execution (which is the other thing
>>that isSingleSelect and friends try to avoid)
>
>
> Definitely.  I don't plan to repeat the calculation; only to avoid deferring
> it.  I think the deferred calculations introduce unnecessary work on every
> execution, just to avoid about 100 processor cycles or so when first parsing
> the query.

Not having benchmarked either approach I can hardly disagree :) Object
creation is a real bugbear in our app so I tend to reflexively avoid it
where I can, even when the direct CPU benefit is dubious.

>>This currently chews a *lot* of
>>CPU on the Java side while executing -- on the order of a 50/50 split
>>between Java and the backend.
>
>
> I'm interested in your ideas on why this is the case.  I suspect a lot of it
> may have something to do with building the text form of the parameters to
> inject them into the query (in which case I'm working on solving exactly that
> problem by making these changes).

I can only speculate, since the java profiling tools are so bad -- I'm
yet to get a good CPU profile out of this bit of code. It all *seems*
fairly cheap so I can only guess that it's an accumulation of many small
operations along the way. Object creation doesn't seem to be the root of
the problem, as even with heap settings that avoid GCing frequently it's
chewing CPU the whole time, not just during GCs.

I should really do some more profiling of this area, I've just been
avoiding it because it's so painful to do..

-O

Re: isSingleXXX in AbstractJdbc1Statement

From
"Chris Smith"
Date:
Oliver Jowett wrote:
> I can only speculate, since the java profiling tools are so bad -- I'm
> yet to get a good CPU profile out of this bit of code. It all *seems*
> fairly cheap so I can only guess that it's an accumulation of many
> small operations along the way. Object creation doesn't seem to be
> the root of the problem, as even with heap settings that avoid GCing
> frequently it's chewing CPU the whole time, not just during GCs.
>
> I should really do some more profiling of this area, I've just been
> avoiding it because it's so painful to do..

Fair enough.  When I finish this patch, I'll certainly do some comparative
testing and look forward to your own experiences.  Finishing is starting to
look not *so* far away any longer -- I just need to get batch updates and
updatable result sets working, do the prepared statement and cursor stuff for
v3 extended query, and then test and clean up.

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation