How to quickly obtain the number of records in a persistent class?

Primary tabs

Replies

Not to my knowledge.  While there is a global node in the storage map that is used to get the next available Id this would only work on tables/objects based on a single integer id.  At the same time, this is the next available Id and does not account for physical deletes that may have occurred, ie the next Id might be = 101 but you may have fewer than 100 rows/objects as some may have been deleted.  The simplest way to accomplish this would then to perform a SELECT COUNT(*) FROM TableName.  If the table implements this bitmap indices this should be ms.  If you don't get the performance you want you might consider adding %PARALLEL to the FROM clause and let the optimizer decide if it makes sense to split the job.

If records are never deleted and you're okay with a few misses, use ExtentSizeFast, if you need the fastest precise runtime implementation use ExtentSize, in you need better compile speed and don't care about runtime speed use GetExtentSize.

/// w ##class(Utils.Persistent).GetGlobal("Utils.Persistent")
ClassMethod GetDataGlobal(Class As %Dictionary.CacheClassname) As %String
{
    Quit:'$$$comClassDefined(Class) ""
    Set Strategy = $$$comClassKeyGet(Class, $$$cCLASSstoragestrategy)
    Quit $$$defMemberKeyGet(Class, $$$cCLASSstorage, Strategy, $$$cSDEFdatalocation)
}

/// w ##class(Utils.Persistent).ExtentSizeFast("Utils.Persistent")
ClassMethod ExtentSizeFast(Class As %Dictionary.CacheClassname) As %String [ CodeMode = expression ]
{
$Get(@..GetDataGlobal(Class), 0)
}


/// w ##class(Utils.Persistent).GetExtentSize("Utils.Persistent")
ClassMethod GetExtentSize(Class As %String) As %Integer
{
    Set Global = ..GetDataGlobal(Class)
    Quit:Global="" 0
   
    Set Id = $Order(@Global@(""))
    Set Count = 0
    While Id '= "" {
        Set Id = $Order(@Global@(Id))
        Set Count = Count + 1
    }
    Quit Count
}

ClassMethod ExtentSize() As %Integer [ CodeMode = objectgenerator ]
{
    set Strategy = $$$comClassKeyGet(%classname, $$$cCLASSstoragestrategy)
    set Global = $$$defMemberKeyGet(%classname, $$$cCLASSstorage, Strategy, $$$cSDEFdatalocation)
    Do %code.WriteLine(" Set Id = $Order(" _ Global _ "(""""))")
    Do %code.WriteLine(" Set Count = 0")
    Do %code.WriteLine(" While Id '= """" {")
    Do %code.WriteLine("     Set Id = $Order(" _ Global _ "(Id))")
    Do %code.WriteLine("     Set Count = Count + 1")
    Do %code.WriteLine(" }")
    Do %code.WriteLine(" Quit Count")
    Quit $$$OK
}

Thanks, Ed!

Why does ExtentSize() is faster than GetExtentSize()?

Do we really don't have anything faster than a full scan on ID index?

If you run Tune Table on a regular base it updated EXTENTSIZE.
But this is then not exact but a close estimation depending on the frequency of run

In principle I'd share te suggestion of @Stephen Canzano;

with 2 minor additions: SELECT Count(ID) from <youtable>  will you always lead to the
explicit or implicit Extent Index.  (bitmap or standard)

set cls=##class(%Dictionary.CompiledClass).%OpenId("classname")
set table=clsSqlTableName

This is important as there are a bunch of classes that have explicit defined table names.

In addition, it works also for table linked over SQL gateway
 

You can use this macro to translate class name into table name.

set table = ##class(%CSP.UI.Portal.SQL.Home).Quoter2($$$ClassSQLTable(class))

It's faster (no object access) and quotes table name if required.

And if I make the Extent index a bitmap will it be faster to get the amount of records?

Absolutely true.

InterSystems SQL uses this index to improve performance of COUNT(*), which returns the number of records (rows) in the table. proof

So, assuming @Vitaliy Serdtsev and @Robert Cemper answers we have:

Index ext [type = bitmap; Extent];

Is the must for every Persistent class, with standard ID, if we want to have fast answer on "How many records"

And the easiest and fastest "How many records" in this case is:

ClassMethod HowManyRecords(ByRef recordsCount as %Integer) As %Status

{
&sql(SELECT Count(1) INTO :recordsCount FROM schema_package.table)

IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg QUIT}

}

It can be simpler:

Property HowManyRecords As %Integer CalculatedReadOnlyRequiredSqlComputeCode = {r,SQLCODE &sql(select count(*into :r from schema_package.table{*}=$s(SQLCODE:-1,1:r)}, SqlComputed ];