Click here to Skip to main content
Click here to Skip to main content

Tagged as

A better way to count(*)

, 7 Jun 2010
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.

License

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

Share

About the Author

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

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140821.2 | Last Updated 7 Jun 2010
Article Copyright 2010 by Lee Humphries
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid