So how is the real data different from the test data? - How many rows are there? - How many columns? - How big is one row on average, in bytes?
On Thu, Nov 21, 2024 at 7:08 PM Charlin S <charli...@hotelhub.com> wrote: > Hi, > The summary with real data :- > 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 | Median | Gen0 | > Gen1 | Gen2 | Allocated | > |------------- > |---------:|---------:|---------:|---------:|----------:|----------:|----------:|----------:| > | Linq | 530.0 ms | 17.28 ms | 48.46 ms | 512.4 ms | 7000.0000 | > 3000.0000 | 1000.0000 | 999.57 MB | > | LinqCompiled | 543.3 ms | 21.48 ms | 59.53 ms | 525.3 ms | 6000.0000 | > 2000.0000 | 1000.0000 | 994.65 MB | > > // * Hints * > Outliers > Benchmarks.Linq: Default -> 9 outliers were removed (722.82 > ms..1.42 s) > Benchmarks.LinqCompiled: Default -> 11 outliers were removed (767.85 > ms..2.67 s) > > Thanks > Charlin > > On Thu, 21 Nov 2024 at 16:01, Pavel Tupitsyn <ptupit...@apache.org> wrote: > >> 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 >>>>>>>>>>>>>>> >>>>>>>>>>>>>>>
