ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Prasad Bhalerao <prasadbhalerao1...@gmail.com>
Subject Re: Query Execution very slow
Date Wed, 26 Dec 2018 17:48:45 GMT
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
>
> See https://apacheignite.readme.io/docs/configuration-parameters for
> enforceJoinOrder.
>
> 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: image.png]
>>
>

Mime
View raw message