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