[Solved] Paging performance and a suggestion

[Akzhol The Kyrgyz]

Hi,

I decided to investigate what's going on behind the paging, since paging was extremely slow in the grid even though the data is coming from one table/indexed view.
The TSQL query behind paging was: 

exec sp_executesql N'SELECT [t1].[TCK], [t1].[Ad], [t1].[Soyad], [t1].[MemberNo], [t1].[AnaAdi], [t1].[BabaAdi], [t1].[Cinsiyet], [t1].[DogumTarihi], [t1].[Meslek], [t1].[Tahsil], [t1].[MedeniHal],
[t1].[BloodType], [t1].[Status], [t1].[Nufus_Il], [t1].[Nufus_Ilce], [t1].[PID], [t1].[MobilTel1], [t1].[MobilTel2], [t1].[CiltNo], [t1].[BireySiraNo], [t1].[DogumYeri], [t1].[Email], [t1].[Il],
[t1].[Ilce], [t1].[Belde], [t1].[MahalleKoy]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[TCK], [t0].[Ad], [t0].[Soyad], [t0].[MemberNo], [t0].[AnaAdi], [t0].[BabaAdi], [t0].[Cinsiyet], [t0].[DogumTarihi], [t0].[Meslek], [t0].[Tahsil],
[t0].[MedeniHal], [t0].[BloodType], [t0].[Status], [t0].[Nufus_Il], [t0].[Nufus_Ilce], [t0].[PID], [t0].[MobilTel1], [t0].[MobilTel2], [t0].[CiltNo], [t0].[BireySiraNo], [t0].[DogumYeri], [t0].[Email],
[t0].[Il], [t0].[Ilce], [t0].[Belde], [t0].[MahalleKoy]) AS [ROW_NUMBER], [t0].[TCK], [t0].[Ad], [t0].[Soyad], [t0].[MemberNo], [t0].[AnaAdi], [t0].[BabaAdi], [t0].[Cinsiyet], [t0].[DogumTarihi],
[t0].[Meslek], [t0].[Tahsil], [t0].[MedeniHal], [t0].[BloodType], [t0].[Status], [t0].[Nufus_Il], [t0].[Nufus_Ilce], [t0].[PID], [t0].[MobilTel1], [t0].[MobilTel2], [t0].[CiltNo], [t0].[BireySiraNo],
[t0].[DogumYeri], [t0].[Email], [t0].[Il], [t0].[Ilce], [t0].[Belde], [t0].[MahalleKoy]
    FROM [TBS].[vwMemberDetails] AS [t0]
    WHERE ([t0].[Il] = @p0) AND ([t0].[Status] = @p1)
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p2 + 1 AND @p2 + @p3
ORDER BY [t1].[ROW_NUMBER]',N'@p0 int,@p1 int,@p2 int,@p3 int',@p0=12,@p1=1,@p2=120,@p3=30

And this took around 100 seconds in a server, when there is no other user. This performance is just unacceptable, when there are hundreds of users. But when I change the query to the following, it takes less than a second.

exec sp_executesql N'SELECT [t1].[TCK], [t1].[Ad], [t1].[Soyad], [t1].[MemberNo], [t1].[AnaAdi], [t1].[BabaAdi], [t1].[Cinsiyet], [t1].[DogumTarihi], [t1].[Meslek], [t1].[Tahsil], [t1].[MedeniHal],
[t1].[BloodType], [t1].[Status], [t1].[Nufus_Il], [t1].[Nufus_Ilce], [t1].[PID], [t1].[MobilTel1], [t1].[MobilTel2], [t1].[CiltNo], [t1].[BireySiraNo], [t1].[DogumYeri], [t1].[Email], [t1].[Il],
[t1].[Ilce], [t1].[Belde], [t1].[MahalleKoy]
FROM (
    SELECT ROW_NUMBER() OVER (  ORDER BY [t0].[TCK]  ) AS [ROW_NUMBER], [t0].[TCK], [t0].[Ad], [t0].[Soyad], [t0].[MemberNo], [t0].[AnaAdi], [t0].[BabaAdi], [t0].[Cinsiyet], [t0].[DogumTarihi],
[t0].[Meslek], [t0].[Tahsil], [t0].[MedeniHal], [t0].[BloodType], [t0].[Status], [t0].[Nufus_Il], [t0].[Nufus_Ilce], [t0].[PID], [t0].[MobilTel1], [t0].[MobilTel2], [t0].[CiltNo], [t0].[BireySiraNo],
[t0].[DogumYeri], [t0].[Email], [t0].[Il], [t0].[Ilce], [t0].[Belde], [t0].[MahalleKoy]
    FROM [TBS].[vwMemberDetails] AS [t0]
    WHERE ([t0].[Il] = @p0) AND ([t0].[Status] = @p1)
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p2 + 1 AND @p2 + @p3
ORDER BY [t1].[ROW_NUMBER]',N'@p0 int,@p1 int,@p2 int,@p3 int',@p0=12,@p1=1,@p2=120,@p3=30

 
Why telerik is not generating such query, even if there is SingleColumn sort is enabled? (of course [t0].[TCK] has to be changed according to the sorted column)
Is there a way for me/you to change this TSQL generation?

[t0].[Tahsil], [t0].[MedeniHal], [t0].[BloodType], [t0].[Status], [t0].[Nufus_Il], [t0].[Nufus_Ilce], [t0].[PID], [t0].[MobilTel1], [t0].[MobilTel2], [t0].[CiltNo], [t0].[BireySiraNo], 

[t0].[DogumYeri], [t0].[Email], [t0].[Il], [t0].[Ilce], [t0].[Belde], [t0].[MahalleKoy]

    FROM [TBS].[vwMemberDetails] AS [t0]

    WHERE ([t0].[Il] = @p0) AND ([t0].[Status] = @p1)

    ) AS [t1]

