GridGain Community Edition

Expand all | Collapse all

How to improve query performance?

Jump to Best Answer
  • 1.  How to improve query performance?

    Posted 07-02-2019 08:27 AM
    Hi, I'm running RC3

    I have the following cache config.... <bean class="org.apache.ignite.marshaller.optimized.OptimizedMarshaller"> <property name="requireSerializable" value="false"/> </bean> ... <bean parent="cache-template"> <property name="name" value="myCache"/> <property name="cacheMode" value="PARTITIONED"/> <property name="atomicityMode" value="ATOMIC"/> <property name="distributionMode" value="PARTITIONED_ONLY"/> <property name="backups" value="0"/> </bean> ... <bean id="cache-template" abstract="true" class="org.apache.ignite.configuration.CacheConfiguration">

        <property name="startSize" value="1000000"/>
        <property name="queryIndexEnabled" value="true"/>

    My model looks like so...

    public class MyModel { @QuerySqlField(index = true) Long id; @QuerySqlField(index = true) Integer someField1; @QuerySqlField(index = true) Integer someField2; @QuerySqlField(index = true) String someField3; @QuerySqlField(index = true) String someField14; @QuerySqlField(index = true) String someField5; @QuerySqlField(index = true) String someField6; @QuerySqlField(index = true) String someField7; @QuerySqlField(index = true) String someField8; @QuerySqlField(index = true) Long someField9; @QuerySqlField(index = true) String someField10; @QuerySqlField(index = true) String someField11;

    .. Setters and Getters here... }

    And in my application I run...

    // Do this once at startup... Ignite ignite = Ignition.start("cache.xml"); myCache= ignite.jcache("risk");

    // Per http POST request in my servlet myCache.put(request.getId(), request);

    String sqlStr = "someField1 = ? OR "
    + "someField2 = ? OR "
    + "someField3 = ? OR "
    + "someField4 = ? OR "
    + "someField5= ? OR "
    + "someField6 = ? OR "
    + "someField7 = ? OR "
    + "someField8 = ? OR "
    + "someField9 = ?";

    SqlQuery sql = new SqlQuery(MyModel.class, sqlStr);

    QueryCursor<Entry<Long, Transaction>> cursor = myCache.query(sql.setArgs(...));

    for (Entry<Long, Transaction> t : cursor) size++;

    Even on a single node with verry few entries a couple thousand I am only able to execute about 200 queries per second at average 400ms. Is there anything I can do to improve the performance?

    The query at most will only ever return 1-2 records even if the data grid could reach millions of records.

  • 2.  RE: How to improve query performance?
    Best Answer

    Posted 07-03-2019 07:44 AM
    Hi DataBrew,
    "OR" queries are very slow with cache.query(). It has to do with how H2 and indexes work. If you use UNION rather then OR, queries with cache.query will be super fast!

    GCE and Ignite use H2 as it's indexing mechanism, so using UNION rather than OR is the way to go. 

    Steve Bremmer
    Software engineer