Can we have update on this?
---------- Forwarded message ---------
From: Prasad Bhalerao <prasadbhalerao1...@gmail.com>
Date: Wed, Sep 5, 2018, 11:34 AM
Subject: Re: Query execution too long even after providing index
To: <user@ignite.apache.org>
Hi Andrey,
Can you please help me with this? I
Thanks,
Prasad
On Tue, Sep 4, 2018 at 2:08 PM Prasad Bhalerao <prasadbhalerao1...@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.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
>>
>
