A better way to count(*)

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.