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