Geek Answers Handbook

LINQ to SQL Pagination and COUNT (*)

I use the PagedList class in my web application, and many of you may be familiar with what you were doing with ASP.NET MVC and LINQ to SQL. This was reported by Rob Conery , and a similar incarnation was included in things like Nerd Dinner , etc. It works great, but my database administrator expressed concern about possible future performance issues.

Its problem is related to SELECT COUNT (*), which is returned as a result of this line:

TotalCount = source.Count();

Any action that has paged data will trigger an additional query (for example, below) as a result of calling the IQueryable.Count () method:

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

Is there a better way to handle this? I examined the use of the Count property of the PagedList class to get the number of elements, but realized that this would not work because it counts the number of elements currently being displayed (and not the total number).

How much of the performance will be the cause of my application when there is a lot of data in the database?

+6
performance asp.net-mvc linq-to-sql pagination
source share
4 answers

iirc this material is part of the index statistics and should be very effective; you should ask your database administrator to back up your problems and not optimize prematurely.

+8
source share

This is actually a fairly common problem with Linq.

Yes, index statistics will be used if the statement was only SELECT COUNT(*) AS [value] FROM [dbo].[Products] AS [t0] , but in 99% of cases it will also contain WHERE statements.

So basically two SQL statements are executed:

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

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

You start to get problems if the table is updated frequently because the COUNT(*) returned in the first statement is not equal to the second statement ... this may return the error message "Row not found or modified."

+2
source share

Some databases (Oracle, Postgresql, SQL Server, I think) store row records in system tables; although sometimes they are accurate until the last time the statistics were updated (Oracle). You can use this approach if you need only accurate, but not exact metrics.

What database are you using, or does it depend?

+1
source share

(PS I know what you're talking about MsSQL, however)

I'm not a DBA, but count (*) in MySQL is a real performance hit. Simply changing this metric (ID) really improves speed.

I came across this when I requested a table with very large GLOB data (Images). The query tool takes about 15 seconds. Changing the request to count (id) reduced the request to 0.02. Still a little slow, but damn much better.

I think this is what the DBA leads to. I noticed when the debuggin Linq statement, which counts, takes a very long time (1 second) to move on to the next statement.

Based on my finding, I have to agree with the DBA consuls ...

+1
source share

More articles:


All Articles