?
Solved

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

Posted on 2014-10-10
5
Medium Priority
?
498 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:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 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 500 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 35

Assisted Solution

by:ste5an
ste5an earned 500 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 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 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:Murray Brown
ID: 40373066
Thank you all for the help
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

764 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