Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-10-10
5
Medium Priority
?
512 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
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 12

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 36

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 53

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

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.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

577 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