Can you adjust this benchmark project to have the data and query more like your real project? Currently we have a similar number of rows and the query performs very well, let's understand what is different.
On Thu, Nov 21, 2024 at 11:43 AM Charlin S <charli...@hotelhub.com> wrote: > 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 >>>>>>>>>>>>> >>>>>>>>>>>>>
