ROWCOUNT field in SYSTABLES table not updated after rows have been deleted

Question

I use the ROWCOUNT field in SYSTABLES to get fast the rowcount of all my tables from a .NET App using Oledb (i don't want to use a query to get it becasue it takes too much time on big tables).

The problem is: after deleting rows from the table, that ROWCOUNT number in SYSTABLES is not updated. I tested some commands and even found that running ROWCOUNT TABLENAME in SqlTalk works and is very fast, but if i try to call that as a query from .NET using OLEDB it's returning nothing, sample code:

using (var connection = ConnectionFactory.CreateConnection(NombreBaseModelo))
{
    using (OleDbCommand cmd = connection.CreateCommand())
    {
        foreach (Tabla ot in LstTables)
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 5000;
            cmd.CommandText = "ROWCOUNT " + ot.NAME;
            var sRet = cmd.ExecuteScalar();
            ot.ROWCOUNT = int.Parse(sRet);
        }
    }
}

Is there any way to tell SqlBase to update Rowcount for each table in systables?

Or as alternative, is there any way to make this code work?

Thanks!

Answer 1

The systables.rowcount only gets updated when an update statistics is done, so it's not guaranteed to be accurate, until you execute 'Update Statistics on table ' + ot.NAME; Then it is .

Probably not what you want when quickly counting rows.

Does your 'ot.NAME' variable have a table owner included ? usually its 'SYSADM.' Have you checked the value returned in 'sRet' , as maybe its 'int.Parse(sRet)' that is failing. Otherwise create an index on the PK and execute a COUNT(*) . Should be as fast as ROWCOUNT anyway if the index is being used .

Alternatively, write a SQLBase function or stored proc that just executes the ROWCOUNT command natively and returns the correct count ( as per SQLTalk ), and call that from your , Net app