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 <ilya.kasnach...@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 <prasadbhalerao1...@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.kasnach...@gmail.com> wrote: >> >>> 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] >>>>>>>>> >>>>>>>>
