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