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/12/26 16:00:38 UTC
Query Execution very slow
I am executing following SQL on ignite cache. This cache has 37 million
records and this data is distributed across 4 nodes.
*SQL:*
SELECT ipv4agd.id,
ipv4agd.assetGroupId,
ipv4agd.ipStart,
ipv4agd.ipEnd
FROM IpV4AssetGroupData ipv4agd
JOIN TABLE (assetGroupId bigint = ? ) temp
ON ipv4agd.assetGroupId = temp.assetGroupId
WHERE subscriptionId = ?
AND (ipStart <= ? AND ipEnd >= ?)
ORDER BY ipv4agd.assetGroupId
As per the execution plan show below, ignite is using index
"IPV4_ASSET_GROUP_DATA_IDX2 " and execution plan attached below.
This sql is taking around 23 seconds. I have set the max index inline size
as 65 bytes.
The number of assetGroupIds set in join clause are 50.
*Is there anything I can do to improve the performance of this SQL?*
I have checked the memory and cpu utilization and it it is very low. I also
tried to profile it using jprofiler to find out the issue, but could not
find solution. I have also attached profiler snapshot at the end. Please
check.
.
*Indexes:*
public class IpV4AssetGroupData implements
UpdatableData<DefaultDataAffinityKey> {
@QuerySqlField
private long id;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ipv4_asset_group_data_idx2", order = 2)})
private long assetGroupId;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ipv4_asset_group_data_idx1", order = 1),
@QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 1)})
private long subscriptionId;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ipv4_asset_group_data_idx1", order = 2),
@QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 3)})
private int ipStart;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ipv4_asset_group_data_idx1", order = 3),
@QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 4)})
private int ipEnd;
}
*Execution plan:*
Query execution is too long [time=15788 ms, sql='SELECT
IPV4AGD__Z0.ID __C0_0,
IPV4AGD__Z0.ASSETGROUPID __C0_1,
IPV4AGD__Z0.IPSTART __C0_2,
IPV4AGD__Z0.IPEND __C0_3
FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
ON TRUE
WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID) AND
((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) AND ((IPV4AGD__Z0.IPSTART <= ?3) AND
(IPV4AGD__Z0.IPEND >= ?4)))
ORDER BY 2',
plan=
SELECT
IPV4AGD__Z0.ID AS __C0_0,
IPV4AGD__Z0.ASSETGROUPID AS __C0_1,
IPV4AGD__Z0.IPSTART AS __C0_2,
IPV4AGD__Z0.IPEND AS __C0_3
FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
/* IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4_ASSET_GROUP_DATA_IDX2:
SUBSCRIPTIONID = ?2
AND IPSTART <= ?3
AND IPEND >= ?4
*/
/* WHERE (IPV4AGD__Z0.IPEND >= ?4)
AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
AND (IPV4AGD__Z0.IPSTART <= ?3))
*/
INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
/* function: ASSETGROUPID = IPV4AGD__Z0.ASSETGROUPID */
ON 1=1
WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID)
AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
AND ((IPV4AGD__Z0.IPSTART <= ?3)
AND (IPV4AGD__Z0.IPEND >= ?4)))
ORDER BY 2
, parameters=[[3483555, 3180458, 3250090, 3483563, 3182509, 3213230,
3245998, 3487661, 3215281, 3444657, 3182515, 3372974, 3483573, 3372981,
3200951, 3485624, 3295161, 3485626, 3379125, 3211196, 3213242, 3381181,
3194805, 3213247, 3258299, 3379123, 3377070, 3315637, 3352502, 3295174,
3485618, 3438530, 3483592, 3352516, 3155914, 3424204, 3192775, 3485643,
3317711, 3246026, 3209159, 3485584, 3485645, 3483594, 3248085, 3321799,
3248086, 3190744, 3211222, 3379162], 164307, 1084754675, -2094919442]]
Profiler snapshot:
[image: image.png]
Re: Query Execution very slow
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
There's a docs page at
https://apacheignite.readme.io/docs/configuration-parameters
This is because Ignite's query planner sometimes plans joins unoptimally
and you have to help it.
Regarding second sql, additional investigation is needed. Can you show
EXPLAIN after you apply enforceJoinOrder?
Regards,
--
Ilya Kasnacheev
пт, 28 дек. 2018 г. в 18:59, Prasad Bhalerao <pr...@gmail.com>:
> Hi,
>
> After setting enforceJoinOrder to true it worked. Can you please explain
> how did it work... pushing the data to temp table first and then
> enforeOrder?
>
> Is it documented in ignite docs?
>
> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
> getAffectedIPRange_3 :: *TimeTakenToComplete=10 ::* Size=1295
>
>
> *But second sql is still taking time. The only difference is it has
> ipStart and ipEnd filter in where clause.*
>
> getAffectedIPRange_2 :: SQL_2=SELECT ipv4agd.id, ipv4agd.assetGroupId,
> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
> WHERE subscriptionId = ? AND (ipStart <= ? AND ipEnd >= ?) ORDER BY
> ipv4agd.assetGroupId
> getAffectedIPRange_2 :: *TimeTakenToComplete=25436* :: Size=1260
>
> Thanks,
> Prasad
>
> On Fri, Dec 28, 2018 at 9:02 PM Ilya Kasnacheev <il...@gmail.com>
> wrote:
>
>> Hello!
>>
>> Did you set enforceJoinOrder to true?
>>
>> Regards,
>> --
>> Ilya Kasnacheev
>>
>>
>> пт, 28 дек. 2018 г. в 18:19, Prasad Bhalerao <
>> prasadbhalerao1983@gmail.com>:
>>
>>> Hi,
>>>
>>> I tried your suggestion but it did not work. It is taking 22.8 seconds.
>>>
>>> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>>> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
>>> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
>>>
>>> getAffectedIPRange_3 :: TimeTakenToComplete=22891 :: Size=1295
>>> Thanks,
>>> Prasad
>>>
>>> On Fri, Dec 28, 2018 at 8:32 PM Ilya Kasnacheev <
>>> ilya.kasnacheev@gmail.com> wrote:
>>>
>>>> Hello!
>>>>
>>>> I have created a PR for you:
>>>> https://github.com/prasadbhalerao1983/IgniteTestPrj/pull/1
>>>> With it, I can see:
>>>>
>>>> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>>>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>>>> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
>>>> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
>>>> getAffectedIPRange_3 :: TimeTakenToComplete=11 :: Size=1295
>>>>
>>>> Regards,
>>>> --
>>>> Ilya Kasnacheev
>>>>
>>>>
>>>> пт, 28 дек. 2018 г. в 17:46, Prasad Bhalerao <
>>>> prasadbhalerao1983@gmail.com>:
>>>>
>>>>> Can someone from community help me with t
>>>>>
>>>>> I have created a reproducer and uploaded it to GitHub. I have created
>>>>> 3 cases to test the sql execution time.
>>>>>
>>>>> Please run *IgniteQueryTester_4* class to check the issue.
>>>>> GitHub project: https://github
>>>>> .com/prasadbhalerao1983/IgniteTestPrj.git
>>>>>
>>>>> Thanks,
>>>>> Prasad
>>>>>
>>>>>
>>>>> On Wed, Dec 26, 2018 at 11:18 PM Prasad Bhalerao <
>>>>> prasadbhalerao1983@gmail.com> wrote:
>>>>>
>>>>>> How to push the ids to temp table, can you please give any example?
>>>>>> Is it a in memory temp table created by ignite?
>>>>>>
>>>>>> Can you please explain how enforceJoinOrder will help in this case?
>>>>>> Thanks ,
>>>>>> Prasad
>>>>>>
>>>>>> On Wed 26 Dec, 2018, 9:37 PM Ilya Kasnacheev <
>>>>>> ilya.kasnacheev@gmail.com wrote:
>>>>>>
>>>>>>> Hello!
>>>>>>>
>>>>>>> Can you try pushing temp table to 1st position and setting
>>>>>>> enforceJoinOrder=true?
>>>>>>>
>>>>>>> SELECT ipv4agd.id,
>>>>>>> ipv4agd.assetGroupId,
>>>>>>> ipv4agd.ipStart,
>>>>>>> ipv4agd.ipEnd
>>>>>>> FROM TABLE (assetGroupId bigint = ? ) temp
>>>>>>> JOIN IpV4AssetGroupData ipv4agd
>>>>>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>>>>>> WHERE subscriptionId = ?
>>>>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>>>>> ORDER BY ipv4agd.assetGroupId
>>>>>>>
>>>>>>> See https://apacheignite.readme.io/docs/configuration-parameters
>>>>>>> for enforceJoinOrder.
>>>>>>>
>>>>>>> Regards,
>>>>>>> --
>>>>>>> Ilya Kasnacheev
>>>>>>>
>>>>>>>
>>>>>>> ср, 26 дек. 2018 г. в 19:01, Prasad Bhalerao <
>>>>>>> prasadbhalerao1983@gmail.com>:
>>>>>>>
>>>>>>>> I am executing following SQL on ignite cache. This cache has 37
>>>>>>>> million records and this data is distributed across 4 nodes.
>>>>>>>> *SQL:*
>>>>>>>> SELECT ipv4agd.id,
>>>>>>>> ipv4agd.assetGroupId,
>>>>>>>> ipv4agd.ipStart,
>>>>>>>> ipv4agd.ipEnd
>>>>>>>> FROM IpV4AssetGroupData ipv4agd
>>>>>>>> JOIN TABLE (assetGroupId bigint = ? ) temp
>>>>>>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>>>>>>> WHERE subscriptionId = ?
>>>>>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>>>>>> ORDER BY ipv4agd.assetGroupId
>>>>>>>>
>>>>>>>>
>>>>>>>> As per the execution plan show below, ignite is using index
>>>>>>>> "IPV4_ASSET_GROUP_DATA_IDX2 " and execution plan attached below.
>>>>>>>>
>>>>>>>> This sql is taking around 23 seconds. I have set the max index
>>>>>>>> inline size as 65 bytes.
>>>>>>>> The number of assetGroupIds set in join clause are 50.
>>>>>>>>
>>>>>>>> *Is there anything I can do to improve the performance of this SQL?*
>>>>>>>>
>>>>>>>> I have checked the memory and cpu utilization and it it is very
>>>>>>>> low. I also tried to profile it using jprofiler to find out the issue, but
>>>>>>>> could not find solution. I have also attached profiler snapshot at the end.
>>>>>>>> Please check.
>>>>>>>>
>>>>>>>>
>>>>>>>> .
>>>>>>>>
>>>>>>>> *Indexes:*
>>>>>>>>
>>>>>>>> public class IpV4AssetGroupData implements UpdatableData<DefaultDataAffinityKey> {
>>>>>>>>
>>>>>>>> @QuerySqlField
>>>>>>>> private long id;
>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 2)})
>>>>>>>> private long assetGroupId;
>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 1),
>>>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 1)})
>>>>>>>> private long subscriptionId;
>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 2),
>>>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 3)})
>>>>>>>> private int ipStart;
>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 3),
>>>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 4)})
>>>>>>>> private int ipEnd;
>>>>>>>>
>>>>>>>> }
>>>>>>>>
>>>>>>>> *Execution plan:*
>>>>>>>>
>>>>>>>> Query execution is too long [time=15788 ms, sql='SELECT
>>>>>>>> IPV4AGD__Z0.ID __C0_0,
>>>>>>>> IPV4AGD__Z0.ASSETGROUPID __C0_1,
>>>>>>>> IPV4AGD__Z0.IPSTART __C0_2,
>>>>>>>> IPV4AGD__Z0.IPEND __C0_3
>>>>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>>>>> ON TRUE
>>>>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID) AND
>>>>>>>> ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) AND ((IPV4AGD__Z0.IPSTART <= ?3) AND
>>>>>>>> (IPV4AGD__Z0.IPEND >= ?4)))
>>>>>>>> ORDER BY 2',
>>>>>>>>
>>>>>>>> plan=
>>>>>>>>
>>>>>>>> SELECT
>>>>>>>> IPV4AGD__Z0.ID AS __C0_0,
>>>>>>>> IPV4AGD__Z0.ASSETGROUPID AS __C0_1,
>>>>>>>> IPV4AGD__Z0.IPSTART AS __C0_2,
>>>>>>>> IPV4AGD__Z0.IPEND AS __C0_3
>>>>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>>>>> /* IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4_ASSET_GROUP_DATA_IDX2:
>>>>>>>> SUBSCRIPTIONID = ?2
>>>>>>>> AND IPSTART <= ?3
>>>>>>>> AND IPEND >= ?4
>>>>>>>> */
>>>>>>>> /* WHERE (IPV4AGD__Z0.IPEND >= ?4)
>>>>>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>>>>> AND (IPV4AGD__Z0.IPSTART <= ?3))
>>>>>>>> */
>>>>>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>>>>> /* function: ASSETGROUPID = IPV4AGD__Z0.ASSETGROUPID */
>>>>>>>> ON 1=1
>>>>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID)
>>>>>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>>>>> AND ((IPV4AGD__Z0.IPSTART <= ?3)
>>>>>>>> AND (IPV4AGD__Z0.IPEND >= ?4)))
>>>>>>>> ORDER BY 2
>>>>>>>> , parameters=[[3483555, 3180458, 3250090, 3483563, 3182509,
>>>>>>>> 3213230, 3245998, 3487661, 3215281, 3444657, 3182515, 3372974, 3483573,
>>>>>>>> 3372981, 3200951, 3485624, 3295161, 3485626, 3379125, 3211196, 3213242,
>>>>>>>> 3381181, 3194805, 3213247, 3258299, 3379123, 3377070, 3315637, 3352502,
>>>>>>>> 3295174, 3485618, 3438530, 3483592, 3352516, 3155914, 3424204, 3192775,
>>>>>>>> 3485643, 3317711, 3246026, 3209159, 3485584, 3485645, 3483594, 3248085,
>>>>>>>> 3321799, 3248086, 3190744, 3211222, 3379162], 164307, 1084754675,
>>>>>>>> -2094919442]]
>>>>>>>>
>>>>>>>>
>>>>>>>> Profiler snapshot:
>>>>>>>>
>>>>>>>> [image: image.png]
>>>>>>>>
>>>>>>>
Re: Query Execution very slow
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
The answer to all your questions is "try it and see". I think that I have
shown you enough tools so that you are now ready for research of your own.
Regards,
--
Ilya Kasnacheev
сб, 29 дек. 2018 г. в 15:59, Prasad Bhalerao <pr...@gmail.com>:
> Hi Ilya,
>
> Thank you for the help, I tried your suggestion and it worked fine. For
> Second query h2 was selecting idx1 which was slowing it down.
>
> I have couple of questions.
> 1) Is there any limit for JOIN temp table clause? In some cases I have
> 17000 ids and it was causing sql execution to hang but it was before
> implementing you suggestion. So I had to split the 17K list into batch of
> 50 ids.
> But now I have to execute this sql 17000/50 times in a loop which is again
> slowing down the process. One thing is I can increase the batch size.
> Is there any way to handle it in better way?
>
> 2) Is it necessary to push the ids first to the temp table and then join
> it with necessary table like we did in this case?
>
> 3) When I executed the same sql with 900 ids in temp table, it took 30
> seconds and fetched 385332 rows. Does ignite suppose to take this much time
> when number ids in temp table join case increases?
>
>
> Regards,
> Prasad
>
> On Sat, Dec 29, 2018 at 4:58 PM Ilya Kasnacheev <il...@gmail.com>
> wrote:
>
>> Hello!
>>
>> I have updated my Pull Request with USE INDEX:
>> getAffectedIPRange_2 :: SQL_2=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>> JOIN IpV4AssetGroupData ipv4agd USE INDEX (ipv4_asset_group_data_idx2) ON
>> ipv4agd.assetGroupId = temp.assetGroupId WHERE subscriptionId = ? AND
>> (ipStart <= ? AND ipEnd >= ?) ORDER BY ipv4agd.assetGroupId
>> getAffectedIPRange_2 :: TimeTakenToComplete=9 :: Size=2
>>
>> getAffectedIPRange_2 :: SQL_2=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>> JOIN IpV4AssetGroupData ipv4agd USE INDEX (ipv4_asset_group_data_idx2) ON
>> ipv4agd.assetGroupId = temp.assetGroupId WHERE subscriptionId = ? AND
>> (ipStart <= ? AND ipEnd >= ?) ORDER BY ipv4agd.assetGroupId
>> getAffectedIPRange_2 :: TimeTakenToComplete=25 :: Size=1260
>>
>> Regards,
>> --
>> Ilya Kasnacheev
>>
>>
>> пт, 28 дек. 2018 г. в 18:59, Prasad Bhalerao <
>> prasadbhalerao1983@gmail.com>:
>>
>>> Hi,
>>>
>>> After setting enforceJoinOrder to true it worked. Can you please explain
>>> how did it work... pushing the data to temp table first and then
>>> enforeOrder?
>>>
>>> Is it documented in ignite docs?
>>>
>>> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>>> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
>>> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
>>> getAffectedIPRange_3 :: *TimeTakenToComplete=10 ::* Size=1295
>>>
>>>
>>> *But second sql is still taking time. The only difference is it has
>>> ipStart and ipEnd filter in where clause.*
>>>
>>> getAffectedIPRange_2 :: SQL_2=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>>> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
>>> WHERE subscriptionId = ? AND (ipStart <= ? AND ipEnd >= ?) ORDER BY
>>> ipv4agd.assetGroupId
>>> getAffectedIPRange_2 :: *TimeTakenToComplete=25436* :: Size=1260
>>>
>>> Thanks,
>>> Prasad
>>>
>>> On Fri, Dec 28, 2018 at 9:02 PM Ilya Kasnacheev <
>>> ilya.kasnacheev@gmail.com> wrote:
>>>
>>>> Hello!
>>>>
>>>> Did you set enforceJoinOrder to true?
>>>>
>>>> Regards,
>>>> --
>>>> Ilya Kasnacheev
>>>>
>>>>
>>>> пт, 28 дек. 2018 г. в 18:19, Prasad Bhalerao <
>>>> prasadbhalerao1983@gmail.com>:
>>>>
>>>>> Hi,
>>>>>
>>>>> I tried your suggestion but it did not work. It is taking 22.8 seconds.
>>>>>
>>>>> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id,
>>>>> ipv4agd.assetGroupId, ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE
>>>>> (assetGroupId bigint = ? ) temp JOIN IpV4AssetGroupData ipv4agd ON
>>>>> ipv4agd.assetGroupId = temp.assetGroupId WHERE subscriptionId = ? ORDER BY
>>>>> ipv4agd.assetGroupId
>>>>>
>>>>> getAffectedIPRange_3 :: TimeTakenToComplete=22891 :: Size=1295
>>>>> Thanks,
>>>>> Prasad
>>>>>
>>>>> On Fri, Dec 28, 2018 at 8:32 PM Ilya Kasnacheev <
>>>>> ilya.kasnacheev@gmail.com> wrote:
>>>>>
>>>>>> Hello!
>>>>>>
>>>>>> I have created a PR for you:
>>>>>> https://github.com/prasadbhalerao1983/IgniteTestPrj/pull/1
>>>>>> With it, I can see:
>>>>>>
>>>>>> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id,
>>>>>> ipv4agd.assetGroupId, ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE
>>>>>> (assetGroupId bigint = ? ) temp JOIN IpV4AssetGroupData ipv4agd ON
>>>>>> ipv4agd.assetGroupId = temp.assetGroupId WHERE subscriptionId = ? ORDER BY
>>>>>> ipv4agd.assetGroupId
>>>>>> getAffectedIPRange_3 :: TimeTakenToComplete=11 :: Size=1295
>>>>>>
>>>>>> Regards,
>>>>>> --
>>>>>> Ilya Kasnacheev
>>>>>>
>>>>>>
>>>>>> пт, 28 дек. 2018 г. в 17:46, Prasad Bhalerao <
>>>>>> prasadbhalerao1983@gmail.com>:
>>>>>>
>>>>>>> Can someone from community help me with t
>>>>>>>
>>>>>>> I have created a reproducer and uploaded it to GitHub. I have
>>>>>>> created 3 cases to test the sql execution time.
>>>>>>>
>>>>>>> Please run *IgniteQueryTester_4* class to check the issue.
>>>>>>> GitHub project: https://github
>>>>>>> .com/prasadbhalerao1983/IgniteTestPrj.git
>>>>>>>
>>>>>>> Thanks,
>>>>>>> Prasad
>>>>>>>
>>>>>>>
>>>>>>> On Wed, Dec 26, 2018 at 11:18 PM Prasad Bhalerao <
>>>>>>> prasadbhalerao1983@gmail.com> wrote:
>>>>>>>
>>>>>>>> How to push the ids to temp table, can you please give any example?
>>>>>>>> Is it a in memory temp table created by ignite?
>>>>>>>>
>>>>>>>> Can you please explain how enforceJoinOrder will help in this case?
>>>>>>>> Thanks ,
>>>>>>>> Prasad
>>>>>>>>
>>>>>>>> On Wed 26 Dec, 2018, 9:37 PM Ilya Kasnacheev <
>>>>>>>> ilya.kasnacheev@gmail.com wrote:
>>>>>>>>
>>>>>>>>> Hello!
>>>>>>>>>
>>>>>>>>> Can you try pushing temp table to 1st position and setting
>>>>>>>>> enforceJoinOrder=true?
>>>>>>>>>
>>>>>>>>> SELECT ipv4agd.id,
>>>>>>>>> ipv4agd.assetGroupId,
>>>>>>>>> ipv4agd.ipStart,
>>>>>>>>> ipv4agd.ipEnd
>>>>>>>>> FROM TABLE (assetGroupId bigint = ? ) temp
>>>>>>>>> JOIN IpV4AssetGroupData ipv4agd
>>>>>>>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>>>>>>>> WHERE subscriptionId = ?
>>>>>>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>>>>>>> ORDER BY ipv4agd.assetGroupId
>>>>>>>>>
>>>>>>>>> See https://apacheignite.readme.io/docs/configuration-parameters
>>>>>>>>> for enforceJoinOrder.
>>>>>>>>>
>>>>>>>>> Regards,
>>>>>>>>> --
>>>>>>>>> Ilya Kasnacheev
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> ср, 26 дек. 2018 г. в 19:01, Prasad Bhalerao <
>>>>>>>>> prasadbhalerao1983@gmail.com>:
>>>>>>>>>
>>>>>>>>>> I am executing following SQL on ignite cache. This cache has 37
>>>>>>>>>> million records and this data is distributed across 4 nodes.
>>>>>>>>>> *SQL:*
>>>>>>>>>> SELECT ipv4agd.id,
>>>>>>>>>> ipv4agd.assetGroupId,
>>>>>>>>>> ipv4agd.ipStart,
>>>>>>>>>> ipv4agd.ipEnd
>>>>>>>>>> FROM IpV4AssetGroupData ipv4agd
>>>>>>>>>> JOIN TABLE (assetGroupId bigint = ? ) temp
>>>>>>>>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>>>>>>>>> WHERE subscriptionId = ?
>>>>>>>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>>>>>>>> ORDER BY ipv4agd.assetGroupId
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> As per the execution plan show below, ignite is using index
>>>>>>>>>> "IPV4_ASSET_GROUP_DATA_IDX2 " and execution plan attached below.
>>>>>>>>>>
>>>>>>>>>> This sql is taking around 23 seconds. I have set the max index
>>>>>>>>>> inline size as 65 bytes.
>>>>>>>>>> The number of assetGroupIds set in join clause are 50.
>>>>>>>>>>
>>>>>>>>>> *Is there anything I can do to improve the performance of this
>>>>>>>>>> SQL?*
>>>>>>>>>>
>>>>>>>>>> I have checked the memory and cpu utilization and it it is very
>>>>>>>>>> low. I also tried to profile it using jprofiler to find out the issue, but
>>>>>>>>>> could not find solution. I have also attached profiler snapshot at the end.
>>>>>>>>>> Please check.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> .
>>>>>>>>>>
>>>>>>>>>> *Indexes:*
>>>>>>>>>>
>>>>>>>>>> public class IpV4AssetGroupData implements UpdatableData<DefaultDataAffinityKey> {
>>>>>>>>>>
>>>>>>>>>> @QuerySqlField
>>>>>>>>>> private long id;
>>>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 2)})
>>>>>>>>>> private long assetGroupId;
>>>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 1),
>>>>>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 1)})
>>>>>>>>>> private long subscriptionId;
>>>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 2),
>>>>>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 3)})
>>>>>>>>>> private int ipStart;
>>>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 3),
>>>>>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 4)})
>>>>>>>>>> private int ipEnd;
>>>>>>>>>>
>>>>>>>>>> }
>>>>>>>>>>
>>>>>>>>>> *Execution plan:*
>>>>>>>>>>
>>>>>>>>>> Query execution is too long [time=15788 ms, sql='SELECT
>>>>>>>>>> IPV4AGD__Z0.ID __C0_0,
>>>>>>>>>> IPV4AGD__Z0.ASSETGROUPID __C0_1,
>>>>>>>>>> IPV4AGD__Z0.IPSTART __C0_2,
>>>>>>>>>> IPV4AGD__Z0.IPEND __C0_3
>>>>>>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>>>>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>>>>>>> ON TRUE
>>>>>>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID) AND
>>>>>>>>>> ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) AND ((IPV4AGD__Z0.IPSTART <= ?3) AND
>>>>>>>>>> (IPV4AGD__Z0.IPEND >= ?4)))
>>>>>>>>>> ORDER BY 2',
>>>>>>>>>>
>>>>>>>>>> plan=
>>>>>>>>>>
>>>>>>>>>> SELECT
>>>>>>>>>> IPV4AGD__Z0.ID AS __C0_0,
>>>>>>>>>> IPV4AGD__Z0.ASSETGROUPID AS __C0_1,
>>>>>>>>>> IPV4AGD__Z0.IPSTART AS __C0_2,
>>>>>>>>>> IPV4AGD__Z0.IPEND AS __C0_3
>>>>>>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>>>>>>> /* IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4_ASSET_GROUP_DATA_IDX2:
>>>>>>>>>> SUBSCRIPTIONID = ?2
>>>>>>>>>> AND IPSTART <= ?3
>>>>>>>>>> AND IPEND >= ?4
>>>>>>>>>> */
>>>>>>>>>> /* WHERE (IPV4AGD__Z0.IPEND >= ?4)
>>>>>>>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>>>>>>> AND (IPV4AGD__Z0.IPSTART <= ?3))
>>>>>>>>>> */
>>>>>>>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>>>>>>> /* function: ASSETGROUPID = IPV4AGD__Z0.ASSETGROUPID */
>>>>>>>>>> ON 1=1
>>>>>>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID)
>>>>>>>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>>>>>>> AND ((IPV4AGD__Z0.IPSTART <= ?3)
>>>>>>>>>> AND (IPV4AGD__Z0.IPEND >= ?4)))
>>>>>>>>>> ORDER BY 2
>>>>>>>>>> , parameters=[[3483555, 3180458, 3250090, 3483563, 3182509,
>>>>>>>>>> 3213230, 3245998, 3487661, 3215281, 3444657, 3182515, 3372974, 3483573,
>>>>>>>>>> 3372981, 3200951, 3485624, 3295161, 3485626, 3379125, 3211196, 3213242,
>>>>>>>>>> 3381181, 3194805, 3213247, 3258299, 3379123, 3377070, 3315637, 3352502,
>>>>>>>>>> 3295174, 3485618, 3438530, 3483592, 3352516, 3155914, 3424204, 3192775,
>>>>>>>>>> 3485643, 3317711, 3246026, 3209159, 3485584, 3485645, 3483594, 3248085,
>>>>>>>>>> 3321799, 3248086, 3190744, 3211222, 3379162], 164307, 1084754675,
>>>>>>>>>> -2094919442]]
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Profiler snapshot:
>>>>>>>>>>
>>>>>>>>>> [image: image.png]
>>>>>>>>>>
>>>>>>>>>
Re: Query Execution very slow
Posted by Prasad Bhalerao <pr...@gmail.com>.
Hi Ilya,
Thank you for the help, I tried your suggestion and it worked fine. For
Second query h2 was selecting idx1 which was slowing it down.
I have couple of questions.
1) Is there any limit for JOIN temp table clause? In some cases I have
17000 ids and it was causing sql execution to hang but it was before
implementing you suggestion. So I had to split the 17K list into batch of
50 ids.
But now I have to execute this sql 17000/50 times in a loop which is again
slowing down the process. One thing is I can increase the batch size.
Is there any way to handle it in better way?
2) Is it necessary to push the ids first to the temp table and then join it
with necessary table like we did in this case?
3) When I executed the same sql with 900 ids in temp table, it took 30
seconds and fetched 385332 rows. Does ignite suppose to take this much time
when number ids in temp table join case increases?
Regards,
Prasad
On Sat, Dec 29, 2018 at 4:58 PM Ilya Kasnacheev <il...@gmail.com>
wrote:
> Hello!
>
> I have updated my Pull Request with USE INDEX:
> getAffectedIPRange_2 :: SQL_2=SELECT ipv4agd.id, ipv4agd.assetGroupId,
> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
> JOIN IpV4AssetGroupData ipv4agd USE INDEX (ipv4_asset_group_data_idx2) ON
> ipv4agd.assetGroupId = temp.assetGroupId WHERE subscriptionId = ? AND
> (ipStart <= ? AND ipEnd >= ?) ORDER BY ipv4agd.assetGroupId
> getAffectedIPRange_2 :: TimeTakenToComplete=9 :: Size=2
>
> getAffectedIPRange_2 :: SQL_2=SELECT ipv4agd.id, ipv4agd.assetGroupId,
> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
> JOIN IpV4AssetGroupData ipv4agd USE INDEX (ipv4_asset_group_data_idx2) ON
> ipv4agd.assetGroupId = temp.assetGroupId WHERE subscriptionId = ? AND
> (ipStart <= ? AND ipEnd >= ?) ORDER BY ipv4agd.assetGroupId
> getAffectedIPRange_2 :: TimeTakenToComplete=25 :: Size=1260
>
> Regards,
> --
> Ilya Kasnacheev
>
>
> пт, 28 дек. 2018 г. в 18:59, Prasad Bhalerao <prasadbhalerao1983@gmail.com
> >:
>
>> Hi,
>>
>> After setting enforceJoinOrder to true it worked. Can you please explain
>> how did it work... pushing the data to temp table first and then
>> enforeOrder?
>>
>> Is it documented in ignite docs?
>>
>> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
>> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
>> getAffectedIPRange_3 :: *TimeTakenToComplete=10 ::* Size=1295
>>
>>
>> *But second sql is still taking time. The only difference is it has
>> ipStart and ipEnd filter in where clause.*
>>
>> getAffectedIPRange_2 :: SQL_2=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
>> WHERE subscriptionId = ? AND (ipStart <= ? AND ipEnd >= ?) ORDER BY
>> ipv4agd.assetGroupId
>> getAffectedIPRange_2 :: *TimeTakenToComplete=25436* :: Size=1260
>>
>> Thanks,
>> Prasad
>>
>> On Fri, Dec 28, 2018 at 9:02 PM Ilya Kasnacheev <
>> ilya.kasnacheev@gmail.com> wrote:
>>
>>> Hello!
>>>
>>> Did you set enforceJoinOrder to true?
>>>
>>> Regards,
>>> --
>>> Ilya Kasnacheev
>>>
>>>
>>> пт, 28 дек. 2018 г. в 18:19, Prasad Bhalerao <
>>> prasadbhalerao1983@gmail.com>:
>>>
>>>> Hi,
>>>>
>>>> I tried your suggestion but it did not work. It is taking 22.8 seconds.
>>>>
>>>> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>>>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>>>> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
>>>> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
>>>>
>>>> getAffectedIPRange_3 :: TimeTakenToComplete=22891 :: Size=1295
>>>> Thanks,
>>>> Prasad
>>>>
>>>> On Fri, Dec 28, 2018 at 8:32 PM Ilya Kasnacheev <
>>>> ilya.kasnacheev@gmail.com> wrote:
>>>>
>>>>> Hello!
>>>>>
>>>>> I have created a PR for you:
>>>>> https://github.com/prasadbhalerao1983/IgniteTestPrj/pull/1
>>>>> With it, I can see:
>>>>>
>>>>> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id,
>>>>> ipv4agd.assetGroupId, ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE
>>>>> (assetGroupId bigint = ? ) temp JOIN IpV4AssetGroupData ipv4agd ON
>>>>> ipv4agd.assetGroupId = temp.assetGroupId WHERE subscriptionId = ? ORDER BY
>>>>> ipv4agd.assetGroupId
>>>>> getAffectedIPRange_3 :: TimeTakenToComplete=11 :: Size=1295
>>>>>
>>>>> Regards,
>>>>> --
>>>>> Ilya Kasnacheev
>>>>>
>>>>>
>>>>> пт, 28 дек. 2018 г. в 17:46, Prasad Bhalerao <
>>>>> prasadbhalerao1983@gmail.com>:
>>>>>
>>>>>> Can someone from community help me with t
>>>>>>
>>>>>> I have created a reproducer and uploaded it to GitHub. I have
>>>>>> created 3 cases to test the sql execution time.
>>>>>>
>>>>>> Please run *IgniteQueryTester_4* class to check the issue.
>>>>>> GitHub project: https://github
>>>>>> .com/prasadbhalerao1983/IgniteTestPrj.git
>>>>>>
>>>>>> Thanks,
>>>>>> Prasad
>>>>>>
>>>>>>
>>>>>> On Wed, Dec 26, 2018 at 11:18 PM Prasad Bhalerao <
>>>>>> prasadbhalerao1983@gmail.com> wrote:
>>>>>>
>>>>>>> How to push the ids to temp table, can you please give any example?
>>>>>>> Is it a in memory temp table created by ignite?
>>>>>>>
>>>>>>> Can you please explain how enforceJoinOrder will help in this case?
>>>>>>> Thanks ,
>>>>>>> Prasad
>>>>>>>
>>>>>>> On Wed 26 Dec, 2018, 9:37 PM Ilya Kasnacheev <
>>>>>>> ilya.kasnacheev@gmail.com wrote:
>>>>>>>
>>>>>>>> Hello!
>>>>>>>>
>>>>>>>> Can you try pushing temp table to 1st position and setting
>>>>>>>> enforceJoinOrder=true?
>>>>>>>>
>>>>>>>> SELECT ipv4agd.id,
>>>>>>>> ipv4agd.assetGroupId,
>>>>>>>> ipv4agd.ipStart,
>>>>>>>> ipv4agd.ipEnd
>>>>>>>> FROM TABLE (assetGroupId bigint = ? ) temp
>>>>>>>> JOIN IpV4AssetGroupData ipv4agd
>>>>>>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>>>>>>> WHERE subscriptionId = ?
>>>>>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>>>>>> ORDER BY ipv4agd.assetGroupId
>>>>>>>>
>>>>>>>> See https://apacheignite.readme.io/docs/configuration-parameters
>>>>>>>> for enforceJoinOrder.
>>>>>>>>
>>>>>>>> Regards,
>>>>>>>> --
>>>>>>>> Ilya Kasnacheev
>>>>>>>>
>>>>>>>>
>>>>>>>> ср, 26 дек. 2018 г. в 19:01, Prasad Bhalerao <
>>>>>>>> prasadbhalerao1983@gmail.com>:
>>>>>>>>
>>>>>>>>> I am executing following SQL on ignite cache. This cache has 37
>>>>>>>>> million records and this data is distributed across 4 nodes.
>>>>>>>>> *SQL:*
>>>>>>>>> SELECT ipv4agd.id,
>>>>>>>>> ipv4agd.assetGroupId,
>>>>>>>>> ipv4agd.ipStart,
>>>>>>>>> ipv4agd.ipEnd
>>>>>>>>> FROM IpV4AssetGroupData ipv4agd
>>>>>>>>> JOIN TABLE (assetGroupId bigint = ? ) temp
>>>>>>>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>>>>>>>> WHERE subscriptionId = ?
>>>>>>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>>>>>>> ORDER BY ipv4agd.assetGroupId
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> As per the execution plan show below, ignite is using index
>>>>>>>>> "IPV4_ASSET_GROUP_DATA_IDX2 " and execution plan attached below.
>>>>>>>>>
>>>>>>>>> This sql is taking around 23 seconds. I have set the max index
>>>>>>>>> inline size as 65 bytes.
>>>>>>>>> The number of assetGroupIds set in join clause are 50.
>>>>>>>>>
>>>>>>>>> *Is there anything I can do to improve the performance of this
>>>>>>>>> SQL?*
>>>>>>>>>
>>>>>>>>> I have checked the memory and cpu utilization and it it is very
>>>>>>>>> low. I also tried to profile it using jprofiler to find out the issue, but
>>>>>>>>> could not find solution. I have also attached profiler snapshot at the end.
>>>>>>>>> Please check.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> .
>>>>>>>>>
>>>>>>>>> *Indexes:*
>>>>>>>>>
>>>>>>>>> public class IpV4AssetGroupData implements UpdatableData<DefaultDataAffinityKey> {
>>>>>>>>>
>>>>>>>>> @QuerySqlField
>>>>>>>>> private long id;
>>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 2)})
>>>>>>>>> private long assetGroupId;
>>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 1),
>>>>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 1)})
>>>>>>>>> private long subscriptionId;
>>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 2),
>>>>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 3)})
>>>>>>>>> private int ipStart;
>>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 3),
>>>>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 4)})
>>>>>>>>> private int ipEnd;
>>>>>>>>>
>>>>>>>>> }
>>>>>>>>>
>>>>>>>>> *Execution plan:*
>>>>>>>>>
>>>>>>>>> Query execution is too long [time=15788 ms, sql='SELECT
>>>>>>>>> IPV4AGD__Z0.ID __C0_0,
>>>>>>>>> IPV4AGD__Z0.ASSETGROUPID __C0_1,
>>>>>>>>> IPV4AGD__Z0.IPSTART __C0_2,
>>>>>>>>> IPV4AGD__Z0.IPEND __C0_3
>>>>>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>>>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>>>>>> ON TRUE
>>>>>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID) AND
>>>>>>>>> ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) AND ((IPV4AGD__Z0.IPSTART <= ?3) AND
>>>>>>>>> (IPV4AGD__Z0.IPEND >= ?4)))
>>>>>>>>> ORDER BY 2',
>>>>>>>>>
>>>>>>>>> plan=
>>>>>>>>>
>>>>>>>>> SELECT
>>>>>>>>> IPV4AGD__Z0.ID AS __C0_0,
>>>>>>>>> IPV4AGD__Z0.ASSETGROUPID AS __C0_1,
>>>>>>>>> IPV4AGD__Z0.IPSTART AS __C0_2,
>>>>>>>>> IPV4AGD__Z0.IPEND AS __C0_3
>>>>>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>>>>>> /* IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4_ASSET_GROUP_DATA_IDX2:
>>>>>>>>> SUBSCRIPTIONID = ?2
>>>>>>>>> AND IPSTART <= ?3
>>>>>>>>> AND IPEND >= ?4
>>>>>>>>> */
>>>>>>>>> /* WHERE (IPV4AGD__Z0.IPEND >= ?4)
>>>>>>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>>>>>> AND (IPV4AGD__Z0.IPSTART <= ?3))
>>>>>>>>> */
>>>>>>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>>>>>> /* function: ASSETGROUPID = IPV4AGD__Z0.ASSETGROUPID */
>>>>>>>>> ON 1=1
>>>>>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID)
>>>>>>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>>>>>> AND ((IPV4AGD__Z0.IPSTART <= ?3)
>>>>>>>>> AND (IPV4AGD__Z0.IPEND >= ?4)))
>>>>>>>>> ORDER BY 2
>>>>>>>>> , parameters=[[3483555, 3180458, 3250090, 3483563, 3182509,
>>>>>>>>> 3213230, 3245998, 3487661, 3215281, 3444657, 3182515, 3372974, 3483573,
>>>>>>>>> 3372981, 3200951, 3485624, 3295161, 3485626, 3379125, 3211196, 3213242,
>>>>>>>>> 3381181, 3194805, 3213247, 3258299, 3379123, 3377070, 3315637, 3352502,
>>>>>>>>> 3295174, 3485618, 3438530, 3483592, 3352516, 3155914, 3424204, 3192775,
>>>>>>>>> 3485643, 3317711, 3246026, 3209159, 3485584, 3485645, 3483594, 3248085,
>>>>>>>>> 3321799, 3248086, 3190744, 3211222, 3379162], 164307, 1084754675,
>>>>>>>>> -2094919442]]
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Profiler snapshot:
>>>>>>>>>
>>>>>>>>> [image: image.png]
>>>>>>>>>
>>>>>>>>
Re: Query Execution very slow
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
I have updated my Pull Request with USE INDEX:
getAffectedIPRange_2 :: SQL_2=SELECT ipv4agd.id, ipv4agd.assetGroupId,
ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
JOIN IpV4AssetGroupData ipv4agd USE INDEX (ipv4_asset_group_data_idx2) ON
ipv4agd.assetGroupId = temp.assetGroupId WHERE subscriptionId = ? AND
(ipStart <= ? AND ipEnd >= ?) ORDER BY ipv4agd.assetGroupId
getAffectedIPRange_2 :: TimeTakenToComplete=9 :: Size=2
getAffectedIPRange_2 :: SQL_2=SELECT ipv4agd.id, ipv4agd.assetGroupId,
ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
JOIN IpV4AssetGroupData ipv4agd USE INDEX (ipv4_asset_group_data_idx2) ON
ipv4agd.assetGroupId = temp.assetGroupId WHERE subscriptionId = ? AND
(ipStart <= ? AND ipEnd >= ?) ORDER BY ipv4agd.assetGroupId
getAffectedIPRange_2 :: TimeTakenToComplete=25 :: Size=1260
Regards,
--
Ilya Kasnacheev
пт, 28 дек. 2018 г. в 18:59, Prasad Bhalerao <pr...@gmail.com>:
> Hi,
>
> After setting enforceJoinOrder to true it worked. Can you please explain
> how did it work... pushing the data to temp table first and then
> enforeOrder?
>
> Is it documented in ignite docs?
>
> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
> getAffectedIPRange_3 :: *TimeTakenToComplete=10 ::* Size=1295
>
>
> *But second sql is still taking time. The only difference is it has
> ipStart and ipEnd filter in where clause.*
>
> getAffectedIPRange_2 :: SQL_2=SELECT ipv4agd.id, ipv4agd.assetGroupId,
> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
> WHERE subscriptionId = ? AND (ipStart <= ? AND ipEnd >= ?) ORDER BY
> ipv4agd.assetGroupId
> getAffectedIPRange_2 :: *TimeTakenToComplete=25436* :: Size=1260
>
> Thanks,
> Prasad
>
> On Fri, Dec 28, 2018 at 9:02 PM Ilya Kasnacheev <il...@gmail.com>
> wrote:
>
>> Hello!
>>
>> Did you set enforceJoinOrder to true?
>>
>> Regards,
>> --
>> Ilya Kasnacheev
>>
>>
>> пт, 28 дек. 2018 г. в 18:19, Prasad Bhalerao <
>> prasadbhalerao1983@gmail.com>:
>>
>>> Hi,
>>>
>>> I tried your suggestion but it did not work. It is taking 22.8 seconds.
>>>
>>> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>>> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
>>> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
>>>
>>> getAffectedIPRange_3 :: TimeTakenToComplete=22891 :: Size=1295
>>> Thanks,
>>> Prasad
>>>
>>> On Fri, Dec 28, 2018 at 8:32 PM Ilya Kasnacheev <
>>> ilya.kasnacheev@gmail.com> wrote:
>>>
>>>> Hello!
>>>>
>>>> I have created a PR for you:
>>>> https://github.com/prasadbhalerao1983/IgniteTestPrj/pull/1
>>>> With it, I can see:
>>>>
>>>> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>>>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>>>> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
>>>> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
>>>> getAffectedIPRange_3 :: TimeTakenToComplete=11 :: Size=1295
>>>>
>>>> Regards,
>>>> --
>>>> Ilya Kasnacheev
>>>>
>>>>
>>>> пт, 28 дек. 2018 г. в 17:46, Prasad Bhalerao <
>>>> prasadbhalerao1983@gmail.com>:
>>>>
>>>>> Can someone from community help me with t
>>>>>
>>>>> I have created a reproducer and uploaded it to GitHub. I have created
>>>>> 3 cases to test the sql execution time.
>>>>>
>>>>> Please run *IgniteQueryTester_4* class to check the issue.
>>>>> GitHub project: https://github
>>>>> .com/prasadbhalerao1983/IgniteTestPrj.git
>>>>>
>>>>> Thanks,
>>>>> Prasad
>>>>>
>>>>>
>>>>> On Wed, Dec 26, 2018 at 11:18 PM Prasad Bhalerao <
>>>>> prasadbhalerao1983@gmail.com> wrote:
>>>>>
>>>>>> How to push the ids to temp table, can you please give any example?
>>>>>> Is it a in memory temp table created by ignite?
>>>>>>
>>>>>> Can you please explain how enforceJoinOrder will help in this case?
>>>>>> Thanks ,
>>>>>> Prasad
>>>>>>
>>>>>> On Wed 26 Dec, 2018, 9:37 PM Ilya Kasnacheev <
>>>>>> ilya.kasnacheev@gmail.com wrote:
>>>>>>
>>>>>>> Hello!
>>>>>>>
>>>>>>> Can you try pushing temp table to 1st position and setting
>>>>>>> enforceJoinOrder=true?
>>>>>>>
>>>>>>> SELECT ipv4agd.id,
>>>>>>> ipv4agd.assetGroupId,
>>>>>>> ipv4agd.ipStart,
>>>>>>> ipv4agd.ipEnd
>>>>>>> FROM TABLE (assetGroupId bigint = ? ) temp
>>>>>>> JOIN IpV4AssetGroupData ipv4agd
>>>>>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>>>>>> WHERE subscriptionId = ?
>>>>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>>>>> ORDER BY ipv4agd.assetGroupId
>>>>>>>
>>>>>>> See https://apacheignite.readme.io/docs/configuration-parameters
>>>>>>> for enforceJoinOrder.
>>>>>>>
>>>>>>> Regards,
>>>>>>> --
>>>>>>> Ilya Kasnacheev
>>>>>>>
>>>>>>>
>>>>>>> ср, 26 дек. 2018 г. в 19:01, Prasad Bhalerao <
>>>>>>> prasadbhalerao1983@gmail.com>:
>>>>>>>
>>>>>>>> I am executing following SQL on ignite cache. This cache has 37
>>>>>>>> million records and this data is distributed across 4 nodes.
>>>>>>>> *SQL:*
>>>>>>>> SELECT ipv4agd.id,
>>>>>>>> ipv4agd.assetGroupId,
>>>>>>>> ipv4agd.ipStart,
>>>>>>>> ipv4agd.ipEnd
>>>>>>>> FROM IpV4AssetGroupData ipv4agd
>>>>>>>> JOIN TABLE (assetGroupId bigint = ? ) temp
>>>>>>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>>>>>>> WHERE subscriptionId = ?
>>>>>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>>>>>> ORDER BY ipv4agd.assetGroupId
>>>>>>>>
>>>>>>>>
>>>>>>>> As per the execution plan show below, ignite is using index
>>>>>>>> "IPV4_ASSET_GROUP_DATA_IDX2 " and execution plan attached below.
>>>>>>>>
>>>>>>>> This sql is taking around 23 seconds. I have set the max index
>>>>>>>> inline size as 65 bytes.
>>>>>>>> The number of assetGroupIds set in join clause are 50.
>>>>>>>>
>>>>>>>> *Is there anything I can do to improve the performance of this SQL?*
>>>>>>>>
>>>>>>>> I have checked the memory and cpu utilization and it it is very
>>>>>>>> low. I also tried to profile it using jprofiler to find out the issue, but
>>>>>>>> could not find solution. I have also attached profiler snapshot at the end.
>>>>>>>> Please check.
>>>>>>>>
>>>>>>>>
>>>>>>>> .
>>>>>>>>
>>>>>>>> *Indexes:*
>>>>>>>>
>>>>>>>> public class IpV4AssetGroupData implements UpdatableData<DefaultDataAffinityKey> {
>>>>>>>>
>>>>>>>> @QuerySqlField
>>>>>>>> private long id;
>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 2)})
>>>>>>>> private long assetGroupId;
>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 1),
>>>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 1)})
>>>>>>>> private long subscriptionId;
>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 2),
>>>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 3)})
>>>>>>>> private int ipStart;
>>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 3),
>>>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 4)})
>>>>>>>> private int ipEnd;
>>>>>>>>
>>>>>>>> }
>>>>>>>>
>>>>>>>> *Execution plan:*
>>>>>>>>
>>>>>>>> Query execution is too long [time=15788 ms, sql='SELECT
>>>>>>>> IPV4AGD__Z0.ID __C0_0,
>>>>>>>> IPV4AGD__Z0.ASSETGROUPID __C0_1,
>>>>>>>> IPV4AGD__Z0.IPSTART __C0_2,
>>>>>>>> IPV4AGD__Z0.IPEND __C0_3
>>>>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>>>>> ON TRUE
>>>>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID) AND
>>>>>>>> ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) AND ((IPV4AGD__Z0.IPSTART <= ?3) AND
>>>>>>>> (IPV4AGD__Z0.IPEND >= ?4)))
>>>>>>>> ORDER BY 2',
>>>>>>>>
>>>>>>>> plan=
>>>>>>>>
>>>>>>>> SELECT
>>>>>>>> IPV4AGD__Z0.ID AS __C0_0,
>>>>>>>> IPV4AGD__Z0.ASSETGROUPID AS __C0_1,
>>>>>>>> IPV4AGD__Z0.IPSTART AS __C0_2,
>>>>>>>> IPV4AGD__Z0.IPEND AS __C0_3
>>>>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>>>>> /* IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4_ASSET_GROUP_DATA_IDX2:
>>>>>>>> SUBSCRIPTIONID = ?2
>>>>>>>> AND IPSTART <= ?3
>>>>>>>> AND IPEND >= ?4
>>>>>>>> */
>>>>>>>> /* WHERE (IPV4AGD__Z0.IPEND >= ?4)
>>>>>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>>>>> AND (IPV4AGD__Z0.IPSTART <= ?3))
>>>>>>>> */
>>>>>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>>>>> /* function: ASSETGROUPID = IPV4AGD__Z0.ASSETGROUPID */
>>>>>>>> ON 1=1
>>>>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID)
>>>>>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>>>>> AND ((IPV4AGD__Z0.IPSTART <= ?3)
>>>>>>>> AND (IPV4AGD__Z0.IPEND >= ?4)))
>>>>>>>> ORDER BY 2
>>>>>>>> , parameters=[[3483555, 3180458, 3250090, 3483563, 3182509,
>>>>>>>> 3213230, 3245998, 3487661, 3215281, 3444657, 3182515, 3372974, 3483573,
>>>>>>>> 3372981, 3200951, 3485624, 3295161, 3485626, 3379125, 3211196, 3213242,
>>>>>>>> 3381181, 3194805, 3213247, 3258299, 3379123, 3377070, 3315637, 3352502,
>>>>>>>> 3295174, 3485618, 3438530, 3483592, 3352516, 3155914, 3424204, 3192775,
>>>>>>>> 3485643, 3317711, 3246026, 3209159, 3485584, 3485645, 3483594, 3248085,
>>>>>>>> 3321799, 3248086, 3190744, 3211222, 3379162], 164307, 1084754675,
>>>>>>>> -2094919442]]
>>>>>>>>
>>>>>>>>
>>>>>>>> Profiler snapshot:
>>>>>>>>
>>>>>>>> [image: image.png]
>>>>>>>>
>>>>>>>
Re: Query Execution very slow
Posted by Prasad Bhalerao <pr...@gmail.com>.
Hi,
After setting enforceJoinOrder to true it worked. Can you please explain
how did it work... pushing the data to temp table first and then
enforeOrder?
Is it documented in ignite docs?
getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
getAffectedIPRange_3 :: *TimeTakenToComplete=10 ::* Size=1295
*But second sql is still taking time. The only difference is it has ipStart
and ipEnd filter in where clause.*
getAffectedIPRange_2 :: SQL_2=SELECT ipv4agd.id, ipv4agd.assetGroupId,
ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
WHERE subscriptionId = ? AND (ipStart <= ? AND ipEnd >= ?) ORDER BY
ipv4agd.assetGroupId
getAffectedIPRange_2 :: *TimeTakenToComplete=25436* :: Size=1260
Thanks,
Prasad
On Fri, Dec 28, 2018 at 9:02 PM Ilya Kasnacheev <il...@gmail.com>
wrote:
> Hello!
>
> Did you set enforceJoinOrder to true?
>
> Regards,
> --
> Ilya Kasnacheev
>
>
> пт, 28 дек. 2018 г. в 18:19, Prasad Bhalerao <prasadbhalerao1983@gmail.com
> >:
>
>> Hi,
>>
>> I tried your suggestion but it did not work. It is taking 22.8 seconds.
>>
>> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
>> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
>>
>> getAffectedIPRange_3 :: TimeTakenToComplete=22891 :: Size=1295
>> Thanks,
>> Prasad
>>
>> On Fri, Dec 28, 2018 at 8:32 PM Ilya Kasnacheev <
>> ilya.kasnacheev@gmail.com> wrote:
>>
>>> Hello!
>>>
>>> I have created a PR for you:
>>> https://github.com/prasadbhalerao1983/IgniteTestPrj/pull/1
>>> With it, I can see:
>>>
>>> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>>> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
>>> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
>>> getAffectedIPRange_3 :: TimeTakenToComplete=11 :: Size=1295
>>>
>>> Regards,
>>> --
>>> Ilya Kasnacheev
>>>
>>>
>>> пт, 28 дек. 2018 г. в 17:46, Prasad Bhalerao <
>>> prasadbhalerao1983@gmail.com>:
>>>
>>>> Can someone from community help me with t
>>>>
>>>> I have created a reproducer and uploaded it to GitHub. I have created
>>>> 3 cases to test the sql execution time.
>>>>
>>>> Please run *IgniteQueryTester_4* class to check the issue.
>>>> GitHub project: https://github.com/prasadbhalerao1983/IgniteTestPrj.git
>>>>
>>>> Thanks,
>>>> Prasad
>>>>
>>>>
>>>> On Wed, Dec 26, 2018 at 11:18 PM Prasad Bhalerao <
>>>> prasadbhalerao1983@gmail.com> wrote:
>>>>
>>>>> How to push the ids to temp table, can you please give any example?
>>>>> Is it a in memory temp table created by ignite?
>>>>>
>>>>> Can you please explain how enforceJoinOrder will help in this case?
>>>>> Thanks ,
>>>>> Prasad
>>>>>
>>>>> On Wed 26 Dec, 2018, 9:37 PM Ilya Kasnacheev <
>>>>> ilya.kasnacheev@gmail.com wrote:
>>>>>
>>>>>> Hello!
>>>>>>
>>>>>> Can you try pushing temp table to 1st position and setting
>>>>>> enforceJoinOrder=true?
>>>>>>
>>>>>> SELECT ipv4agd.id,
>>>>>> ipv4agd.assetGroupId,
>>>>>> ipv4agd.ipStart,
>>>>>> ipv4agd.ipEnd
>>>>>> FROM TABLE (assetGroupId bigint = ? ) temp
>>>>>> JOIN IpV4AssetGroupData ipv4agd
>>>>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>>>>> WHERE subscriptionId = ?
>>>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>>>> ORDER BY ipv4agd.assetGroupId
>>>>>>
>>>>>> See https://apacheignite.readme.io/docs/configuration-parameters for
>>>>>> enforceJoinOrder.
>>>>>>
>>>>>> Regards,
>>>>>> --
>>>>>> Ilya Kasnacheev
>>>>>>
>>>>>>
>>>>>> ср, 26 дек. 2018 г. в 19:01, Prasad Bhalerao <
>>>>>> prasadbhalerao1983@gmail.com>:
>>>>>>
>>>>>>> I am executing following SQL on ignite cache. This cache has 37
>>>>>>> million records and this data is distributed across 4 nodes.
>>>>>>> *SQL:*
>>>>>>> SELECT ipv4agd.id,
>>>>>>> ipv4agd.assetGroupId,
>>>>>>> ipv4agd.ipStart,
>>>>>>> ipv4agd.ipEnd
>>>>>>> FROM IpV4AssetGroupData ipv4agd
>>>>>>> JOIN TABLE (assetGroupId bigint = ? ) temp
>>>>>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>>>>>> WHERE subscriptionId = ?
>>>>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>>>>> ORDER BY ipv4agd.assetGroupId
>>>>>>>
>>>>>>>
>>>>>>> As per the execution plan show below, ignite is using index
>>>>>>> "IPV4_ASSET_GROUP_DATA_IDX2 " and execution plan attached below.
>>>>>>>
>>>>>>> This sql is taking around 23 seconds. I have set the max index
>>>>>>> inline size as 65 bytes.
>>>>>>> The number of assetGroupIds set in join clause are 50.
>>>>>>>
>>>>>>> *Is there anything I can do to improve the performance of this SQL?*
>>>>>>>
>>>>>>> I have checked the memory and cpu utilization and it it is very low.
>>>>>>> I also tried to profile it using jprofiler to find out the issue, but could
>>>>>>> not find solution. I have also attached profiler snapshot at the end.
>>>>>>> Please check.
>>>>>>>
>>>>>>>
>>>>>>> .
>>>>>>>
>>>>>>> *Indexes:*
>>>>>>>
>>>>>>> public class IpV4AssetGroupData implements UpdatableData<DefaultDataAffinityKey> {
>>>>>>>
>>>>>>> @QuerySqlField
>>>>>>> private long id;
>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 2)})
>>>>>>> private long assetGroupId;
>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 1),
>>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 1)})
>>>>>>> private long subscriptionId;
>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 2),
>>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 3)})
>>>>>>> private int ipStart;
>>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 3),
>>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 4)})
>>>>>>> private int ipEnd;
>>>>>>>
>>>>>>> }
>>>>>>>
>>>>>>> *Execution plan:*
>>>>>>>
>>>>>>> Query execution is too long [time=15788 ms, sql='SELECT
>>>>>>> IPV4AGD__Z0.ID __C0_0,
>>>>>>> IPV4AGD__Z0.ASSETGROUPID __C0_1,
>>>>>>> IPV4AGD__Z0.IPSTART __C0_2,
>>>>>>> IPV4AGD__Z0.IPEND __C0_3
>>>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>>>> ON TRUE
>>>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID) AND
>>>>>>> ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) AND ((IPV4AGD__Z0.IPSTART <= ?3) AND
>>>>>>> (IPV4AGD__Z0.IPEND >= ?4)))
>>>>>>> ORDER BY 2',
>>>>>>>
>>>>>>> plan=
>>>>>>>
>>>>>>> SELECT
>>>>>>> IPV4AGD__Z0.ID AS __C0_0,
>>>>>>> IPV4AGD__Z0.ASSETGROUPID AS __C0_1,
>>>>>>> IPV4AGD__Z0.IPSTART AS __C0_2,
>>>>>>> IPV4AGD__Z0.IPEND AS __C0_3
>>>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>>>> /* IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4_ASSET_GROUP_DATA_IDX2:
>>>>>>> SUBSCRIPTIONID = ?2
>>>>>>> AND IPSTART <= ?3
>>>>>>> AND IPEND >= ?4
>>>>>>> */
>>>>>>> /* WHERE (IPV4AGD__Z0.IPEND >= ?4)
>>>>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>>>> AND (IPV4AGD__Z0.IPSTART <= ?3))
>>>>>>> */
>>>>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>>>> /* function: ASSETGROUPID = IPV4AGD__Z0.ASSETGROUPID */
>>>>>>> ON 1=1
>>>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID)
>>>>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>>>> AND ((IPV4AGD__Z0.IPSTART <= ?3)
>>>>>>> AND (IPV4AGD__Z0.IPEND >= ?4)))
>>>>>>> ORDER BY 2
>>>>>>> , parameters=[[3483555, 3180458, 3250090, 3483563, 3182509, 3213230,
>>>>>>> 3245998, 3487661, 3215281, 3444657, 3182515, 3372974, 3483573, 3372981,
>>>>>>> 3200951, 3485624, 3295161, 3485626, 3379125, 3211196, 3213242, 3381181,
>>>>>>> 3194805, 3213247, 3258299, 3379123, 3377070, 3315637, 3352502, 3295174,
>>>>>>> 3485618, 3438530, 3483592, 3352516, 3155914, 3424204, 3192775, 3485643,
>>>>>>> 3317711, 3246026, 3209159, 3485584, 3485645, 3483594, 3248085, 3321799,
>>>>>>> 3248086, 3190744, 3211222, 3379162], 164307, 1084754675, -2094919442]]
>>>>>>>
>>>>>>>
>>>>>>> Profiler snapshot:
>>>>>>>
>>>>>>> [image: image.png]
>>>>>>>
>>>>>>
Re: Query Execution very slow
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
Did you set enforceJoinOrder to true?
Regards,
--
Ilya Kasnacheev
пт, 28 дек. 2018 г. в 18:19, Prasad Bhalerao <pr...@gmail.com>:
> Hi,
>
> I tried your suggestion but it did not work. It is taking 22.8 seconds.
>
> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
>
> getAffectedIPRange_3 :: TimeTakenToComplete=22891 :: Size=1295
> Thanks,
> Prasad
>
> On Fri, Dec 28, 2018 at 8:32 PM Ilya Kasnacheev <il...@gmail.com>
> wrote:
>
>> Hello!
>>
>> I have created a PR for you:
>> https://github.com/prasadbhalerao1983/IgniteTestPrj/pull/1
>> With it, I can see:
>>
>> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
>> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
>> getAffectedIPRange_3 :: TimeTakenToComplete=11 :: Size=1295
>>
>> Regards,
>> --
>> Ilya Kasnacheev
>>
>>
>> пт, 28 дек. 2018 г. в 17:46, Prasad Bhalerao <
>> prasadbhalerao1983@gmail.com>:
>>
>>> Can someone from community help me with t
>>>
>>> I have created a reproducer and uploaded it to GitHub. I have created 3
>>> cases to test the sql execution time.
>>>
>>> Please run *IgniteQueryTester_4* class to check the issue.
>>> GitHub project: https://github.com/prasadbhalerao1983/IgniteTestPrj.git
>>>
>>> Thanks,
>>> Prasad
>>>
>>>
>>> On Wed, Dec 26, 2018 at 11:18 PM Prasad Bhalerao <
>>> prasadbhalerao1983@gmail.com> wrote:
>>>
>>>> How to push the ids to temp table, can you please give any example?
>>>> Is it a in memory temp table created by ignite?
>>>>
>>>> Can you please explain how enforceJoinOrder will help in this case?
>>>> Thanks ,
>>>> Prasad
>>>>
>>>> On Wed 26 Dec, 2018, 9:37 PM Ilya Kasnacheev <ilya.kasnacheev@gmail.com
>>>> wrote:
>>>>
>>>>> Hello!
>>>>>
>>>>> Can you try pushing temp table to 1st position and setting
>>>>> enforceJoinOrder=true?
>>>>>
>>>>> SELECT ipv4agd.id,
>>>>> ipv4agd.assetGroupId,
>>>>> ipv4agd.ipStart,
>>>>> ipv4agd.ipEnd
>>>>> FROM TABLE (assetGroupId bigint = ? ) temp
>>>>> JOIN IpV4AssetGroupData ipv4agd
>>>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>>>> WHERE subscriptionId = ?
>>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>>> ORDER BY ipv4agd.assetGroupId
>>>>>
>>>>> See https://apacheignite.readme.io/docs/configuration-parameters for
>>>>> enforceJoinOrder.
>>>>>
>>>>> Regards,
>>>>> --
>>>>> Ilya Kasnacheev
>>>>>
>>>>>
>>>>> ср, 26 дек. 2018 г. в 19:01, Prasad Bhalerao <
>>>>> prasadbhalerao1983@gmail.com>:
>>>>>
>>>>>> I am executing following SQL on ignite cache. This cache has 37
>>>>>> million records and this data is distributed across 4 nodes.
>>>>>> *SQL:*
>>>>>> SELECT ipv4agd.id,
>>>>>> ipv4agd.assetGroupId,
>>>>>> ipv4agd.ipStart,
>>>>>> ipv4agd.ipEnd
>>>>>> FROM IpV4AssetGroupData ipv4agd
>>>>>> JOIN TABLE (assetGroupId bigint = ? ) temp
>>>>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>>>>> WHERE subscriptionId = ?
>>>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>>>> ORDER BY ipv4agd.assetGroupId
>>>>>>
>>>>>>
>>>>>> As per the execution plan show below, ignite is using index
>>>>>> "IPV4_ASSET_GROUP_DATA_IDX2 " and execution plan attached below.
>>>>>>
>>>>>> This sql is taking around 23 seconds. I have set the max index inline
>>>>>> size as 65 bytes.
>>>>>> The number of assetGroupIds set in join clause are 50.
>>>>>>
>>>>>> *Is there anything I can do to improve the performance of this SQL?*
>>>>>>
>>>>>> I have checked the memory and cpu utilization and it it is very low.
>>>>>> I also tried to profile it using jprofiler to find out the issue, but could
>>>>>> not find solution. I have also attached profiler snapshot at the end.
>>>>>> Please check.
>>>>>>
>>>>>>
>>>>>> .
>>>>>>
>>>>>> *Indexes:*
>>>>>>
>>>>>> public class IpV4AssetGroupData implements UpdatableData<DefaultDataAffinityKey> {
>>>>>>
>>>>>> @QuerySqlField
>>>>>> private long id;
>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 2)})
>>>>>> private long assetGroupId;
>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 1),
>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 1)})
>>>>>> private long subscriptionId;
>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 2),
>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 3)})
>>>>>> private int ipStart;
>>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 3),
>>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 4)})
>>>>>> private int ipEnd;
>>>>>>
>>>>>> }
>>>>>>
>>>>>> *Execution plan:*
>>>>>>
>>>>>> Query execution is too long [time=15788 ms, sql='SELECT
>>>>>> IPV4AGD__Z0.ID __C0_0,
>>>>>> IPV4AGD__Z0.ASSETGROUPID __C0_1,
>>>>>> IPV4AGD__Z0.IPSTART __C0_2,
>>>>>> IPV4AGD__Z0.IPEND __C0_3
>>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>>> ON TRUE
>>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID) AND
>>>>>> ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) AND ((IPV4AGD__Z0.IPSTART <= ?3) AND
>>>>>> (IPV4AGD__Z0.IPEND >= ?4)))
>>>>>> ORDER BY 2',
>>>>>>
>>>>>> plan=
>>>>>>
>>>>>> SELECT
>>>>>> IPV4AGD__Z0.ID AS __C0_0,
>>>>>> IPV4AGD__Z0.ASSETGROUPID AS __C0_1,
>>>>>> IPV4AGD__Z0.IPSTART AS __C0_2,
>>>>>> IPV4AGD__Z0.IPEND AS __C0_3
>>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>>> /* IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4_ASSET_GROUP_DATA_IDX2:
>>>>>> SUBSCRIPTIONID = ?2
>>>>>> AND IPSTART <= ?3
>>>>>> AND IPEND >= ?4
>>>>>> */
>>>>>> /* WHERE (IPV4AGD__Z0.IPEND >= ?4)
>>>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>>> AND (IPV4AGD__Z0.IPSTART <= ?3))
>>>>>> */
>>>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>>> /* function: ASSETGROUPID = IPV4AGD__Z0.ASSETGROUPID */
>>>>>> ON 1=1
>>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID)
>>>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>>> AND ((IPV4AGD__Z0.IPSTART <= ?3)
>>>>>> AND (IPV4AGD__Z0.IPEND >= ?4)))
>>>>>> ORDER BY 2
>>>>>> , parameters=[[3483555, 3180458, 3250090, 3483563, 3182509, 3213230,
>>>>>> 3245998, 3487661, 3215281, 3444657, 3182515, 3372974, 3483573, 3372981,
>>>>>> 3200951, 3485624, 3295161, 3485626, 3379125, 3211196, 3213242, 3381181,
>>>>>> 3194805, 3213247, 3258299, 3379123, 3377070, 3315637, 3352502, 3295174,
>>>>>> 3485618, 3438530, 3483592, 3352516, 3155914, 3424204, 3192775, 3485643,
>>>>>> 3317711, 3246026, 3209159, 3485584, 3485645, 3483594, 3248085, 3321799,
>>>>>> 3248086, 3190744, 3211222, 3379162], 164307, 1084754675, -2094919442]]
>>>>>>
>>>>>>
>>>>>> Profiler snapshot:
>>>>>>
>>>>>> [image: image.png]
>>>>>>
>>>>>
Re: Query Execution very slow
Posted by Prasad Bhalerao <pr...@gmail.com>.
Hi,
I tried your suggestion but it did not work. It is taking 22.8 seconds.
getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
getAffectedIPRange_3 :: TimeTakenToComplete=22891 :: Size=1295
Thanks,
Prasad
On Fri, Dec 28, 2018 at 8:32 PM Ilya Kasnacheev <il...@gmail.com>
wrote:
> Hello!
>
> I have created a PR for you:
> https://github.com/prasadbhalerao1983/IgniteTestPrj/pull/1
> With it, I can see:
>
> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
> getAffectedIPRange_3 :: TimeTakenToComplete=11 :: Size=1295
>
> Regards,
> --
> Ilya Kasnacheev
>
>
> пт, 28 дек. 2018 г. в 17:46, Prasad Bhalerao <prasadbhalerao1983@gmail.com
> >:
>
>> Can someone from community help me with t
>>
>> I have created a reproducer and uploaded it to GitHub. I have created 3
>> cases to test the sql execution time.
>>
>> Please run *IgniteQueryTester_4* class to check the issue.
>> GitHub project: https://github.com/prasadbhalerao1983/IgniteTestPrj.git
>>
>> Thanks,
>> Prasad
>>
>>
>> On Wed, Dec 26, 2018 at 11:18 PM Prasad Bhalerao <
>> prasadbhalerao1983@gmail.com> wrote:
>>
>>> How to push the ids to temp table, can you please give any example?
>>> Is it a in memory temp table created by ignite?
>>>
>>> Can you please explain how enforceJoinOrder will help in this case?
>>> Thanks ,
>>> Prasad
>>>
>>> On Wed 26 Dec, 2018, 9:37 PM Ilya Kasnacheev <ilya.kasnacheev@gmail.com
>>> wrote:
>>>
>>>> Hello!
>>>>
>>>> Can you try pushing temp table to 1st position and setting
>>>> enforceJoinOrder=true?
>>>>
>>>> SELECT ipv4agd.id,
>>>> ipv4agd.assetGroupId,
>>>> ipv4agd.ipStart,
>>>> ipv4agd.ipEnd
>>>> FROM TABLE (assetGroupId bigint = ? ) temp
>>>> JOIN IpV4AssetGroupData ipv4agd
>>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>>> WHERE subscriptionId = ?
>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>> ORDER BY ipv4agd.assetGroupId
>>>>
>>>> See https://apacheignite.readme.io/docs/configuration-parameters for
>>>> enforceJoinOrder.
>>>>
>>>> Regards,
>>>> --
>>>> Ilya Kasnacheev
>>>>
>>>>
>>>> ср, 26 дек. 2018 г. в 19:01, Prasad Bhalerao <
>>>> prasadbhalerao1983@gmail.com>:
>>>>
>>>>> I am executing following SQL on ignite cache. This cache has 37
>>>>> million records and this data is distributed across 4 nodes.
>>>>> *SQL:*
>>>>> SELECT ipv4agd.id,
>>>>> ipv4agd.assetGroupId,
>>>>> ipv4agd.ipStart,
>>>>> ipv4agd.ipEnd
>>>>> FROM IpV4AssetGroupData ipv4agd
>>>>> JOIN TABLE (assetGroupId bigint = ? ) temp
>>>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>>>> WHERE subscriptionId = ?
>>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>>> ORDER BY ipv4agd.assetGroupId
>>>>>
>>>>>
>>>>> As per the execution plan show below, ignite is using index
>>>>> "IPV4_ASSET_GROUP_DATA_IDX2 " and execution plan attached below.
>>>>>
>>>>> This sql is taking around 23 seconds. I have set the max index inline
>>>>> size as 65 bytes.
>>>>> The number of assetGroupIds set in join clause are 50.
>>>>>
>>>>> *Is there anything I can do to improve the performance of this SQL?*
>>>>>
>>>>> I have checked the memory and cpu utilization and it it is very low. I
>>>>> also tried to profile it using jprofiler to find out the issue, but could
>>>>> not find solution. I have also attached profiler snapshot at the end.
>>>>> Please check.
>>>>>
>>>>>
>>>>> .
>>>>>
>>>>> *Indexes:*
>>>>>
>>>>> public class IpV4AssetGroupData implements UpdatableData<DefaultDataAffinityKey> {
>>>>>
>>>>> @QuerySqlField
>>>>> private long id;
>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 2)})
>>>>> private long assetGroupId;
>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 1),
>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 1)})
>>>>> private long subscriptionId;
>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 2),
>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 3)})
>>>>> private int ipStart;
>>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 3),
>>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 4)})
>>>>> private int ipEnd;
>>>>>
>>>>> }
>>>>>
>>>>> *Execution plan:*
>>>>>
>>>>> Query execution is too long [time=15788 ms, sql='SELECT
>>>>> IPV4AGD__Z0.ID __C0_0,
>>>>> IPV4AGD__Z0.ASSETGROUPID __C0_1,
>>>>> IPV4AGD__Z0.IPSTART __C0_2,
>>>>> IPV4AGD__Z0.IPEND __C0_3
>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>> ON TRUE
>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID) AND
>>>>> ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) AND ((IPV4AGD__Z0.IPSTART <= ?3) AND
>>>>> (IPV4AGD__Z0.IPEND >= ?4)))
>>>>> ORDER BY 2',
>>>>>
>>>>> plan=
>>>>>
>>>>> SELECT
>>>>> IPV4AGD__Z0.ID AS __C0_0,
>>>>> IPV4AGD__Z0.ASSETGROUPID AS __C0_1,
>>>>> IPV4AGD__Z0.IPSTART AS __C0_2,
>>>>> IPV4AGD__Z0.IPEND AS __C0_3
>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>> /* IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4_ASSET_GROUP_DATA_IDX2:
>>>>> SUBSCRIPTIONID = ?2
>>>>> AND IPSTART <= ?3
>>>>> AND IPEND >= ?4
>>>>> */
>>>>> /* WHERE (IPV4AGD__Z0.IPEND >= ?4)
>>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>> AND (IPV4AGD__Z0.IPSTART <= ?3))
>>>>> */
>>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>> /* function: ASSETGROUPID = IPV4AGD__Z0.ASSETGROUPID */
>>>>> ON 1=1
>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID)
>>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>> AND ((IPV4AGD__Z0.IPSTART <= ?3)
>>>>> AND (IPV4AGD__Z0.IPEND >= ?4)))
>>>>> ORDER BY 2
>>>>> , parameters=[[3483555, 3180458, 3250090, 3483563, 3182509, 3213230,
>>>>> 3245998, 3487661, 3215281, 3444657, 3182515, 3372974, 3483573, 3372981,
>>>>> 3200951, 3485624, 3295161, 3485626, 3379125, 3211196, 3213242, 3381181,
>>>>> 3194805, 3213247, 3258299, 3379123, 3377070, 3315637, 3352502, 3295174,
>>>>> 3485618, 3438530, 3483592, 3352516, 3155914, 3424204, 3192775, 3485643,
>>>>> 3317711, 3246026, 3209159, 3485584, 3485645, 3483594, 3248085, 3321799,
>>>>> 3248086, 3190744, 3211222, 3379162], 164307, 1084754675, -2094919442]]
>>>>>
>>>>>
>>>>> Profiler snapshot:
>>>>>
>>>>> [image: image.png]
>>>>>
>>>>
Re: Query Execution very slow
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
I have created a PR for you:
https://github.com/prasadbhalerao1983/IgniteTestPrj/pull/1
With it, I can see:
getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
getAffectedIPRange_3 :: TimeTakenToComplete=11 :: Size=1295
Regards,
--
Ilya Kasnacheev
пт, 28 дек. 2018 г. в 17:46, Prasad Bhalerao <pr...@gmail.com>:
> Can someone from community help me with t
>
> I have created a reproducer and uploaded it to GitHub. I have created 3
> cases to test the sql execution time.
>
> Please run *IgniteQueryTester_4* class to check the issue.
> GitHub project: https://github.com/prasadbhalerao1983/IgniteTestPrj.git
>
> Thanks,
> Prasad
>
>
> On Wed, Dec 26, 2018 at 11:18 PM Prasad Bhalerao <
> prasadbhalerao1983@gmail.com> wrote:
>
>> How to push the ids to temp table, can you please give any example?
>> Is it a in memory temp table created by ignite?
>>
>> Can you please explain how enforceJoinOrder will help in this case?
>> Thanks ,
>> Prasad
>>
>> On Wed 26 Dec, 2018, 9:37 PM Ilya Kasnacheev <ilya.kasnacheev@gmail.com
>> wrote:
>>
>>> Hello!
>>>
>>> Can you try pushing temp table to 1st position and setting
>>> enforceJoinOrder=true?
>>>
>>> SELECT ipv4agd.id,
>>> ipv4agd.assetGroupId,
>>> ipv4agd.ipStart,
>>> ipv4agd.ipEnd
>>> FROM TABLE (assetGroupId bigint = ? ) temp
>>> JOIN IpV4AssetGroupData ipv4agd
>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>> WHERE subscriptionId = ?
>>> AND (ipStart <= ? AND ipEnd >= ?)
>>> ORDER BY ipv4agd.assetGroupId
>>>
>>> See https://apacheignite.readme.io/docs/configuration-parameters for
>>> enforceJoinOrder.
>>>
>>> Regards,
>>> --
>>> Ilya Kasnacheev
>>>
>>>
>>> ср, 26 дек. 2018 г. в 19:01, Prasad Bhalerao <
>>> prasadbhalerao1983@gmail.com>:
>>>
>>>> I am executing following SQL on ignite cache. This cache has 37 million
>>>> records and this data is distributed across 4 nodes.
>>>> *SQL:*
>>>> SELECT ipv4agd.id,
>>>> ipv4agd.assetGroupId,
>>>> ipv4agd.ipStart,
>>>> ipv4agd.ipEnd
>>>> FROM IpV4AssetGroupData ipv4agd
>>>> JOIN TABLE (assetGroupId bigint = ? ) temp
>>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>>> WHERE subscriptionId = ?
>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>> ORDER BY ipv4agd.assetGroupId
>>>>
>>>>
>>>> As per the execution plan show below, ignite is using index
>>>> "IPV4_ASSET_GROUP_DATA_IDX2 " and execution plan attached below.
>>>>
>>>> This sql is taking around 23 seconds. I have set the max index inline
>>>> size as 65 bytes.
>>>> The number of assetGroupIds set in join clause are 50.
>>>>
>>>> *Is there anything I can do to improve the performance of this SQL?*
>>>>
>>>> I have checked the memory and cpu utilization and it it is very low. I
>>>> also tried to profile it using jprofiler to find out the issue, but could
>>>> not find solution. I have also attached profiler snapshot at the end.
>>>> Please check.
>>>>
>>>>
>>>> .
>>>>
>>>> *Indexes:*
>>>>
>>>> public class IpV4AssetGroupData implements UpdatableData<DefaultDataAffinityKey> {
>>>>
>>>> @QuerySqlField
>>>> private long id;
>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 2)})
>>>> private long assetGroupId;
>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 1),
>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 1)})
>>>> private long subscriptionId;
>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 2),
>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 3)})
>>>> private int ipStart;
>>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 3),
>>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 4)})
>>>> private int ipEnd;
>>>>
>>>> }
>>>>
>>>> *Execution plan:*
>>>>
>>>> Query execution is too long [time=15788 ms, sql='SELECT
>>>> IPV4AGD__Z0.ID __C0_0,
>>>> IPV4AGD__Z0.ASSETGROUPID __C0_1,
>>>> IPV4AGD__Z0.IPSTART __C0_2,
>>>> IPV4AGD__Z0.IPEND __C0_3
>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>> ON TRUE
>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID) AND
>>>> ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) AND ((IPV4AGD__Z0.IPSTART <= ?3) AND
>>>> (IPV4AGD__Z0.IPEND >= ?4)))
>>>> ORDER BY 2',
>>>>
>>>> plan=
>>>>
>>>> SELECT
>>>> IPV4AGD__Z0.ID AS __C0_0,
>>>> IPV4AGD__Z0.ASSETGROUPID AS __C0_1,
>>>> IPV4AGD__Z0.IPSTART AS __C0_2,
>>>> IPV4AGD__Z0.IPEND AS __C0_3
>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>> /* IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4_ASSET_GROUP_DATA_IDX2:
>>>> SUBSCRIPTIONID = ?2
>>>> AND IPSTART <= ?3
>>>> AND IPEND >= ?4
>>>> */
>>>> /* WHERE (IPV4AGD__Z0.IPEND >= ?4)
>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>> AND (IPV4AGD__Z0.IPSTART <= ?3))
>>>> */
>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>> /* function: ASSETGROUPID = IPV4AGD__Z0.ASSETGROUPID */
>>>> ON 1=1
>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID)
>>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>> AND ((IPV4AGD__Z0.IPSTART <= ?3)
>>>> AND (IPV4AGD__Z0.IPEND >= ?4)))
>>>> ORDER BY 2
>>>> , parameters=[[3483555, 3180458, 3250090, 3483563, 3182509, 3213230,
>>>> 3245998, 3487661, 3215281, 3444657, 3182515, 3372974, 3483573, 3372981,
>>>> 3200951, 3485624, 3295161, 3485626, 3379125, 3211196, 3213242, 3381181,
>>>> 3194805, 3213247, 3258299, 3379123, 3377070, 3315637, 3352502, 3295174,
>>>> 3485618, 3438530, 3483592, 3352516, 3155914, 3424204, 3192775, 3485643,
>>>> 3317711, 3246026, 3209159, 3485584, 3485645, 3483594, 3248085, 3321799,
>>>> 3248086, 3190744, 3211222, 3379162], 164307, 1084754675, -2094919442]]
>>>>
>>>>
>>>> Profiler snapshot:
>>>>
>>>> [image: image.png]
>>>>
>>>
Re: Query Execution very slow
Posted by Prasad Bhalerao <pr...@gmail.com>.
Can someone from community help me with t
I have created a reproducer and uploaded it to GitHub. I have created 3
cases to test the sql execution time.
Please run *IgniteQueryTester_4* class to check the issue.
GitHub project: https://github.com/prasadbhalerao1983/IgniteTestPrj.git
Thanks,
Prasad
On Wed, Dec 26, 2018 at 11:18 PM Prasad Bhalerao <
prasadbhalerao1983@gmail.com> wrote:
> How to push the ids to temp table, can you please give any example?
> Is it a in memory temp table created by ignite?
>
> Can you please explain how enforceJoinOrder will help in this case?
> Thanks ,
> Prasad
>
> On Wed 26 Dec, 2018, 9:37 PM Ilya Kasnacheev <ilya.kasnacheev@gmail.com
> wrote:
>
>> Hello!
>>
>> Can you try pushing temp table to 1st position and setting
>> enforceJoinOrder=true?
>>
>> SELECT ipv4agd.id,
>> ipv4agd.assetGroupId,
>> ipv4agd.ipStart,
>> ipv4agd.ipEnd
>> FROM TABLE (assetGroupId bigint = ? ) temp
>> JOIN IpV4AssetGroupData ipv4agd
>> ON ipv4agd.assetGroupId = temp.assetGroupId
>> WHERE subscriptionId = ?
>> AND (ipStart <= ? AND ipEnd >= ?)
>> ORDER BY ipv4agd.assetGroupId
>>
>> See https://apacheignite.readme.io/docs/configuration-parameters for
>> enforceJoinOrder.
>>
>> Regards,
>> --
>> Ilya Kasnacheev
>>
>>
>> ср, 26 дек. 2018 г. в 19:01, Prasad Bhalerao <
>> prasadbhalerao1983@gmail.com>:
>>
>>> I am executing following SQL on ignite cache. This cache has 37 million
>>> records and this data is distributed across 4 nodes.
>>> *SQL:*
>>> SELECT ipv4agd.id,
>>> ipv4agd.assetGroupId,
>>> ipv4agd.ipStart,
>>> ipv4agd.ipEnd
>>> FROM IpV4AssetGroupData ipv4agd
>>> JOIN TABLE (assetGroupId bigint = ? ) temp
>>> ON ipv4agd.assetGroupId = temp.assetGroupId
>>> WHERE subscriptionId = ?
>>> AND (ipStart <= ? AND ipEnd >= ?)
>>> ORDER BY ipv4agd.assetGroupId
>>>
>>>
>>> As per the execution plan show below, ignite is using index
>>> "IPV4_ASSET_GROUP_DATA_IDX2 " and execution plan attached below.
>>>
>>> This sql is taking around 23 seconds. I have set the max index inline
>>> size as 65 bytes.
>>> The number of assetGroupIds set in join clause are 50.
>>>
>>> *Is there anything I can do to improve the performance of this SQL?*
>>>
>>> I have checked the memory and cpu utilization and it it is very low. I
>>> also tried to profile it using jprofiler to find out the issue, but could
>>> not find solution. I have also attached profiler snapshot at the end.
>>> Please check.
>>>
>>>
>>> .
>>>
>>> *Indexes:*
>>>
>>> public class IpV4AssetGroupData implements UpdatableData<DefaultDataAffinityKey> {
>>>
>>> @QuerySqlField
>>> private long id;
>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 2)})
>>> private long assetGroupId;
>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 1),
>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 1)})
>>> private long subscriptionId;
>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 2),
>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 3)})
>>> private int ipStart;
>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 3),
>>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 4)})
>>> private int ipEnd;
>>>
>>> }
>>>
>>> *Execution plan:*
>>>
>>> Query execution is too long [time=15788 ms, sql='SELECT
>>> IPV4AGD__Z0.ID __C0_0,
>>> IPV4AGD__Z0.ASSETGROUPID __C0_1,
>>> IPV4AGD__Z0.IPSTART __C0_2,
>>> IPV4AGD__Z0.IPEND __C0_3
>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>> ON TRUE
>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID) AND
>>> ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) AND ((IPV4AGD__Z0.IPSTART <= ?3) AND
>>> (IPV4AGD__Z0.IPEND >= ?4)))
>>> ORDER BY 2',
>>>
>>> plan=
>>>
>>> SELECT
>>> IPV4AGD__Z0.ID AS __C0_0,
>>> IPV4AGD__Z0.ASSETGROUPID AS __C0_1,
>>> IPV4AGD__Z0.IPSTART AS __C0_2,
>>> IPV4AGD__Z0.IPEND AS __C0_3
>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>> /* IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4_ASSET_GROUP_DATA_IDX2:
>>> SUBSCRIPTIONID = ?2
>>> AND IPSTART <= ?3
>>> AND IPEND >= ?4
>>> */
>>> /* WHERE (IPV4AGD__Z0.IPEND >= ?4)
>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>> AND (IPV4AGD__Z0.IPSTART <= ?3))
>>> */
>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>> /* function: ASSETGROUPID = IPV4AGD__Z0.ASSETGROUPID */
>>> ON 1=1
>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID)
>>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>> AND ((IPV4AGD__Z0.IPSTART <= ?3)
>>> AND (IPV4AGD__Z0.IPEND >= ?4)))
>>> ORDER BY 2
>>> , parameters=[[3483555, 3180458, 3250090, 3483563, 3182509, 3213230,
>>> 3245998, 3487661, 3215281, 3444657, 3182515, 3372974, 3483573, 3372981,
>>> 3200951, 3485624, 3295161, 3485626, 3379125, 3211196, 3213242, 3381181,
>>> 3194805, 3213247, 3258299, 3379123, 3377070, 3315637, 3352502, 3295174,
>>> 3485618, 3438530, 3483592, 3352516, 3155914, 3424204, 3192775, 3485643,
>>> 3317711, 3246026, 3209159, 3485584, 3485645, 3483594, 3248085, 3321799,
>>> 3248086, 3190744, 3211222, 3379162], 164307, 1084754675, -2094919442]]
>>>
>>>
>>> Profiler snapshot:
>>>
>>> [image: image.png]
>>>
>>
Re: Query Execution very slow
Posted by Prasad Bhalerao <pr...@gmail.com>.
How to push the ids to temp table, can you please give any example?
Is it a in memory temp table created by ignite?
Can you please explain how enforceJoinOrder will help in this case?
Thanks ,
Prasad
On Wed 26 Dec, 2018, 9:37 PM Ilya Kasnacheev <ilya.kasnacheev@gmail.com
wrote:
> Hello!
>
> Can you try pushing temp table to 1st position and setting
> enforceJoinOrder=true?
>
> SELECT ipv4agd.id,
> ipv4agd.assetGroupId,
> ipv4agd.ipStart,
> ipv4agd.ipEnd
> FROM TABLE (assetGroupId bigint = ? ) temp
> JOIN IpV4AssetGroupData ipv4agd
> ON ipv4agd.assetGroupId = temp.assetGroupId
> WHERE subscriptionId = ?
> AND (ipStart <= ? AND ipEnd >= ?)
> ORDER BY ipv4agd.assetGroupId
>
> See https://apacheignite.readme.io/docs/configuration-parameters for
> enforceJoinOrder.
>
> Regards,
> --
> Ilya Kasnacheev
>
>
> ср, 26 дек. 2018 г. в 19:01, Prasad Bhalerao <prasadbhalerao1983@gmail.com
> >:
>
>> I am executing following SQL on ignite cache. This cache has 37 million
>> records and this data is distributed across 4 nodes.
>> *SQL:*
>> SELECT ipv4agd.id,
>> ipv4agd.assetGroupId,
>> ipv4agd.ipStart,
>> ipv4agd.ipEnd
>> FROM IpV4AssetGroupData ipv4agd
>> JOIN TABLE (assetGroupId bigint = ? ) temp
>> ON ipv4agd.assetGroupId = temp.assetGroupId
>> WHERE subscriptionId = ?
>> AND (ipStart <= ? AND ipEnd >= ?)
>> ORDER BY ipv4agd.assetGroupId
>>
>>
>> As per the execution plan show below, ignite is using index
>> "IPV4_ASSET_GROUP_DATA_IDX2 " and execution plan attached below.
>>
>> This sql is taking around 23 seconds. I have set the max index inline
>> size as 65 bytes.
>> The number of assetGroupIds set in join clause are 50.
>>
>> *Is there anything I can do to improve the performance of this SQL?*
>>
>> I have checked the memory and cpu utilization and it it is very low. I
>> also tried to profile it using jprofiler to find out the issue, but could
>> not find solution. I have also attached profiler snapshot at the end.
>> Please check.
>>
>>
>> .
>>
>> *Indexes:*
>>
>> public class IpV4AssetGroupData implements UpdatableData<DefaultDataAffinityKey> {
>>
>> @QuerySqlField
>> private long id;
>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 2)})
>> private long assetGroupId;
>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 1),
>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 1)})
>> private long subscriptionId;
>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 2),
>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 3)})
>> private int ipStart;
>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 3),
>> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 4)})
>> private int ipEnd;
>>
>> }
>>
>> *Execution plan:*
>>
>> Query execution is too long [time=15788 ms, sql='SELECT
>> IPV4AGD__Z0.ID __C0_0,
>> IPV4AGD__Z0.ASSETGROUPID __C0_1,
>> IPV4AGD__Z0.IPSTART __C0_2,
>> IPV4AGD__Z0.IPEND __C0_3
>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>> ON TRUE
>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID) AND
>> ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) AND ((IPV4AGD__Z0.IPSTART <= ?3) AND
>> (IPV4AGD__Z0.IPEND >= ?4)))
>> ORDER BY 2',
>>
>> plan=
>>
>> SELECT
>> IPV4AGD__Z0.ID AS __C0_0,
>> IPV4AGD__Z0.ASSETGROUPID AS __C0_1,
>> IPV4AGD__Z0.IPSTART AS __C0_2,
>> IPV4AGD__Z0.IPEND AS __C0_3
>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>> /* IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4_ASSET_GROUP_DATA_IDX2:
>> SUBSCRIPTIONID = ?2
>> AND IPSTART <= ?3
>> AND IPEND >= ?4
>> */
>> /* WHERE (IPV4AGD__Z0.IPEND >= ?4)
>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>> AND (IPV4AGD__Z0.IPSTART <= ?3))
>> */
>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>> /* function: ASSETGROUPID = IPV4AGD__Z0.ASSETGROUPID */
>> ON 1=1
>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID)
>> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>> AND ((IPV4AGD__Z0.IPSTART <= ?3)
>> AND (IPV4AGD__Z0.IPEND >= ?4)))
>> ORDER BY 2
>> , parameters=[[3483555, 3180458, 3250090, 3483563, 3182509, 3213230,
>> 3245998, 3487661, 3215281, 3444657, 3182515, 3372974, 3483573, 3372981,
>> 3200951, 3485624, 3295161, 3485626, 3379125, 3211196, 3213242, 3381181,
>> 3194805, 3213247, 3258299, 3379123, 3377070, 3315637, 3352502, 3295174,
>> 3485618, 3438530, 3483592, 3352516, 3155914, 3424204, 3192775, 3485643,
>> 3317711, 3246026, 3209159, 3485584, 3485645, 3483594, 3248085, 3321799,
>> 3248086, 3190744, 3211222, 3379162], 164307, 1084754675, -2094919442]]
>>
>>
>> Profiler snapshot:
>>
>> [image: image.png]
>>
>
Re: Query Execution very slow
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
Can you try pushing temp table to 1st position and setting
enforceJoinOrder=true?
SELECT ipv4agd.id,
ipv4agd.assetGroupId,
ipv4agd.ipStart,
ipv4agd.ipEnd
FROM TABLE (assetGroupId bigint = ? ) temp
JOIN IpV4AssetGroupData ipv4agd
ON ipv4agd.assetGroupId = temp.assetGroupId
WHERE subscriptionId = ?
AND (ipStart <= ? AND ipEnd >= ?)
ORDER BY ipv4agd.assetGroupId
See https://apacheignite.readme.io/docs/configuration-parameters for
enforceJoinOrder.
Regards,
--
Ilya Kasnacheev
ср, 26 дек. 2018 г. в 19:01, Prasad Bhalerao <pr...@gmail.com>:
> I am executing following SQL on ignite cache. This cache has 37 million
> records and this data is distributed across 4 nodes.
> *SQL:*
> SELECT ipv4agd.id,
> ipv4agd.assetGroupId,
> ipv4agd.ipStart,
> ipv4agd.ipEnd
> FROM IpV4AssetGroupData ipv4agd
> JOIN TABLE (assetGroupId bigint = ? ) temp
> ON ipv4agd.assetGroupId = temp.assetGroupId
> WHERE subscriptionId = ?
> AND (ipStart <= ? AND ipEnd >= ?)
> ORDER BY ipv4agd.assetGroupId
>
>
> As per the execution plan show below, ignite is using index
> "IPV4_ASSET_GROUP_DATA_IDX2 " and execution plan attached below.
>
> This sql is taking around 23 seconds. I have set the max index inline size
> as 65 bytes.
> The number of assetGroupIds set in join clause are 50.
>
> *Is there anything I can do to improve the performance of this SQL?*
>
> I have checked the memory and cpu utilization and it it is very low. I
> also tried to profile it using jprofiler to find out the issue, but could
> not find solution. I have also attached profiler snapshot at the end.
> Please check.
>
>
> .
>
> *Indexes:*
>
> public class IpV4AssetGroupData implements UpdatableData<DefaultDataAffinityKey> {
>
> @QuerySqlField
> private long id;
> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 2)})
> private long assetGroupId;
> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 1),
> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 1)})
> private long subscriptionId;
> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 2),
> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 3)})
> private int ipStart;
> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "ipv4_asset_group_data_idx1", order = 3),
> @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2", order = 4)})
> private int ipEnd;
>
> }
>
> *Execution plan:*
>
> Query execution is too long [time=15788 ms, sql='SELECT
> IPV4AGD__Z0.ID __C0_0,
> IPV4AGD__Z0.ASSETGROUPID __C0_1,
> IPV4AGD__Z0.IPSTART __C0_2,
> IPV4AGD__Z0.IPEND __C0_3
> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
> ON TRUE
> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID) AND
> ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) AND ((IPV4AGD__Z0.IPSTART <= ?3) AND
> (IPV4AGD__Z0.IPEND >= ?4)))
> ORDER BY 2',
>
> plan=
>
> SELECT
> IPV4AGD__Z0.ID AS __C0_0,
> IPV4AGD__Z0.ASSETGROUPID AS __C0_1,
> IPV4AGD__Z0.IPSTART AS __C0_2,
> IPV4AGD__Z0.IPEND AS __C0_3
> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
> /* IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4_ASSET_GROUP_DATA_IDX2:
> SUBSCRIPTIONID = ?2
> AND IPSTART <= ?3
> AND IPEND >= ?4
> */
> /* WHERE (IPV4AGD__Z0.IPEND >= ?4)
> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
> AND (IPV4AGD__Z0.IPSTART <= ?3))
> */
> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
> /* function: ASSETGROUPID = IPV4AGD__Z0.ASSETGROUPID */
> ON 1=1
> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID)
> AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
> AND ((IPV4AGD__Z0.IPSTART <= ?3)
> AND (IPV4AGD__Z0.IPEND >= ?4)))
> ORDER BY 2
> , parameters=[[3483555, 3180458, 3250090, 3483563, 3182509, 3213230,
> 3245998, 3487661, 3215281, 3444657, 3182515, 3372974, 3483573, 3372981,
> 3200951, 3485624, 3295161, 3485626, 3379125, 3211196, 3213242, 3381181,
> 3194805, 3213247, 3258299, 3379123, 3377070, 3315637, 3352502, 3295174,
> 3485618, 3438530, 3483592, 3352516, 3155914, 3424204, 3192775, 3485643,
> 3317711, 3246026, 3209159, 3485584, 3485645, 3483594, 3248085, 3321799,
> 3248086, 3190744, 3211222, 3379162], 164307, 1084754675, -2094919442]]
>
>
> Profiler snapshot:
>
> [image: image.png]
>