Re: Ignite query is taking long time even no data in that cache model

[Charlin S]

Hi,
EXPLAIN SELECT
TestField1,TestField2,TestField3,TestField4,TestField5
 FROM
TestModel
 WHERE
TestField2 = 'A02'
 AND
TestField3 = 'EN'

resulview [0]: SELECT
    __Z0.TESTFIELD1 AS __C0_0,
    __Z0.TESTFIELD2 AS __C0_1,
    __Z0.TESTFIELD3 AS __C0_2,
    __Z0.TESTFIELD4 AS __C0_3,
    __Z0.TESTFIELD5 AS __C0_4
FROM "TestModel".TESTMODEL __Z0
    /* "TestModel".TESTMODEL_TESTFIELD3_ASC_IDX: TESTFIELD3 = 'EN' */
WHERE (__Z0.TESTFIELD2 = 'A02')
    AND (__Z0.TESTFIELD3 = 'EN')

resultview[1]: SELECT
    __C0_0 AS TESTFIELD1,
    __C0_1 AS TESTFIELD2,
    __C0_2 AS TESTFIELD3,
    __C0_3 AS TESTFIELD4,
    __C0_4 AS TESTFIELD5
FROM PUBLIC.__T0
    /* "TestModel"."merge_scan" */

Thanks & Regards,
Charlin

On Tue, 19 Apr 2022 at 18:04, Surinder Mehra <redni...@gmail.com> wrote:

> Looks correct to me. Can you run explain plain for this query and see if
> it uses index.
>
> On Tue, 19 Apr 2022, 17:41 Charlin S, <charli...@hotelhub.com> wrote:
>
>> Hi,
>> My query details are
>> fieldsQuery="SELECT
>> TestField1,TestField2,TestField3,TestField4,TestField5
>>  FROM
>> TestModel
>>  WHERE
>> TestField2 = 'A02'
>>  AND
>> TestField2 = 'EN'"
>>
>> //cache model
>>  public class TestModel : IBinarizable
>>     {
>>
>>         [QuerySqlField(IsIndexed = true)]
>>         public string TestField1 { get; set; }
>>         [QuerySqlField(IsIndexed = true)]
>>         public string TestField2 { get; set; }
>>         [QuerySqlField(IsIndexed = true)]
>>         public string TestField3 { get; set; }
>>         [QuerySqlField]
>>         public string TestField4 { get; set; }
>>         [QuerySqlField]
>>         public decimal? TestField5 { get; set; }
>>
>>         public void ReadBinary(IBinaryReader reader){//implementation}
>>         public void WriteBinary(IBinaryWriter writer){//implementation}
>>     }
>> implementation
>>
>> SqlFieldsQuery fieldsQuery = new SqlFieldsQuery(query) { Timeout =
>> TimeSpan.FromMilliseconds(10000) };
>>                 List<T> list = new List<T>();
>> // public ICache<string, T> IgniteCache { get; set; }
>>                 IFieldsQueryCursor queryCursor =
>> IgniteCache.Query(fieldsQuery);
>>
>> //our implementation
>>  queryCursor.Dispose();
>>
>> Thanks,
>> Charlin
>>
>>
>>
>> On Mon, 18 Apr 2022 at 13:35, Surinder Mehra <redni...@gmail.com> wrote:
>>
>>> Can you please show slow query console log output if it's using index
>>> scan or full cache scan.
>>> I ran into one scenario where index wasn't used and it ended up scaning
>>> whole cache.
>>> You can try this locally by using control centre and run explain query
>>>
>>> On Mon, 18 Apr 2022, 13:08 Charlin S, <charli...@hotelhub.com> wrote:
>>>
>>>> Hi Ignite team,
>>>> We are using Ignite 2.10.0 with 4.6.2 and .Net 5 WebAPI and we have a
>>>> 16-nodes(including 2 server nodes) Ignite cluster.
>>>> We are facing slowness issues with some particular cache model query
>>>> and other models query are fine.
>>>>
>>>> query type: SqlFieldsQuery
>>>> Index: index created for where clause columns.
>>>>
>>>> Regards,
>>>> Charlin
>>>>
>>>>