Troubleshooting

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"/>
    </bean>

    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
    MCI
    ------------------------------



Would you attend a July Meetup?


Announcements

  • Welcome to the new GridGain Forums!

    Hello and welcome to the new GridGain Forums. This is the place to ask questions, get or give advice and connect with your peers. GridGain experts regularly monitor these posts and can also help solve your issues.