3 Replies Latest reply on May 10, 2019 10:37 AM by Paul Ferraro

    Prepared statement caching in WildFly

    valsaraj viswanathan Master

      Which settings need to be set to enabled prepared statement caching in WildFly? What is the impact of following settings in this context?

      <xa-datasource-property name="preparedStatementCacheQueries">0</xa-datasource-property>

      <prepared-statement-cache-size>0</prepared-statement-cache-size>

      <share-prepared-statements>false</share-prepared-statements>

       

       

        • 1. Re: Prepared statement caching in WildFly
          Paul Ferraro Master

          I saw you asked about prepared statement caching in a previous post in the context of entity/query caching.  I wanted to make sure that you realize that prepared statement caching has nothing to do with caching query results, but rather caching the parsed parameterized query within the driver (and potentially, the query plan itself, if supported by your database) itself.

          Generally, if you are caching the results of queries, then caching prepared statements becomes less important as consequence of the mere fact that these prepared statements will be used less frequently.  In the end, it all depends on your use case, how much heap you have, and how you want to prioritize your data caching.

           

          To enable prepared statement caching, just set the cache size:

          WildFly 16.0 Model Reference

          • 2. Re: Prepared statement caching in WildFly
            valsaraj viswanathan Master

            Hi pferraro

            We have not enabled query cache for all queries. So the others will benefit with prepared_statement_cache right?

             

            preparedStatementCacheQueries settings for driver does the same as prepared-statement-cache-size settings in WildFly datasource?

             

            Thanks!

            • 3. Re: Prepared statement caching in WildFly
              Paul Ferraro Master

              Yes - though the cost of preparing a statement is not terribly significant compared to the cost of performing the query itself - so don't expect too much.

               

              The prepared-statement-cache-size attribute is used to perform prepared statement caching within IronJacamar's generic DataSource implementation.

               

              If your vendor's XADataSource implementation already implements prepared statement caching logic, then I would suggest using that instead, as some databases will use this configuration to additionally cache query plans within the database itself.