ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Prasad Bhalerao <prasadbhalerao1...@gmail.com>
Subject Re: Query execution too long even after providing index
Date Tue, 04 Sep 2018 08:38:16 GMT
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