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