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