I am implementing paging using LINQ, and i was thinking about the performance issues that i could encounter. Lets say i have a page size of 10 and i have 100 records in a table. If i were to use the following
var myList = _dataContext.Person.Skip(PageNumber * PageSize).Take(10); it will get me the data i want. now if i were to take this scenario, and apply it to 1,000,000 records. will this affect the performance? Would it be much more worth using an sql stored procedure, performance wise?
This won't be returning all the rows from the database before it's skipping and taking. If you did a ToList after the .Person, then that would be very inefficient. What you have there should be fine
Stored procedures do not have any advantages over plain SQL text, which is generated for you by Linq provider.
BTW for your query something like following SQL will be generated
SELECT /* [t0] fields */ FROM ( SELECT ROW_NUMBER() OVER (ORDER BY /* [t0] fields */ FROM [Person] AS [t0] ) AS [t1] WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1 ORDER BY [t1].[ROW_NUMBER] And it will be executed only when you will use myList variable (e.g. call ToList(), Count(), enumerate it). Thus it does not matter how many persons you have in database (well, matter, but this is db part, not objects in memory), and you will not have performance boost from usage of stored procedure.
