StringTemplate3StatementLocator question

[Pawan Singh]

Hi

We are using JDBI 2.72 (I know it is old) - but I have a general question.

What we are noticing is that this class walks through the entire CLASSPATH and loads every JAR in order to search for JDBI templates. We do not even use these. As our CLASSPATH contains hundreds of jars, every SQL execution becomes painfully slow.

Is there a configuration we are missing on where these templates are stored? I do not know why it would try to load jar files to look for JDBI templates.

Please pardon my ignorance as I have just started to delve into this performance issue with an existing code base.

Pawan

[Pawan Singh]

Here is the stack trace showing the problem. ClassPath has many jars and JDBI is trying to find templates on the classpath. As far as I am looking, the code is not using any templates.

jdk.internal.loader.URLClassPath is synchronized. This means that all JDBI calls get single threaded while the code is searching for JDBI string templates on the class path.

        at java.util.zip.ZipFile$Source.hashN(java...@11.0.5/ZipFile.java:1324)
        at java.util.zip.ZipFile$Source.getEntryPos(java...@11.0.5/ZipFile.java:1546)
        at java.util.zip.ZipFile.getEntry(java...@11.0.5/ZipFile.java:349)
        - locked <0x000000058016ff68> (a java.util.jar.JarFile)
        at java.util.zip.ZipFile$1.getEntry(java...@11.0.5/ZipFile.java:1121)
        at java.util.jar.JarFile.getEntry0(java...@11.0.5/JarFile.java:576)
        at java.util.jar.JarFile.getEntry(java...@11.0.5/JarFile.java:506)
        at java.util.jar.JarFile.getJarEntry(java...@11.0.5/JarFile.java:468)
        at jdk.internal.loader.URLClassPath$JarLoader.getResource(java...@11.0.5/URLClassPath.java:929)
        at jdk.internal.loader.URLClassPath$JarLoader.findResource(java...@11.0.5/URLClassPath.java:912)
        at jdk.internal.loader.URLClassPath.findResource(java...@11.0.5/URLClassPath.java:291)
        at jdk.internal.loader.BuiltinClassLoader.findResourceOnClassPath(java...@11.0.5/BuiltinClassLoader.java:478)
        at jdk.internal.loader.BuiltinClassLoader.findResource(java...@11.0.5/BuiltinClassLoader.java:302)
        at java.lang.ClassLoader.getResource(java...@11.0.5/ClassLoader.java:1400)
        at java.lang.ClassLoader.getResourceAsStream(java...@11.0.5/ClassLoader.java:1736)
        at org.antlr.stringtemplate.StringTemplateGroup.loadTemplateFromBeneathRootDirOrCLASSPATH(StringTemplateGroup.java:568)
        at org.antlr.stringtemplate.StringTemplateGroup.lookupTemplate(StringTemplateGroup.java:480)
        - locked <0x00000007dc25ca88> (a org.antlr.stringtemplate.StringTemplateGroup)
        at org.antlr.stringtemplate.StringTemplateGroup.lookupTemplate(StringTemplateGroup.java:520)
        at org.antlr.stringtemplate.StringTemplateGroup.isDefined(StringTemplateGroup.java:781)
        at org.skife.jdbi.v2.sqlobject.stringtemplate.StringTemplate3StatementLocator.locate(StringTemplate3StatementLocator.java:259)
        at org.skife.jdbi.v2.SQLStatement.wrapLookup(SQLStatement.java:1284)
        at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1293)
        at org.skife.jdbi.v2.Query.fold(Query.java:173)
        at org.skife.jdbi.v2.Query.list(Query.java:82)
        at org.skife.jdbi.v2.sqlobject.ResultReturnThing$IterableReturningThing.result(ResultReturnThing.java:253)
        at org.skife.jdbi.v2.sqlobject.ResultReturnThing.map(ResultReturnThing.java:46)
        at org.skife.jdbi.v2.sqlobject.QueryHandler.invoke(QueryHandler.java:43)
        at org.skife.jdbi.v2.sqlobject.SqlObject.invoke(SqlObject.java:212)
        at org.skife.jdbi.v2.sqlobject.SqlObject$3.intercept(SqlObject.java:127)
        at org.skife.jdbi.v2.sqlobject.CloseInternalDoNotUseThisClass$$EnhancerByCGLIB$$f6d0601e.findByNeIdAndBase(<generated>)

[Brian McCallister]

The easiest way to remove this behavior would be to set a StatementLocator on the DBI instance which just returns the passed in string, rather than performing a search. I am kind of surprised we didn’t have include one!

- Brian

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jdbi/0eebe998-1234-483a-94d4-6b17b30595cfn%40googlegroups.com.

[Pawan Singh]

Thanks Brian. I will give that a try.

BTW - is there a documentation on how to use StringTemplate3StatementLocator? I could not find one e.g. how does it work and how to configure templates.

Do pre-existing templates provide some performance benefit e.g. pre-compiled SQL so that SQL does not have to parsed or is that a JDBC and Database server function?

Pawan

[Steven Schlansker]

> On Feb 8, 2022, at 7:44 AM, Pawan Singh <pa...@pinger.org> wrote:
>
> Thanks Brian. I will give that a try.
>
> BTW - is there a documentation on how to use StringTemplate3StatementLocator? I could not find one e.g. how does it work and how to configure templates.

Unfortunately I don't see any. Please feel free to file a documentation request ticket to track this.

>
> Do pre-existing templates provide some performance benefit e.g. pre-compiled SQL so that SQL does not have to parsed or is that a JDBC and Database server function?
>

No - in fact, it's often the opposite. At least with Postgres, executing the same statement (same String value) has some performance benefit. But having statement templating
defeats this since each statement SQL is different, so there is no possibility to cache.

Statement templating is a feature for flexibility, not performance.

> Pawan
>
> On Tuesday, February 8, 2022 at 7:02:33 AM UTC-8 Brian McCallister wrote:
> The easiest way to remove this behavior would be to set a StatementLocator on the DBI instance which just returns the passed in string, rather than performing a search. I am kind of surprised we didn’t have include one!
>
> - Brian
>
> On Mon, Feb 7, 2022 at 5:47 PM Pawan Singh <pa...@pinger.org> wrote:
> Hi
>
> We are using JDBI 2.72 (I know it is old) - but I have a general question.
>
> What we are noticing is that this class walks through the entire CLASSPATH and loads every JAR in order to search for JDBI templates. We do not even use these. As our CLASSPATH contains hundreds of jars, every SQL execution becomes painfully slow.
>
> Is there a configuration we are missing on where these templates are stored? I do not know why it would try to load jar files to look for JDBI templates.
>
> Please pardon my ignorance as I have just started to delve into this performance issue with an existing code base.
>
>
> Pawan
>
> --
> You received this message because you are subscribed to the Google Groups "jDBI" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/jdbi/0eebe998-1234-483a-94d4-6b17b30595cfn%40googlegroups.com.
>
> --
> You received this message because you are subscribed to the Google Groups "jDBI" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/jdbi/a1cc442c-4ba0-4b3c-978a-662a8f4d814en%40googlegroups.com.