Hi Andrey,

Can you please help me with this? I 

Thanks,
Prasad

On Tue, Sep 4, 2018 at 2:08 PM Prasad Bhalerao <prasadbhalerao1983@gmail.com> wrote:

I tried changing SqlIndexMaxInlineSize to 32 byte and 100 byte using cache config.

ipContainerIpV4CacheCfg.setSqlIndexMaxInlineSize(32/100);

But it did not improve the sql execution time. Sql execution time increases with increase in cache size.

It is a simple range scan query. Which part of the execution process might take time in this case?

Can you please advise?

Thanks,
PRasad

On Mon, Sep 3, 2018 at 8:06 PM Andrey Mashenkov <andrey.mashenkov@gmail.com> wrote:
HI,

Have you tried to increase index inlineSize? It is 10 bytes by default.

Your indices uses simple value types (Java primitives) and all columns can be easily inlined.
It should be enough to increase inlineSize up to 32 bytes (3 longs + 1 int = 3*(8 /*long*/ + 1/*type code*/) + (4/*int*/ + 1/*type code*/)) to inline all columns for the idx1, and up to 27 (3 longs) for idx2.

You can try to benchmark queries with different inline sizes to find optimal ratio between speedup and index size.



On Mon, Sep 3, 2018 at 5:12 PM Prasad Bhalerao <prasadbhalerao1983@gmail.com> wrote:
Hi,
My cache has 1 million rows and the sql is as follows.
This sql is taking around 1.836 seconds to execute and this time increases as I go on adding the data to this cache. Some time it takes more than 4 seconds.

Is there any way to improve the execution time?

SQL:
SELECT id, moduleId,ipEnd, ipStart
FROM IpContainerIpV4Data USE INDEX(ip_container_ipv4_idx1)
WHERE subscriptionId = ?  AND moduleId         = ? AND (ipStart        <= ? AND ipEnd           >= ?)
UNION ALL
SELECT id, moduleId,ipEnd, ipStart
FROM IpContainerIpV4Data USE INDEX(ip_container_ipv4_idx1)
WHERE subscriptionId = ? AND moduleId         = ? AND (ipStart        <= ? AND ipEnd           >= ?)
UNION ALL
SELECT id, moduleId,ipEnd, ipStart
FROM IpContainerIpV4Data USE INDEX(ip_container_ipv4_idx1)
WHERE subscriptionId = ? AND moduleId         = ? AND (ipStart        >= ? AND ipEnd           <= ?)

Indexes are as follows:

public class IpContainerIpV4Data implements Data<DefaultDataAffinityKey>, UpdatableData<DefaultDataAffinityKey> {

@QuerySqlField
private long id;

@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ip_container_ipv4_idx1", order = 1)})
private int moduleId;

@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ip_container_ipv4_idx1", order = 0),
@QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 0)})
private long subscriptionId;

@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ip_container_ipv4_idx1", order = 3, descending = true),
@QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 2, descending = true)})
private long ipEnd;

@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ip_container_ipv4_idx1", order = 2),
@QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 1)})
private long ipStart;
}

Execution Plan:

2018-09-03 19:32:03,098 232176 [pub-#78%springDataNode%] INFO  c.q.a.g.d.IpContainerIpV4DataGridDaoImpl - SELECT
    __Z0.ID AS __C0_0,
    __Z0.MODULEID AS __C0_1,
    __Z0.IPEND AS __C0_2,
    __Z0.IPSTART AS __C0_3
FROM IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z0 USE INDEX (IP_CONTAINER_IPV4_IDX1)
    /* IP_CONTAINER_IPV4_CACHE.IP_CONTAINER_IPV4_IDX1: SUBSCRIPTIONID = ?1
        AND MODULEID = ?2
        AND IPSTART <= ?3
        AND IPEND >= ?4
     */
WHERE ((__Z0.SUBSCRIPTIONID = ?1)
    AND (__Z0.MODULEID = ?2))
    AND ((__Z0.IPSTART <= ?3)
    AND (__Z0.IPEND >= ?4))
2018-09-03 19:32:03,098 232176 [pub-#78%springDataNode%] INFO  c.q.a.g.d.IpContainerIpV4DataGridDaoImpl - SELECT
    __Z1.ID AS __C1_0,
    __Z1.MODULEID AS __C1_1,
    __Z1.IPEND AS __C1_2,
    __Z1.IPSTART AS __C1_3
FROM IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z1 USE INDEX (IP_CONTAINER_IPV4_IDX1)
    /* IP_CONTAINER_IPV4_CACHE.IP_CONTAINER_IPV4_IDX1: SUBSCRIPTIONID = ?5
        AND MODULEID = ?6
        AND IPSTART <= ?7
        AND IPEND >= ?8
     */
WHERE ((__Z1.SUBSCRIPTIONID = ?5)
    AND (__Z1.MODULEID = ?6))
    AND ((__Z1.IPSTART <= ?7)
    AND (__Z1.IPEND >= ?8))
2018-09-03 19:32:03,098 232176 [pub-#78%springDataNode%] INFO  c.q.a.g.d.IpContainerIpV4DataGridDaoImpl - SELECT
    __Z2.ID AS __C2_0,
    __Z2.MODULEID AS __C2_1,
    __Z2.IPEND AS __C2_2,
    __Z2.IPSTART AS __C2_3
FROM IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z2 USE INDEX (IP_CONTAINER_IPV4_IDX1)
    /* IP_CONTAINER_IPV4_CACHE.IP_CONTAINER_IPV4_IDX1: SUBSCRIPTIONID = ?9
        AND MODULEID = ?10
        AND IPSTART >= ?11
        AND IPEND <= ?12
     */
WHERE ((__Z2.SUBSCRIPTIONID = ?9)
    AND (__Z2.MODULEID = ?10))
    AND ((__Z2.IPSTART >= ?11)
    AND (__Z2.IPEND <= ?12))
2018-09-03 19:32:03,098 232176 [pub-#78%springDataNode%] INFO  c.q.a.g.d.IpContainerIpV4DataGridDaoImpl - ((SELECT
    __C0_0 AS ID,
    __C0_1 AS MODULEID,
    __C0_2 AS IPEND,
    __C0_3 AS IPSTART
FROM PUBLIC.__T0
    /* IP_CONTAINER_IPV4_CACHE."merge_scan" */)
UNION ALL
(SELECT
    __C1_0 AS ID,
    __C1_1 AS MODULEID,
    __C1_2 AS IPEND,
    __C1_3 AS IPSTART
FROM PUBLIC.__T1
    /* IP_CONTAINER_IPV4_CACHE."merge_scan" */))
UNION ALL
(SELECT
    __C2_0 AS ID,
    __C2_1 AS MODULEID,
    __C2_2 AS IPEND,
    __C2_3 AS IPSTART
FROM PUBLIC.__T2
    /* IP_CONTAINER_IPV4_CACHE."merge_scan" */)




--
Best regards,
Andrey V. Mashenkov