Hi, Yes, It's approximately 300MB. Thanks Charlin
On Mon, 25 Nov 2024 at 12:52, Pavel Tupitsyn <ptupit...@apache.org> wrote: > > How big is one row on average, in bytes? > > Approximately 300MB size > > 300 Megabytes, is that correct? This explains the measurements, I think. > > On Fri, Nov 22, 2024 at 4:27 PM Charlin S <charli...@hotelhub.com> wrote: > >> Hi >> records count => more than 160,0000 >> No of columns => 87 Columns (80 columns data null only) >> How big is one row on average, in bytes? Approximately 300MB size >> >> New stats after removing 80 columns with same no of records >> 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 | Gen0 | Gen1 | >> Gen2 | Allocated | >> |------------- >> |---------:|---------:|---------:|----------:|---------:|---------:|----------:| >> | Linq | 188.6 ms | 15.08 ms | 43.26 ms | 1000.0000 | - | >> - | 126.01 MB | >> | LinqCompiled | 166.8 ms | 3.24 ms | 5.23 ms | 666.6667 | 666.6667 | >> 333.3333 | 121.18 MB | >> >> Thanks >> Charlin >> >> On Fri, 22 Nov 2024 at 00:07, Pavel Tupitsyn <ptupit...@apache.org> >> wrote: >> >>> 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>> i >>>>>>>>>>>>>>>>>> gniteQuerable = cache.AsCacheQueryable(); >>>>>>>>>>>>>>>>>> igniteQuerable.AsParallel() >>>>>>>>>>>>>>>>>> .Where(x=>string.Equals(x.Value.CountryCode, >>>>>>>>>>>>>>>>>> criteria.CountryCode, StringComparison. >>>>>>>>>>>>>>>>>> CurrentCultureIgnoreCase)) >>>>>>>>>>>>>>>>>> .Select(x => x.Key).ToList(); >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> Regards, >>>>>>>>>>>>>>>>>> Charlin >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>
