Click here to Skip to main content
12,897,830 members (76,871 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


4 bookmarked
Posted 7 Jun 2010

A better way to count(*)

, 7 Jun 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
LINQ translates .count() to a SQL query that uses COUNT(*), but on large tables even count(*) can be too slow. Here's another way
We all know
SELECT COUNT(*) AS recCount FROM [myTable];

as a means of getting the number of records in [myTable]. But for very large tables this method can be just too slow, even tripping over default timeouts.

Do a quick search on this problem and you can find an alternative for SQL Server which is substantially faster. It looks a little like this.
USE [myDatabase];
SELECT ISNULL(st.row_count, 0)
FROM [sys].[dm_db_partition_stats] st
WHERE index_id < 2 AND OBJECT_NAME(OBJECT_ID) = 'myTable';

Unfortunately if you use .count() in your LINQ statements you'll also get COUNT(*) in the resulting SQL.

So rather than doing that in your code try the following:
string sqlCount = "SELECT ISNULL(st.row_count, 0)
FROM [sys].[dm_db_partition_stats] st
WHERE index_id < 2 AND OBJECT_NAME(OBJECT_ID) = '||TableName||';";
long myRecCount = dc.ExecuteQuery<long>(sqlCount.Replace("||TableName||", "myTable")).First();

In the above code dc is the datacontext that you've already instantiated.
Substitute the name of the table you need to get the record count for and you'll get the result in lightening quick time.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Lee Humphries
Founder md8n
Australia Australia
If it ain't broke - that can be arranged.

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170424.1 | Last Updated 7 Jun 2010
Article Copyright 2010 by Lee Humphries
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid