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.mashen...@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 <
> prasadbhalerao1...@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
>
