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 <[email protected]> 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 <[email protected]> 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 <[email protected]>
>> 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 <[email protected]>
>>> 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 <[email protected]>
>>>> 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 <[email protected]>
>>>>> 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 <[email protected]>
>>>>>> 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 <[email protected]>
>>>>>>> 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
>>>>>>>>
>>>>>>>>
