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