ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Prasad Bhalerao <prasadbhalerao1...@gmail.com>
Subject Fwd: Query execution too long even after providing index
Date Fri, 07 Sep 2018 02:34:11 GMT
Can we have update on this?

---------- Forwarded message ---------
From: Prasad Bhalerao <prasadbhalerao1983@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 <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
>>
>

Mime
View raw message