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
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()?
No indirection.
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 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.
Are a couple of points:
- The macro "ClassSQLTable" is defined in the method %Library.EnsembleMgr:UpgradeUTCIndices
- I would prefer to use ##class(%DeepSee.Utils).%GetSQLTableName(pClass As %String, pVerifyExists=0)
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
If you don't have a standard ID (%Integer,MINVAL=1)
but something like ^<dataglobal>(BRANCH,ID) or similar than you might get your fast count by applying
The adopted Bitmap
Adopted Bitmaps example now on Open Exchange
ant iterate over the "BRANCHES"
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:
<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>Nice! Thank you, Vitaly!