WHERE [t1].[ROW_NUMBER] BETWEEN @p2 + 1 AND @p2 + @p3

ORDER BY [t1].[ROW_NUMBER]',N'@p0 int,@p1 int,@p2 int,@p3 int',@p0=12,@p

SELECT [t1].[TCK], [t1].[Ad], [t1].[Soyad], [t1].[MemberNo], [t1].[AnaAdi], [t1].[BabaAdi], [t1].[Cinsiyet], [t1].[DogumTarihi], [t1].[Meslek], [t1].[Tahsil], [t1].[MedeniHal], 

[t1].[BloodType], [t1].[Status], [t1].[Nufus_Il], [t1].[Nufus_Ilce], [t1].[PID], [t1].[MobilTel1], [t1].[MobilTel2], [t1].[CiltNo], [t1].[BireySiraNo], [t1].[DogumYeri], [t1].[Email], [t1].[Il], 

[t1].[Ilce], [t1].[Belde], [t1].[MahalleKoy]

FROM (

    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[TCK], [t0].[Ad], [t0].[Soyad], [t0].[MemberNo], [t0].[AnaAdi], [t0].[BabaAdi], [t0].[Cinsiyet], [t0].[DogumTarihi], [t0].[Meslek], [t0].[Tahsil], 

[t0].[MedeniHal], [t0].[BloodType], [t0].[Status], [t0].[Nufus_Il], [t0].[Nufus_Ilce], [t0].[PID], [t0].[MobilTel1], [t0].[MobilTel2], [t0].[CiltNo], [t0].[BireySiraNo], [t0].[DogumYeri], [t0].[Email], 

[t0].[Il], [t0].[Ilce], [t0].[Belde], [t0].[MahalleKoy]) AS [ROW_NUMBER], [t0].[TCK], [t0].[Ad], [t0].[Soyad], [t0].[MemberNo], [t0].[AnaAdi], [t0].[BabaAdi], [t0].[Cinsiyet], [t0].[DogumTarihi], 

[t0].[Meslek], [t0].[Tahsil], [t0].[MedeniHal], [t0].[BloodType], [t0].[Status], [t0].[Nufus_Il], [t0].[Nufus_Ilce], [t0].[PID], [t0].[MobilTel1], [t0].[MobilTel2], [t0].[CiltNo], [t0].[BireySiraNo], 

[t0].[DogumYeri], [t0].[Email], [t0].[Il], [t0].[Ilce], [t0].[Belde], [t0].[MahalleKoy]

    FROM [TBS].[vwMemberDetails] AS [t0]

    WHERE [t0].[Status] = 1

    ) AS [t1]

WHERE [t1].[ROW_NUMBER] BETWEEN 270 + 1 AND 270 + 30

ORDER BY [t1].[ROW_NUMBER]

SELECT [t1].[TCK], [t1].[Ad], [t1].[Soyad], [t1].[MemberNo], [t1].[AnaAdi], [t1].[BabaAdi], [t1].[Cinsiyet], [t1].[DogumTarihi], [t1].[Meslek], [t1].[Tahsil], [t1].[MedeniHal], 

[t1].[BloodType], [t1].[Status], [t1].[Nufus_Il], [t1].[Nufus_Ilce], [t1].[PID], [t1].[MobilTel1], [t1].[MobilTel2], [t1].[CiltNo], [t1].[BireySiraNo], [t1].[DogumYeri], [t1].[Email], [t1].[Il], 

[t1].[Ilce], [t1].[Belde], [t1].[MahalleKoy]

FROM (

    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[TCK], [t0].[Ad], [t0].[Soyad], [t0].[MemberNo], [t0].[AnaAdi], [t0].[BabaAdi], [t0].[Cinsiyet], [t0].[DogumTarihi], [t0].[Meslek], [t0].[Tahsil], 

[t0].[MedeniHal], [t0].[BloodType], [t0].[Status], [t0].[Nufus_Il], [t0].[Nufus_Ilce], [t0].[PID], [t0].[MobilTel1], [t0].[MobilTel2], [t0].[CiltNo], [t0].[BireySiraNo], [t0].[DogumYeri], [t0].[Email], 

[t0].[Il], [t0].[Ilce], [t0].[Belde], [t0].[MahalleKoy]) AS [ROW_NUMBER], [t0].[TCK], [t0].[Ad], [t0].[Soyad], [t0].[MemberNo], [t0].[AnaAdi], [t0].[BabaAdi], [t0].[Cinsiyet], [t0].[DogumTarihi], 

[t0].[Meslek], [t0].[Tahsil], [t0].[MedeniHal], [t0].[BloodType], [t0].[Status], [t0].[Nufus_Il], [t0].[Nufus_Ilce], [t0].[PID], [t0].[MobilTel1], [t0].[MobilTel2], [t0].[CiltNo], [t0].[BireySiraNo], 

[t0].[DogumYeri], [t0].[Email], [t0].[Il], [t0].[Ilce], [t0].[Belde], [t0].[MahalleKoy]

    FROM [TBS].[vwMemberDetails] AS [t0]

    WHERE [t0].[Status] = 1

    ) AS [t1]

WHERE [t1].[ROW_NUMBER] BETWEEN 270 + 1 AND 270 + 30

ORDER BY [t1].[ROW_NUMBER]

[Atanas Korchev]

Hello Akzhol The Kyrgyz,

 Actually Telerik Grid for ASP.NET MVC is not generating any SQL expressions. It is using LINQ expressions to build the query (similar to data.Skip(pageSize*currentPage).Take(pageSize)). In your case this SQL is generated by LinqToSql, EntityFramework or whatever other Linq capable ORM you are using.

Regards,
Atanas Korchev
the Telerik team