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] >>>>>>>> >>>>>>>
