Solved

VB.net Record Count taking long for large tables in SQL

Posted on 2014-10-10
5
477 Views
Last Modified: 2014-10-10
sSQL = "SELECT Count(*) As MyCount FROM [" & oTableName & "]"
Hi
I am using the following code to count the number of records in my SQL tables.
For larger tables of 5 million records or more it seems to take very long
Is there a faster way to do this?
           
    Dim cn As New SqlConnection(oConnectionString)
                Dim cmd As New SqlCommand(sSQL, cn)
                cn.Open()

                oCount_Records_InTable = cmd.ExecuteScalar.ToString

                If cn.State <> ConnectionState.Closed Then
                    cn.Close()
                End If
0
Comment
Question by:murbro
5 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 125 total points
ID: 40372413
Might it be quicker, instead of using Count(*), to use Count(ID), where ID is an indexed field in your table?
0
 
LVL 11

Assisted Solution

by:louisfr
louisfr earned 125 total points
ID: 40372465
count(*) and count(id) don't return the same count if id is nullable.
Anyway, in the absence of a where clause or a join, a count(*) should be very quick, and quicker than any other count(xxx).
Which database are you using?
0
 
LVL 33

Assisted Solution

by:ste5an
ste5an earned 125 total points
ID: 40372470
Run the SELECT COUNT(*) in SSMS and post the actual execution plan. Normally this is caused by table or index fragmentation. Or you have a blocking situation. When it is not necessary to get the exact count, you may query sys.dm_db_partition_stats:

SELECT  * ,
        Q.page_count * 8 / 1024 AS MB
FROM    ( SELECT    S2.[name] AS schema_name ,
                    T.[name] AS table_name ,
                    SUM(S.row_count) AS row_count ,
                    SUM(S.used_page_count) AS page_count ,
                    CASE SUM(S.used_page_count)
                      WHEN 0 THEN 0
                      ELSE SUM(S.row_count) / SUM(S.used_page_count)
                    END AS rows_per_page
          FROM      sys.dm_db_partition_stats S
                    INNER JOIN sys.tables T ON T.object_id = S.object_id
                    INNER JOIN sys.schemas S2 ON S2.schema_id = T.schema_id
          WHERE     ( S.index_id = 0
                      OR S.index_id = 1
                    )
          GROUP BY  S2.[name] ,
                    T.[name]
        ) Q
ORDER BY Q.page_count DESC;

Open in new window

0
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 40372491
You can try COUNT(1) instead of COUNT(*) since you don't want really to work with columns but records.
In a table with 5 million records isn't a good idea to have a SELECT statement without a WHERE clause since you won't be able to use indexes (only clustered index) so if you has a regular reindex task then you can query for the number of rows in the sysindexes table:
SELECT rows
FROM sys.sysindexes
WHERE id=OBJECT_ID(N'YourTableNameHere') AND indid=0

Open in new window

0
 

Author Closing Comment

by:murbro
ID: 40373066
Thank you all for the help
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question