Solved

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

Posted on 2014-10-10
5
479 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
[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 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 49

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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

730 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