LOWER() in SQL script created by Kendo causes major performance problem!

[Frank]

Hello,

We are using Kendo.MVC, version 2020.1.400. We use microsoft sql server 2017. Our database is case insensitive.

When kendo grid request comes in, we translate Kendo's sort, paging, etc into ISpecification in SharpRepository and we let SharpRepository to handle the sql communication.

We recently made the code change that we skip SharpRepository and use Kendo extension ToDataSourceResult() to deal with sql server.

For large data set, we find the new approach takes much longer time to show the Kendo grid. After investigation, I found that the sql generated by Kendo contains LOWER() for columns! Below is an example. However, in SQL Server, when you use LOWER for a column, the index for that column is not used! It has to do clustered index scan, which is very slow.

I saw an old post saying LOWER() is built in behavior and no way to change it. https://www.telerik.com/forums/grid-filtering-on-text-using-lower-in-sql

I wonder if the latest kendo can allow the user to disable LOWER() behavior. it is very straightforward for your company to fix it, basically providing an additional boolean parameter to ToDataSourceResult() to enable or disable LOWER(). A small thing for Kendo but a big thing for us. Currently if LOWER() can't be removed, we have to go back to the old approach by using SharpRepository. the sql generated by SharpRepository doesn't include LOWER. Our database is case insensitive, so LOWER() is really unnecessary and it is a performance bottleneck.

Please please please fix it.

SELECT
    [Extent1].[UserId] AS [UserId],
    [Extent1].[Id] AS [Id],
    [Extent1].Blah
    FROM [dbo].[UserFilteredWorkbenchItems] AS [Extent1]
WHERE (((LOWER([Extent1].[UserId])) = (LOWER(N'881d796c-9c88-ea11-99e7-0003ff77da57'))) OR ((LOWER([Extent1].[UserId]) IS NULL) AND (LOWER(N'881d796c-9c88-ea11-99e7-0003ff77da57') IS NULL))) AND (((LOWER([Extent1].[FilterByUnit])) = (LOWER(N''))) OR ((LOWER([Extent1].[FilterByUnit]) IS NULL) AND (LOWER(N'') IS NULL)))
    ORDER BY row_number() OVER (ORDER BY [Extent1].[Priority] ASC)
    OFFSET 160 ROWS FETCH NEXT 20 ROWS ONLY

Best regards

Frank

[Brian]

We are experiencing the same issue - unacceptable slow performance using the ToDataSourceResult function. After capturing the generate state via SQL profiler, I determined that it is NOT the lower() function causing this issue. It is exactly the ORDER BY row_number() OVER statement that is causing the performance degradation.   

[Ivan Danchev]

Hello Frank,

I'll paste my reply from the support ticket here:

An option to avoid this behavior has been requested a few times and there is a feature request logged in the Feedback Portal: https://feedback.telerik.com/aspnet-core-ui/1458053-kendo-mvc-extensions-queryableextensions-always-lowers-string-equals

The GenerateToLowerCall method is called in both GenerateEqual and GenerateNotEqual methods. Unfortunately, it hasn't acquired a lot of support from the community so far (only 1 vote), so we haven't revised the logic and implemented an alternative to the current behavior yet.

Moritz has suggested a possible workaround in the feature request item I linked.

Regards,
Ivan Danchev
Progress Telerik

[Brian]

We are experiencing the same issue - unacceptable slow performance using the ToDataSourceResult function. After capturing the generate state via SQL profiler, I determined that it is NOT the lower() function causing this issue. It is exactly the ORDER BY row_number() OVER statement that is causing the performance degradation.   

[Ivan Danchev]

Consider logging an item dedicated to performance improvement of ToDataSourceResult in the Feedback Portal: https://feedback.telerik.com/aspnet-mvc

If you choose to do so, please share your findings and reproduction steps.