Hello guys,
I am also facing the similar problem. Does community users have any
solution for this?
This has become blocking issue for me. Can someone please help?
Thanks,
Akash
On Mon, Sep 10, 2018 at 8:33 AM Prasad Bhalerao <
prasadbhalerao1...@gmail.com> wrote:
> Guys, is there any solution for this?
> Can someone please respond?
>
> Thanks,
> Prasad
>
>
> ---------- Forwarded message ---------
> From: Prasad Bhalerao <prasadbhalerao1...@gmail.com>
> Date: Fri, Sep 7, 2018, 8:04 AM
> Subject: Fwd: Query execution too long even after providing index
> To: <user@ignite.apache.org>
>
>
> 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
>>>
>>
