I'm currently looking into server side paging and data calls for the kendoUI grid. I can get the grid to perform the server side transaction but I have notices that even if a select all or select the top 10 the reads to the SQL database are the same.
- When returning all the records - it gets a sql profiler read of 104
- When returning only 10 records - it gets a sql profiler read of 104, as well
Function used to return the data
public List<Employee> GetEmployees(int take, int skip)
return GetRepo<Employee>().All(null).where(p => p.IsDeleted == false).OrderBy(p => p.EmployeeNumber).Skip(10).Take(10).ToList();
When the LINQ performs the 10 record call, I have used the built in .Skip() and .Take() functions. This method require an .OrderBy() function to work, and I have order by the Id column on the table.
When you use the .Skip() and .Take() functions, it wraps a top 10 on the outside of the same query used to call all the records at once. Please see the sql extract taken from sql profiler,
SELECT TOP (10) *
FROM ( SELECT *, row_number() OVER (ORDER BY [Project1].[EmployeeNumber] ASC) AS [row_number]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent2].[EmployeeNumber] AS [EmployeeNumber],
[Extent2].[WeeklyHours] AS [WeeklyHours],
'0X0X' AS [C1]
FROM [dbo].[Person] AS [Extent1]
INNER JOIN [dbo].[Employee] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
WHERE 0 = [Extent2].[IsDeleted]
) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[EmployeeNumber] ASC
The project is a asp.net MVC project and using framework 4.5.
Is there a way to return the 10 records back without having to scan through the whole table? As the employee and person table will grow to be more than 1 million active records at any time.
If this is in the wrong category, my apologies for that.
Any help would be greatly appreciated.
- Changed type Fred BaoModerator Friday, December 20, 2013 11:30 AM It is a question
Glad to hear that you have found the solution and share it with us.
Usually, as said in the link, we use the IQuerable<T> because it will translate the method like Take() and Skip() to sql statement, but ToList<T> and IEnumerable<T> will not, they just do the filter in memory.
In a word, cheers.
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.