Written by

Senior Startups and Community Programs Manager at InterSystems Corporation
Question Evgeny Shvarov · May 17, 2020

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

Hi!

Is there a method in a persistent class that can return the current number of records/persistent objects in it?

Comments

Stephen Canzano · May 17, 2020

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.

0
Eduard Lebedyuk · May 17, 2020

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
}
0
Evgeny Shvarov  May 17, 2020 to Eduard Lebedyuk

Thanks, Ed!

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

0
Evgeny Shvarov  May 17, 2020 to Eduard Lebedyuk

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

0
Robert Cemper  May 17, 2020 to Evgeny Shvarov

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

0
Robert Cemper · May 17, 2020

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
 

0
Eduard Lebedyuk  May 17, 2020 to Robert Cemper

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.

0
Evgeny Shvarov  May 17, 2020 to Robert Cemper

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

0
Vitaliy Serdtsev  May 18, 2020 to Evgeny Shvarov

Absolutely true.

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

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}

}

0
Vitaliy Serdtsev  May 20, 2020 to Evgeny Shvarov

It can be simpler:

<FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">HowManyRecords </FONT><FONT COLOR="#000080">As %Integer </FONT><FONT COLOR="#000000">[ </FONT><FONT COLOR="#000080">Calculated</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">ReadOnly</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">Required</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">SqlComputeCode </FONT><FONT COLOR="#000000">= {</FONT><FONT COLOR="#0000ff">n </FONT><FONT COLOR="#800000">r</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">SQLCODE </FONT><FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">count</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080"></FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#800000">:r </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">schema_package</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">table</FONT><FONT COLOR="#800080">) </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800080">{}</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$s</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">SQLCODE</FONT><FONT COLOR="#000000">:-1,1:</FONT><FONT COLOR="#800000">r</FONT><FONT COLOR="#000000">)}, </FONT><FONT COLOR="#000080">SqlComputed </FONT><FONT COLOR="#000000">];</FONT>
0
Evgeny Shvarov  May 20, 2020 to Vitaliy Serdtsev

Nice! Thank you, Vitaly!

0