Hi Zaid,
It looks like you start query from client node, while cache data resides on
server node.
So, there is an overhead on data transfer from server node to client.
I've simplified your test, but query return no records for me.
On Fri, Mar 17, 2017 at 10:19 PM, Denis Magda <[email protected]> wrote:
> Thanks, Zaid,
>
> One of Ignite SQL gurus will take a look at provided example and share
> suggestions.
>
> —
> Denis
>
> > On Mar 17, 2017, at 2:42 AM, zaid <[email protected]> wrote:
> >
> > Hi,
> > Please let me know if I need to provide anything else to reproduce the
> > issue.
> > Regards,
> > Zaid
> >
> >
> >
> > --
> > View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/Very-slow-cache-query-compared-to-H2-
> tp11013p11268.html
> > Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
>
--
Best regards,
Andrey V. Mashenkov
package poc.sqlgrid;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.IgniteDataStreamer;
import org.apache.ignite.Ignition;
import org.apache.ignite.cache.CacheMode;
import org.apache.ignite.cache.query.QueryCursor;
import org.apache.ignite.cache.query.SqlFieldsQuery;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.configuration.IgniteConfiguration;
import org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi;
import org.apache.ignite.spi.discovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder;
import poc.sqlgrid.vos.CacheVO1;
import poc.sqlgrid.vos.CacheVO2;
import poc.sqlgrid.vos.CacheVO3;
import poc.sqlgrid.vos.CacheVO4;
import poc.sqlgrid.vos.CacheVO5;
import poc.sqlgrid.vos.CacheVO6;
import poc.sqlgrid.vos.CacheVO7;
/** */
public class SlowQuery {
/** */
public static final TcpDiscoveryVmIpFinder FINDER = new TcpDiscoveryVmIpFinder(true);
// Query in readable form is given below.
private static final String QUERY = "SELECT c.field1,c.field2,cb.field1,cbi.field2,NULL AS BCD,cb.field5,cb.field6"
+ ",cbrr.field2,cb.field9,COALESCE(add1.field5, add2.field5) add_field5,COALESCE(add1.field6, add2.field6) add_field6"
+ ",COALESCE(add1.field7, add2.field7) add_field7,COALESCE(add1.field2, add2.field2) add_field2"
+ ",COALESCE(add1.field8, add2.field8) add_field8,cb.field57,cb.field32,cb.field23,1 AS ABC"
+ " FROM CacheVO1 AS c JOIN \"cache3\".CacheVO3 AS cb ON (c.field1 = cb.field1OfCacheVO1)"
+ " LEFT OUTER JOIN \"cache6\".CacheVO6 AS cbi ON (cb.field1 = cbi.field1OfCacheVO3)"
+ " LEFT OUTER JOIN \"cache7\".CacheVO7 AS cbrr ON (cbrr.field1OfCacheVO3 = cb.field1 AND cbrr.field7 = 'SomeCodeValue'"
+ " AND ( NOW() BETWEEN cbrr.field4 AND COALESCE(cbrr.field5, NOW()))) "
+ " LEFT OUTER JOIN \"cache4\".CacheVO4 AS add1 ON (cb.field1 = add1.field1OfCacheVO3 AND add1.field4 = 'SomeCodeValue1')"
+ " LEFT OUTER JOIN \"cache4\".CacheVO4 AS add2 ON (cb.field1 = add2.field1OfCacheVO3 AND add2.field4 = 'SomeCodeValue2')"
+ " WHERE cb.field23 = true AND (c.field2 LIKE '%99%' OR cb.field5 LIKE '%99%' ) AND (cb.field9 IN ('SomeCodeValue1'"
+ ",'SomeCodeValue2')) ORDER BY c.field2 ,cb.field5 ASC limit 20,10";
/** */
private static IgniteConfiguration getCfg(String name) {
return new IgniteConfiguration()
.setGridName(name)
.setDiscoverySpi(new TcpDiscoverySpi().setIpFinder(FINDER))
.setPublicThreadPoolSize(4)
.setSystemThreadPoolSize(2)
.setCacheConfiguration(cacheCfgs());
}
private static CacheConfiguration[] cacheCfgs() {
return new CacheConfiguration[] {
new CacheConfiguration<>("cache1").setCacheMode(CacheMode.REPLICATED).setIndexedTypes(Long.class, CacheVO1.class),
new CacheConfiguration<>("cache2").setCacheMode(CacheMode.REPLICATED).setIndexedTypes(Long.class, CacheVO2.class),
new CacheConfiguration<>("cache3").setCacheMode(CacheMode.REPLICATED).setIndexedTypes(Long.class, CacheVO3.class),
new CacheConfiguration<>("cache4").setCacheMode(CacheMode.REPLICATED).setIndexedTypes(Long.class, CacheVO4.class),
new CacheConfiguration<>("cache5").setCacheMode(CacheMode.REPLICATED).setIndexedTypes(Long.class, CacheVO5.class),
new CacheConfiguration<>("cache6").setCacheMode(CacheMode.REPLICATED).setIndexedTypes(Long.class, CacheVO6.class),
new CacheConfiguration<>("cache7").setCacheMode(CacheMode.REPLICATED).setIndexedTypes(Long.class, CacheVO7.class)
};
}
/** */
public static void main(String[] args) {
System.out.println("Start grid.\n");
Ignite server = Ignition.getOrStart(getCfg("server"));
Ignite client = Ignition.getOrStart(getCfg("client").setClientMode(true));
fillCache(server);
System.out.println("Warm query cache on server.");
runQuery(server);
System.out.println("Run query on client.");
runQuery(client);
System.out.println("Run query on server.");
runQuery(server);
Ignition.stopAll(true);
}
/** */
private static void runQuery(Ignite client) {
final IgniteCache<Object, Object> cache = client.cache("cache1");
long start = System.currentTimeMillis();
int total = 0;
try (QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery(QUERY))) {
for (List<?> row : cursor) {
total++;
}
}
System.out.println(String.format("Finished in: %d\nRecords found: %d\n",
System.currentTimeMillis() - start,
total));
}
/** */
private static void fillCache(Ignite server) {
try (IgniteDataStreamer<Object, Object> streamer = server.dataStreamer("cache1")) {
for (long l = 1; l <= 1000; l++)
streamer.addData(l, CacheVO1.createNewInstance(l, l + "", l + "", "SomeCodeValue", true, false));
}
assert server.cache("cache1").size() == 1000;
try (IgniteDataStreamer<Object, Object> streamer = server.dataStreamer("cache2")) {
for (long l = 1; l <= 1000; l++) {
if (l % 2 == 0)
streamer.addData(l, CacheVO2.createNewInstance(l, l, l + "", l + "", l + "", l + "", l + "", "SomeCodeValue1"));
else
streamer.addData(l, CacheVO2.createNewInstance(l, l, l + "", l + "", l + "", l + "", l + "", "SomeCodeValu2"));
}
}
try (IgniteDataStreamer<Object, Object> streamer = server.dataStreamer("cache3")) {
for (long l = 1; l <= 1000; l++) {
if (l % 2 == 0)
streamer.addData(l, CacheVO3.createNewInstance(l, l, l + "", l + "", "SomeCodeValue1", true, true, true));
else
streamer.addData(l, CacheVO3.createNewInstance(l, l, l + "", l + "", "SomeCodeValue2", true, true, true));
}
}
try (IgniteDataStreamer<Object, Object> streamer = server.dataStreamer("cache4")) {
for (long l = 1; l <= 1000; l++) {
if (l % 2 == 0)
streamer.addData(l, CacheVO4.createNewInstance(l, l + "", l, "SomeCodeValue1", l + "", l + "", l + "", l + ""));
else
streamer.addData(l, CacheVO4.createNewInstance(l, l + "", l, "SomeCodeValue2", l + "", l + "", l + "", l + ""));
}
}
try (IgniteDataStreamer<Object, Object> streamer = server.dataStreamer("cache1")) {
for (long l = 1; l <= 1000; l++)
streamer.addData(l, CacheVO5.createNewInstance(l, "SomeCodeValue"));
}
try (IgniteDataStreamer<Object, Object> streamer = server.dataStreamer("cache1")) {
for (long l = 1; l <= 1000; l++)
streamer.addData(l, CacheVO6.createNewInstance(l, "SomeCodeValue"));
}
try (IgniteDataStreamer<Object, Object> streamer = server.dataStreamer("cache1")) {
String curDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
for (long l = 1; l <= 1000; l++)
streamer.addData(l, CacheVO7.createNewInstance(l, l, curDate, null, "SomeCodeValue"));
}
}
}
