> 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>> igniteQuerable >>>>>>>>>>>>>>>>> = cache.AsCacheQueryable(); >>>>>>>>>>>>>>>>> igniteQuerable.AsParallel() >>>>>>>>>>>>>>>>> .Where(x=>string.Equals(x.Value.CountryCode, >>>>>>>>>>>>>>>>> criteria.CountryCode, StringComparison. >>>>>>>>>>>>>>>>> CurrentCultureIgnoreCase)) >>>>>>>>>>>>>>>>> .Select(x => x.Key).ToList(); >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Regards, >>>>>>>>>>>>>>>>> Charlin >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>
