Hello! Did you set enforceJoinOrder to true?
Regards, -- Ilya Kasnacheev пт, 28 дек. 2018 г. в 18:19, Prasad Bhalerao <prasadbhalerao1...@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.kasnach...@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 < >> prasadbhalerao1...@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 < >>> prasadbhalerao1...@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.kasnach...@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 < >>>>> prasadbhalerao1...@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] >>>>>> >>>>>
