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