LINQ to SQL Pagination and COUNT(*)

Question

I'm using the PagedList class in my web application that many of you might be familiar with if you have been doing anything with ASP.NET MVC and LINQ to SQL. It has been blogged about by Rob Conery, and a similar incarnation was included in things like Nerd Dinner, etc. It works great, but my DBA has raised concerns about potential future performance problems.

His issue is around the SELECT COUNT(*) that gets issued as a result of this line:

TotalCount = source.Count();

Any action that has paged data will fire off an additional query (like below) as a result of the IQueryable.Count() method call:

SELECT COUNT(*) AS [value] FROM [dbo].[Products] AS [t0] 

Is there a better way to handle this? I considered using the Count property of the PagedList class to get the item count, but realized that this won't work because it's only counting the number of items currently displayed (not the total count).

How much of a performance hit will this cause to my application when there's a lot of data in the database?

Answer 1

Some databases (Oracle, Postgresql, SQL Server I think) keep a record of row counts in the system tables; though these are sometimes only accurate to the point at which the statistics were last refreshed (Oracle). You could use this approach, if you only need a fairly-accurate-but-not-exact metric.

Which database are you using, or does that vary?

Answer 2

iirc this stuff is a part of index stats and should be very efficient, you should ask your DBA to substatiate his concerns, rather than prematurely optimising.

Answer 3

Actually, this is a pretty common issue with Linq.

Yes, index stats will get used if the statement was only 'SELECT COUNT(*) AS [value] FROM [dbo].[Products] AS [t0] ' but 99% of the time its going to contain WHERE statements as well.

So basically two SQL statements are executed:

1. SELECT COUNT(*) AS [value] FROM [dbo].[Products] AS [t0]
   WHERE blah=blah and someint=500

2. SELECT blah, someint FROM [dbo].[Products] AS [t0]
   WHERE blah=blah and someint=500

You start receiving problems if the table is updated often as the COUNT(*) returned in the first statement doesnt equal the second statement...this may return an error message 'Row not found or changed.'