Primary Key count Count in LINQ very slow

Question

I thought LINQ to SQL was tuned for performance?

The following LINQ to SQL for counting is very poor

Dim uniqueFactors As Integer = db.LargeTable.Distinct.Count

produces:

SELECT COUNT(*) AS [value]
FROM [dbo].[LargeTable] AS [t0]
WHERE ([t0].[ID] % @p0) = @p1

How every the fastest way to count the number of records based on a primary key is

SELECT  @totalRowCount = rows
FROM    sysindexes
WHERE   id = OBJECT_ID('LargeTable')
    AND indid < 2

So the question, how can I ensure that LINQ to SQL performs a count quickly when asking for Count(*)?

Answer 1

Well, it depends on what you expect. If you ask for the "Distinct.Count" on a table, you're instructing LINQ to do exactly what it does. Since that will result in a table scan, it will be slow for larger tables.

The SELECT COUNT(*) is the only way SQL Server (and therefore LINQ) can give you an exact, up-to-date count of the rows in the table.

Selecting a rows from sysindexes (or preferably: sys.partitions in SQL Server 2005 and up - the "sysindexes" views are being deprecated) will give you a approximate number - but that's not guaranteed to be absolutely correct and up to date.

So basically, what LINQ is missing, is a "UseApproximationForPerformancesSake" switch. That might be helpful at times - but then again, you can always use that little chunk of SQL and query the database yourself, if you need a speedy and only approximate response.

Marc

Answer 2

I can't reproduce your TSQL; have you omitted part of the LINQ query (i.e. the "where") from the question? No matter how I try, I get pretty normal TSQL that perfoms ok...

Since the primary key is unique, why not just:

int count = db.LargeTable.Count();

Otherwise; have you tried selecting the primary key?

int count = db.LargeTable.Select(x=>x.Id).Distinct().Count();

Answer 3

Changing your linq to "db.LargeTable.Distinct.Count();"

Should produce the following SQL

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

Which should use an index scan instead of a table scan. Which should be much quicker.