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