Hi, Thank you for your email. I apologize for the late reply. I ran the sample and summaries are:-
BenchmarkDotNet v0.14.0, Windows 10 (10.0.19044.2130/21H2/November2021Update) Intel Core i5-8500 CPU 3.00GHz (Coffee Lake), 1 CPU, 6 logical and 6 physical cores .NET SDK 8.0.101 [Host] : .NET 8.0.1 (8.0.123.58001), X64 RyuJIT AVX2 DefaultJob : .NET 8.0.1 (8.0.123.58001), X64 RyuJIT AVX2 | Method | Mean | Error | StdDev | |--------------------|--------------:|----------:|---------------:| | Linq | 94.60 us | 1.515 us | 1.417 us | | LinqCompiled | 16.50 us | 0.328 us | 0.449 us | LINQCompiled much faster than LINQ in sample code but with real data it's not much different. Thanks Charlin On Thu, 14 Nov 2024 at 20:30, Pavel Tupitsyn <ptupit...@apache.org> wrote: > 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 >>>>>>>>>>>> >>>>>>>>>>>>
