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

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
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Might it be quicker, instead of using Count(*), to use Count(ID), where ID is an indexed field in your table?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
louisfrCommented:
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
ste5anSenior DeveloperCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you all for the help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.