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
>>>>>>>>>>
>>>>>>>>>>
