Have Geek Brain

Count primary keys in LINQ is very slow

I thought LINQ to SQL was tuned for performance?

Following LINQ to SQL for counting is very bad

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

gives:

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

Like every fastest way to count the number of records based on primary key

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

So the question is, how can I ensure that LINQ to SQL does the count quickly when I query Count (*)?

+1


a source to share


5 answers


Well, it depends on what you expect. If you ask for "Distinct.Count" on a table, you are instructing LINQ to do exactly what it does. Since this will result in a table scan, it will be slow for large tables.

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

Selecting rows from sysindexes (or preferably: sys.partitions in SQL Server 2005 and up - "sysindexes" views are deprecated) will give you an approximate number, but this is not guaranteed to be completely correct or up to date.



So basically what LINQ is missing is "UseApproxixForForformanceSake". This can be useful sometimes, but again, you can always use this little piece of SQL and query the database yourself if you want a quick, rough answer.

Mark

+2


a source


I had some similar problem, I tried them and worked better:

child.count (x => x.paretnID == inputParentID) child.where (x => x.parentID == inputParentID)

my original code, which took about 15-20 seconds per iteration, was: return (isEdit)? db.ChasisBuys.Single (x => x.ChasisBuyID == long.Parse (Request.QueryString ["chbid"])). Chasises.Count (y => y.Bikes.Count> 0 & y.ColorID == buyItems [(int) index] .ColorID & y.ChasisTypeID == buyItems [(int) index] .ChasisTypeID) .ToString (): "-";



New code that works well:

    **return (isEdit) ? db.Chasises.Where(x => x.ChasisBuyID == long.Parse(Request.QueryString["chbid"])).Count(y => y.Bikes.Count > 0 && y.ColorID == buyItems[(int)index].ColorID && y.ChasisTypeID == buyItems[(int)index].ChasisTypeID).ToString() : "-";**

There are about 1000 records in the database in the chassis, about 5 in chasisBuys and about 20 on bicycles. I believe Linq to SQL queries do not do pre-evaluations in logical operators, for example if you write "return a & b && c;" if the a statement is wrong, other statements are not evaluated, and I expected such a thing in linq for sql, but it is not.

+1


a source


I cannot reproduce your TSQL; are you missing the LINQ query part (ie "where") from the question? No matter how I try, I end up with a pretty normal TSQL that works great ...

Since the primary key is unique, why not just:

int count = db.LargeTable.Count();

Otherwise; have you tried choosing the primary key?

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


a source


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

Should form the following SQL

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

To do this, use an index scan instead of a table scan. Which should be much faster.

0


a source


If you're looking for a simple COUNT that's all I need and don't want to wait long, then don't use Entity methods.

Use a simple LINQ to SQL format.

int largeCount = (from o in db.LargeTable
                       where o.SomeField == somVal
                       select o).Count();
0


a source






More articles:


All Articles