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