Re-using PreparedStatement when using Datastax Cassandra Driver?

Question

I'm currently using the Datastax Cassandra driver for Cassandra 2 to execute cql3. This works correctly. I started using PreparedStatement's:

Session session = sessionProvider.getSession();
try {
    PreparedStatement ps = session.prepare(cql);
    ResultSet rs = session.execute(ps.bind(objects));
    if (irsr != null) {
       irsr.read(rs);
    }
}

Sometimes I get a warning from the driver in my log:

Re-preparing already prepared query . Please note that preparing the same query more than once is generally an anti-pattern and will likely affect performance. Consider preparing the statement only once.

This warning makes sense, but i'm not sure how i should reuse the PreparedStatement?

Should I just create all my PreparedStatement in a constructor/init method and than simply use them?

But does this go well when multiple threads use the same PreparedStatement at the same time (especially calling PreparedStatement.bind() to bind objects)

score 20 · Accepted Answer · answered Apr 07 '14 at 16:23

You may just initialize the PreparedStatement once and cache it while the app is running. It should be available for use as long as the Cassandra cluster is up.

Using the statement from multiple threads is fine (as long as you don't modify it throught setXXX() methods). When you call bind(), the code underneath only reads the PreparedStatement and then creates a new instance of BoundStatement() which the caller thread is then free to mutate.

Here is the source code, if you're curious (search for bind()).

Thanks for the reply Daniel. The creation and return of a BoundStatement when bind is called explains everything, now i can use preparedstatements the right way without being to worried. — TinusSky, Apr 07 '14 at 16:45

score 9 · Answer 2 · answered Apr 17 '14 at 12:11

We are using cassandra in a webapplication with Spring. In our case we create the PreparedStatements when the bean which encapsulate the operation against on cf (our repository) is instatiated.

Here you have a snippet of the code we are using:

@Repository
public class StatsRepositoryImpl implements StatsRepository {

@SuppressWarnings("unused")
    @PostConstruct
    private void initStatements(){
        if (cassandraSession == null){
            LOG.error("Cassandra 2.0 not available");
        } else {
            GETSTATS_BY_PROJECT = cassandraSession.prepare(SELECTSTATS+" WHERE projectid = ?");
        }

    }       

@Override
    public Stats findByProject(Project project) {
        Stats stats = null;

        BoundStatement boundStatement = new BoundStatement(GETSTATS_BY_PROJECT);

        ResultSet rs = cassandraSession.execute(boundStatement.bind(project.getId()));
        for (Row row : rs){
            stats = mapRowToStats(row);
        }

        return stats;
    }

By this way the prepared statements are reused each time we execute the method findByProject.

Cheers for expanding with a nice spring based example on a best practice using prepared statements. — TinusSky, Apr 22 '14 at 07:50
when do you close Cassandra Session? Do you even close it any where? — nanosoft, May 30 '19 at 10:14

score 0 · Answer 3 · answered Jul 30 '15 at 13:16

The above solution will work in case the key space is fixed. In case of multi-tenant scenario, this solution will not suffice. I simply did in the following way, where keyspace is passed as a argument.

Check for keyspace from the prepared statement, if it is same as passed argument then do not prepare the statement as it is already prepared in this case.

 private BatchStatement eventBatch(List<SomeEvent> events, String keySpace) {

    BatchStatement batch = new BatchStatement();
    String preparedStmtKeySpace = propEventPer == null? "" : propEventPer.getQueryKeyspace();
    if(!keySpace.equals("\"" + preparedStmtKeySpace +  "\"")) {
        eventStmt = cassandraOperations.getSession().prepare(colFamilyInsert(keySpace + "." + "table_name"));

    }
    ....
private RegularStatement colFamilyInsert(String colFamilyName) {
    return insertInto(colFamilyName)
            .value(PERSON_ID, bindMarker())
            .value(DAY, bindMarker());

}

score -2 · Answer 4 · edited Apr 03 '17 at 18:24

03-Apr-2017 10:02:24,120 WARN  [com.datastax.driver.core.Cluster] (cluster1-worker-2851) Re-preparing already prepared query is generally an anti-pattern and will likely affect performance. Consider preparing the statement only once. Query='select * from xxxx where cjpid=? and cjpsnapshotid =?'

Create a pool of PreparedStatement objects, one for each CQL query.

Then, when these queries are being requested by client, fetch the respective cached PS object and supply values by calling bind().

as explained by Daniel, bind() does new BoundStmt(param) which makes this thread safe.

Re-using PreparedStatement when using Datastax Cassandra Driver?

4 Answers4

Linked