1. Run the code in Release configuration 2. Try Compiled Query https://ignite.apache.org/docs/latest/net-specific/net-linq#compiled-queries
My benchmark on 550_000 rows with 1 matching row shows 28 microseconds for regular query and 6 microseconds for compiled query: https://gist.github.com/ptupitsyn/189c8164061bace8d975b2ec39045ca4 200 ms is a lot. Can you run my code and share the results? On Thu, Nov 14, 2024 at 1:46 PM Charlin S <charli...@hotelhub.com> wrote: > Hi, > I was measuring performance after 5 times. Now I have run more than 100 > times and var queryRes = query.ToList(); taken 202 milliseconds as > minimum. > Will be appreciated if I get a solution for getting the result in 10 - 50 > milliseconds. > > Regards, > Charlin > > > > On Thu, 14 Nov 2024 at 15:35, Pavel Tupitsyn <ptupit...@apache.org> wrote: > >> How do you measure? Do you perform a warm up before measurement? >> Try running the query 100 times before the measurement. Or, ideally, use >> BenchmarkDotNet to get accurate results. >> >> On Thu, Nov 14, 2024 at 11:51 AM Charlin S <charli...@hotelhub.com> >> wrote: >> >>> Hi, >>> Thank you for your response.and sample. >>> My cache instance was created as TestIcache=_ignite.GetOrCreateCache< >>> string, TestModel>("TestModel"); so index was not applied. >>> now I am creating cache instance like below and index applied >>> var cacheCfgTestModel = new CacheConfiguration("TestModel", new >>> QueryEntity(typeof(TestModel))); >>> TestIcache=_ignite.GetOrCreateCache<string, TestModel>( >>> cacheCfgTestModel); >>> >>> SQL: >>> select _T0._KEY, _T0._VAL from "TestModel".TESTMODEL as _T0 where >>> (_T0.COUNTRYCODE IS NOT DISTINCT FROM ?) >>> >>> EXPLANATION: >>> SELECT >>> _T0__Z0._KEY AS __C0_0, >>> _T0__Z0._VAL AS __C0_1 >>> FROM "TestModel".TESTMODEL _T0__Z0 >>> /* "TestModel".TESTMODEL_COUNTRYCODE_ASC_IDX: COUNTRYCODE IS ?1 */ >>> WHERE _T0__Z0.COUNTRYCODE IS ?1 >>> SELECT >>> __C0_0 AS _KEY, >>> __C0_1 AS _VAL >>> FROM PUBLIC.__T0 >>> /* "TestModel"."merge_scan" */ >>> >>> Total records:27713 = > 27713 >>> Time taken for Query:2 milliseconds -> var query = TestIcache. >>> AsCacheQueryable().Where(x => x.Value.CountryCode == CountryCode); Time >>> taken for QueryResult:313 milliseconds -> var queryRes = query.ToList(); >>> Total Time taken:316 milliseconds >>> Why var queryRes = query.ToList(); taking 313 milliseconds even if the >>> result having only one record is taking nearly 300 milliseconds. >>> >>> Regards, >>> Charlin >>> >>> >>> >>> On Wed, 13 Nov 2024 at 22:36, Pavel Tupitsyn <ptupit...@apache.org> >>> wrote: >>> >>>> - This shows a full scan, the index is not being used. >>>> - There is no parametrization, I'm not sure why. Which Ignite version >>>> do you use? >>>> >>>> I've prepared a simple proof of concept [1] which shows that an index >>>> is used in this situation. Generated SQL is different: >>>> >>>> >> SQL: >>>> select _T0._KEY, _T0._VAL from "c".TESTMODEL as _T0 where >>>> (_T0.COUNTRYCODE IS NOT DISTINCT FROM ?) >>>> >>>> >> EXPLAIN: >>>> SELECT >>>> _T0__Z0._KEY AS __C0_0, >>>> _T0__Z0._VAL AS __C0_1 >>>> FROM "c".TESTMODEL _T0__Z0 >>>> /* "c".TESTMODEL_COUNTRYCODE_ASC_IDX: COUNTRYCODE IS ?1 */ >>>> WHERE _T0__Z0.COUNTRYCODE IS ?1 >>>> SELECT >>>> __C0_0 AS _KEY, >>>> __C0_1 AS _VAL >>>> FROM PUBLIC.__T0 >>>> /* "c"."merge_scan" */ >>>> >>>> [1] https://gist.github.com/ptupitsyn/882b9b9e5e13c82fcf96f83fd53b2777 >>>> >>>> >>>> >>>> On Wed, Nov 13, 2024 at 6:41 PM Charlin S <charli...@hotelhub.com> >>>> wrote: >>>> >>>>> Hi, >>>>> 1. Use "==" instead of "string.Equals", looks like the provider does >>>>> not like string.Equals. >>>>> changed to == >>>>> 2. Share the generated SQL >>>>> >>>>> SELECT Field1,Field2 FROM TestModel WHERE COUNTRYCODE='AU' >>>>> 3. Share the result of EXPLAIN for that SQL >>>>> plan=SELECT >>>>> __Z0.FIELD1 AS __C0_0, >>>>> __Z0.FIELD2 AS __C0_1 >>>>> FROM "TestModel".TESTMODEL __Z0 >>>>> /* "TestModel".TESTMODEL.__SCAN_ */ >>>>> /* scanCount: 1681786 */ >>>>> WHERE (__Z0.COUNTRYCODE = 'AU') >>>>> 4. Is there an index on CountryCode? >>>>> Yes, [QuerySqlField(IsIndexed = true)] >>>>> public string CountryCode { get; set; } >>>>> >>>>> Thanks, >>>>> Charlin >>>>> >>>>> On Wed, 13 Nov 2024 at 15:06, Pavel Tupitsyn <ptupit...@apache.org> >>>>> wrote: >>>>> >>>>>> 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 >>>>>>>>>>> >>>>>>>>>>>
