Hello!

There's a docs page at https://apacheignite.readme.io/docs/configuration-parameters
This is because Ignite's query planner sometimes plans joins unoptimally and you have to help it.

Regarding second sql, additional investigation is needed. Can you show EXPLAIN after you apply enforceJoinOrder?

Regards,
--
Ilya Kasnacheev


пт, 28 дек. 2018 г. в 18:59, Prasad Bhalerao <prasadbhalerao1983@gmail.com>:
Hi,

After setting enforceJoinOrder to true it worked. Can you please explain how did it work... pushing the data to temp table first and then enforeOrder?

Is it documented in ignite docs?

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=10 :: Size=1295


But second sql is still taking time. The only difference is it has ipStart and ipEnd filter in where clause.

getAffectedIPRange_2 :: SQL_2=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
getAffectedIPRange_2 :: TimeTakenToComplete=25436 :: Size=1260

Thanks,
Prasad

On Fri, Dec 28, 2018 at 9:02 PM Ilya Kasnacheev <ilya.kasnacheev@gmail.com> wrote:
Hello!

Did you set enforceJoinOrder to true?

Regards,
--
Ilya Kasnacheev


пт, 28 дек. 2018 г. в 18:19, Prasad Bhalerao <prasadbhalerao1983@gmail.com>:
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 <ilya.kasnacheev@gmail.com> wrote:
Hello!

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 <prasadbhalerao1983@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.
Thanks,
Prasad


On Wed, Dec 26, 2018 at 11:18 PM Prasad Bhalerao <prasadbhalerao1983@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.kasnacheev@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


Regards,
--
Ilya Kasnacheev


ср, 26 дек. 2018 г. в 19:01, Prasad Bhalerao <prasadbhalerao1983@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.png