Hi Ignite team, I have added a group index but the slowness issue still persists. not understanding why it's happening only for some specific cache models.
Thanks, Charlin On Wed, 20 Apr 2022 at 11:45, Surinder Mehra <redni...@gmail.com> wrote: > this might help, scroll down to group indexes section: > https://ignite.apache.org/docs/latest/SQL/indexes > > On Wed, Apr 20, 2022 at 10:59 AM Charlin S <charli...@hotelhub.com> wrote: > >> Hi, >> why my query is having only one index field, it supposed to be two index >> column >> >> /* "TestModel".TESTMODEL_TESTFIELD3_ASC_IDX: TESTFIELD3 = 'EN' */ >> >> Thanks & Regards, >> Charlin >> >> >> On Tue, 19 Apr 2022 at 19:34, Surinder Mehra <redni...@gmail.com> wrote: >> >>> I may be wrong but as an excercise, can you try group index on these >>> fields please and see if it makes any difference. >>> I would request Apache ignite Dev's to validate it. >>> >>> On Tue, 19 Apr 2022, 19:20 Charlin S, <charli...@hotelhub.com> wrote: >>> >>>> 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 >>>>>>>> >>>>>>>>
