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

1 Answer 44 Views
Filter Grid
Frank
Top achievements
Rank 1
Frank asked on 29 Jul 2021, 12:26 AM

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
Top achievements
Rank 1
commented on 05 Aug 2021, 05:18 PM

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.   

1 Answer, 1 is accepted

Sort by
0
Ivan Danchev
Telerik team
answered on 02 Aug 2021, 02:50 PM

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

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

Brian
Top achievements
Rank 1
commented on 05 Aug 2021, 05:18 PM

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
Telerik team
commented on 10 Aug 2021, 09:54 AM

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.

Tags
Filter Grid
Asked by
Frank
Top achievements
Rank 1
Answers by
Ivan Danchev
Telerik team
Share this question
or