You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Prasad Bhalerao <pr...@gmail.com> on 2018/09/03 14:12:12 UTC
Query execution too long even after providing index
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" */)
Fwd: Query execution too long even after providing index
Posted by Prasad Bhalerao <pr...@gmail.com>.
Can we have update on this?
---------- Forwarded message ---------
From: Prasad Bhalerao <pr...@gmail.com>
Date: Wed, Sep 5, 2018, 11:34 AM
Subject: Re: Query execution too long even after providing index
To: <us...@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 <pr...@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
>>
>
Re: Query execution too long even after providing index
Posted by Prasad Bhalerao <pr...@gmail.com>.
Hi Andrey,
Can you please help me with this? I
Thanks,
Prasad
On Tue, Sep 4, 2018 at 2:08 PM Prasad Bhalerao <pr...@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
>>
>
Re: Query execution too long even after providing index
Posted by Prasad Bhalerao <pr...@gmail.com>.
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 <an...@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
>
Re: Query execution too long even after providing index
Posted by Andrey Mashenkov <an...@gmail.com>.
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 <pr...@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
Re: Query execution too long even after providing index
Posted by Prasad Bhalerao <pr...@gmail.com>.
Hi, Evgenii,
I tried to execute sql without UNION ALL operator. I mean I just executed
the first part of the sql as shown below and it is taking 700-800 ms to
complete. I have around 3 million records in my cache. and will be having
around 30-40 million records in real scenario.
SELECT id, moduleId,ipEnd, ipStart
FROM IpContainerIpV4Data USE INDEX(ip_container_ipv4_idx1)
WHERE subscriptionId = ? AND moduleId = ? AND (ipStart <= ?
AND ipEnd >= ?)
Does ignite executes 3 sqls independently and then does the union
operation? Is this the reason it is taking time?
Is there any solution to solve this problem? The sql shown below is the
very basic sql which is needed everywhere in my application. Since ignite
does not use index with OR clause I had to rewrite the same sql with UNION
ALL operator.
As mentioned in this email thread, this sql is using indexes. Now the
question is does ignite brings all filtered data in heap space and then
does union all operation?
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 <= ?)
On Thu, Sep 13, 2018 at 9:44 PM ezhuravlev <e....@gmail.com> wrote:
> Hi,
>
> What is the execution time of just a single query without UNION?
>
> Evgenii
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Query execution too long even after providing index
Posted by ezhuravlev <e....@gmail.com>.
Hi,
What is the execution time of just a single query without UNION?
Evgenii
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Query execution too long even after providing index
Posted by Prasad Bhalerao <pr...@gmail.com>.
Hi Evgenii,
Thank you for suggesting the query optimization. It worked perfectly fine.
I unnecessarily complicated the sql.
I really appreciate the efforts you guys are taking to help out the users.
About the test data: Yes in production I will be having more than 100K
records for single subscription and moduleId.
The test data generated has 5 million entries against subscriptionId and
moduleId. This is a worst case scenario but we do have such cases in
production.
Thanks,
Prasad
On Mon, Oct 22, 2018 at 6:22 PM Evgenii Zhuravlev <e....@gmail.com>
wrote:
> Well, looks like you trying to find the segments that intersects defined
> segment, but you're complicating it. You don't need 3 conditions here - it
> will be enough to have only one - ipStart <= MAX and ipEnd >= MIN. I've
> checked it for your case and got absolutely the same results as you have
> with a too complex query.
>
> Aditionally, you have the same subscriptionId AND moduleId in your test
> data, which means that you will have a bad selectivity when you will filter
> by these fields at first. Do you really have such data in your production?
>
> Also, when you will measure something again - do operation a lot of times
> - that how benchmarks work. Ignite initialize internal structures at first
> executions, so, it will not give an idea of overall latency if you will
> measure it just once.
>
> Best Regards,
> Evgenii
>
> пн, 22 окт. 2018 г. в 6:56, Prasad Bhalerao <prasadbhalerao1983@gmail.com
> >:
>
>> Hi Evgenii,
>>
>> Did you get time to check the reproducer?
>>
>> Can you please suggest solution for this?
>>
>>
>> Thanks,
>> Prasad
>>
>>
>> On Fri, Oct 19, 2018, 4:46 PM Prasad Bhalerao <
>> prasadbhalerao1983@gmail.com> wrote:
>>
>>> Hi Evgenii,
>>>
>>> I have created a reproducer and uploaded it to GitHub. I have created 5
>>> cases to test the sql execution time.
>>>
>>> GitHub project: https://github.com/prasadbhalerao1983/IgniteTestPrj.git
>>>
>>> Please run IgniteQueryTester class.
>>>
>>> Thanks,
>>> Prasad
>>>
>>> On Wed, Oct 17, 2018 at 7:46 PM ezhuravlev <e....@gmail.com>
>>> wrote:
>>>
>>>> How much data do you have? What is the amount of heap and offheap
>>>> memory?
>>>> Can you share the reproducer with the community?
>>>>
>>>> Evgenii
>>>>
>>>>
>>>>
>>>> --
>>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>>
>>>
Re: Query execution too long even after providing index
Posted by Evgenii Zhuravlev <e....@gmail.com>.
Well, looks like you trying to find the segments that intersects defined
segment, but you're complicating it. You don't need 3 conditions here - it
will be enough to have only one - ipStart <= MAX and ipEnd >= MIN. I've
checked it for your case and got absolutely the same results as you have
with a too complex query.
Aditionally, you have the same subscriptionId AND moduleId in your test
data, which means that you will have a bad selectivity when you will filter
by these fields at first. Do you really have such data in your production?
Also, when you will measure something again - do operation a lot of times -
that how benchmarks work. Ignite initialize internal structures at first
executions, so, it will not give an idea of overall latency if you will
measure it just once.
Best Regards,
Evgenii
пн, 22 окт. 2018 г. в 6:56, Prasad Bhalerao <pr...@gmail.com>:
> Hi Evgenii,
>
> Did you get time to check the reproducer?
>
> Can you please suggest solution for this?
>
>
> Thanks,
> Prasad
>
>
> On Fri, Oct 19, 2018, 4:46 PM Prasad Bhalerao <
> prasadbhalerao1983@gmail.com> wrote:
>
>> Hi Evgenii,
>>
>> I have created a reproducer and uploaded it to GitHub. I have created 5
>> cases to test the sql execution time.
>>
>> GitHub project: https://github.com/prasadbhalerao1983/IgniteTestPrj.git
>>
>> Please run IgniteQueryTester class.
>>
>> Thanks,
>> Prasad
>>
>> On Wed, Oct 17, 2018 at 7:46 PM ezhuravlev <e....@gmail.com>
>> wrote:
>>
>>> How much data do you have? What is the amount of heap and offheap
>>> memory?
>>> Can you share the reproducer with the community?
>>>
>>> Evgenii
>>>
>>>
>>>
>>> --
>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>
>>
Re: Query execution too long even after providing index
Posted by Prasad Bhalerao <pr...@gmail.com>.
Hi Evgenii,
Did you get time to check the reproducer?
Can you please suggest solution for this?
Thanks,
Prasad
On Fri, Oct 19, 2018, 4:46 PM Prasad Bhalerao <pr...@gmail.com>
wrote:
> Hi Evgenii,
>
> I have created a reproducer and uploaded it to GitHub. I have created 5
> cases to test the sql execution time.
>
> GitHub project: https://github.com/prasadbhalerao1983/IgniteTestPrj.git
>
> Please run IgniteQueryTester class.
>
> Thanks,
> Prasad
>
> On Wed, Oct 17, 2018 at 7:46 PM ezhuravlev <e....@gmail.com>
> wrote:
>
>> How much data do you have? What is the amount of heap and offheap memory?
>> Can you share the reproducer with the community?
>>
>> Evgenii
>>
>>
>>
>> --
>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>
>
Re: Query execution too long even after providing index
Posted by Prasad Bhalerao <pr...@gmail.com>.
Hi Evgenii,
I have created a reproducer and uploaded it to GitHub. I have created 5
cases to test the sql execution time.
GitHub project: https://github.com/prasadbhalerao1983/IgniteTestPrj.git
Please run IgniteQueryTester class.
Thanks,
Prasad
On Wed, Oct 17, 2018 at 7:46 PM ezhuravlev <e....@gmail.com> wrote:
> How much data do you have? What is the amount of heap and offheap memory?
> Can you share the reproducer with the community?
>
> Evgenii
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Query execution too long even after providing index
Posted by ezhuravlev <e....@gmail.com>.
How much data do you have? What is the amount of heap and offheap memory?
Can you share the reproducer with the community?
Evgenii
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/