Accurate recordcount

Accurate recordcount

Post by Anna Li » Sat, 09 Feb 2002 10:38:41



Hi,

We are running a data warehouse. We load data from other
systems each night and have to report on data activities.

In the beginning, we retrieved the record count for each
table using what's in the sysindex table. We set the
database to automatically update and create statistics.
The counts were consistently accurate.

But lately, without the auto update and create statistics
being changed, the recordcount in the sysindex table is
no longer accurate. It will be difficult for us to do
a "select count(*)" since the tables are large, some have
more than 100 million records.  

How can we get an accurate record count efficiently?

Thanks, Anna

 
 
 

Accurate recordcount

Post by Andrew J. Kell » Sat, 09 Feb 2002 12:30:46


I think the only way to guarantee accuracy is to use COUNT() against the
table (or index).   The stats may be correct or may not be but you can never
really guarantee.  When you have 100M rows does the count really need to be
exact?

--
Andrew J. Kelly,  SQL Server MVP
TargitInteractive

Please join me at PASS North America in Denver - the first and only user
conference dedicated to SQL Server.
http://www.sqlpass.org/events/denverjan/index.cfm


Quote:> Hi,

> We are running a data warehouse. We load data from other
> systems each night and have to report on data activities.

> In the beginning, we retrieved the record count for each
> table using what's in the sysindex table. We set the
> database to automatically update and create statistics.
> The counts were consistently accurate.

> But lately, without the auto update and create statistics
> being changed, the recordcount in the sysindex table is
> no longer accurate. It will be difficult for us to do
> a "select count(*)" since the tables are large, some have
> more than 100 million records.

> How can we get an accurate record count efficiently?

> Thanks, Anna


 
 
 

1. Acquiring an accurate recordcount.

I am using an Access Project using SQL Server 2000 and am
having a difficult time getting accurate record counts of
large recordsets (over 1000 records). I use the following
script to populate a caption on a form but for those large
recordsets the count displayed varies.

Public Sub prcPostRecordCount()
    Dim rst As ADODB.Recordset
    Dim lngCount As Long

    Set rst =
      Me.frmSD_RepairParts_subform.Form.RecordsetClone

    lngCount = IIf(rst.EOF, 0, rst.RecordCount)

    Me.lblMessage.Caption = lngCount & " Item(s) Fit The
        Selected Criteria"

    rst.Close
    Set rst = Nothing
End Sub

Is there a way to make the code wait until the recordset
is entirely populated before displaying the recordcount?

Thanks in advance,
Don

2. Data Access Security

3. DAO Recordcount vs. ADO Recordcount

4. simple SELECT

5. My view is not accurate.

6. Commercial Advt: SQL servers cost $20,000 US dollars.

7. Best method of calculating accurate execution time of a Query

8. Traversing Result Sets

9. How to get accurate space utilization on 6.5?

10. Looking For An Accurate Online Access Test

11. Determining Accurate Row Values

12. v$open_cursor accurate?

13. Most accurate Version of 4GL?