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 Fri, 28 Dec 2018 15:59:00 GMT
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!
>>>
>>> I have created a PR for you:
>>> https://github.com/prasadbhalerao1983/IgniteTestPrj/pull/1
>>> 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.
>>>> GitHub project: https://github.com/prasadbhalerao1983/IgniteTestPrj.git
>>>>
>>>> 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
>>>>>>
>>>>>> 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