1. Use "==" instead of "string.Equals", looks like the provider does not like string.Equals 2. Share the generated SQL 3. Share the result of EXPLAIN for that SQL 4. Is there an index on CountryCode?
On Wed, Nov 13, 2024 at 9:16 AM Charlin S <charli...@hotelhub.com> wrote: > Hi, > Thanks for your response. > I have tried different ways but the result is the same. > my cache records count is above 160,0000 > var watchLINQQuery = System.Diagnostics.Stopwatch.StartNew(); > var tmp= TestIcache.AsEnumerable().Where(tc=> string.Equals(tc. > Value.CountryCode, CountryCode)).Select(tc => tc.Value); > watchLINQQuery.Stop(); //0 or 1 Milliseconds > var watchIQueryableToArray = System.Diagnostics.Stopwatch.StartNew(); > > var result = tmp.ToArray(); // 12354 milliseconds taken > watchIQueryableToArray.Stop(); > > var result = tmp.ToArray(); taking similar time even if my query result > returns 1 or 2 records. Please suggest to me how to improve this query > performance. > Regards, > Charlin > > > On Tue, 5 Nov 2024 at 19:01, Pavel Tupitsyn <ptupit...@apache.org> wrote: > >> 1. Check the generated SQL >> >> // Cast to ICacheQueryable >> var cacheQueryable = (ICacheQueryable) query; >> >> // Get resulting fields query >> SqlFieldsQuery fieldsQuery = cacheQueryable.GetFieldsQuery(); >> >> // Examine generated SQL >> Console.WriteLine(fieldsQuery.Sql); >> >> >> 2. Try EXPLAIN and other suggestions from >> https://ignite.apache.org/docs/latest/SQL/sql-tuning >> >> 3. Is there an index on CountryCode? >> >> 4. Case-insensitive comparison might be inefficient. A better approach is >> to store lower/uppercase value in cache, and then using lower/upper >> criteria for search >> >> On Tue, Nov 5, 2024 at 1:08 PM Charlin S <charli...@hotelhub.com> wrote: >> >>> Hi Pavel, >>> Thanks for your email. it reduces to 9 seconds after removing >>> AsParallel. Please let me know if there are any more options to get good >>> performance. >>> >>> Regards, >>> Charlin >>> >>> On Tue, 5 Nov 2024 at 13:31, Pavel Tupitsyn <ptupit...@apache.org> >>> wrote: >>> >>>> Hi, "AsParallel" is the problem, it causes the entire data set to be >>>> loaded locally before filtering. >>>> >>>> Remove it so that the LINQ expression can be translated into Ignite SQL >>>> and executed more efficiently. >>>> >>>> https://ignite.apache.org/docs/latest/net-specific/net-linq >>>> >>>> On Tue, Nov 5, 2024 at 8:58 AM Charlin S <charli...@hotelhub.com> >>>> wrote: >>>> >>>>> Hi All, >>>>> >>>>> I am trying Ignit.Net LINQ for the first time and seeing very slowness >>>>> with my linq query taking 13-15 seconds. Test model having 550,000 records >>>>> my query as below >>>>> TestModel having index for CountryCode field. >>>>> ICache<string, TestModel> cache = ignite.GetCache<string, >>>>> TestModel>(CacheName); >>>>> IQueryable<ICacheEntry<string, TestModel>> igniteQuerable = >>>>> cache.AsCacheQueryable(); >>>>> igniteQuerable.AsParallel() >>>>> .Where(x=>string.Equals(x.Value.CountryCode, criteria. >>>>> CountryCode, StringComparison.CurrentCultureIgnoreCase)) >>>>> .Select(x => x.Key).ToList(); >>>>> >>>>> >>>>> Regards, >>>>> Charlin >>>>> >>>>>
