New post
Question
Bradley Larson · Dec 8, 2021

Query to return table sizes

#Databases #SQL #Caché

I've been accessing Cache tables from a developer/reporting side, but am now involved in a project to create a data warehouse for our application.  I'm trying to find a query I can use to return the sizes of all the tables in the database, so we can identify the largest tables and handle those individually.  Can someone give me a query I can run against our Cache database to return the sizes of all the tables from largest to smallest?  

Thanks for the help

Product version: Caché 2017.1
0
0 384
Discussion (8)1
Log in or sign up to continue
Timothy Leavitt · Dec 8, 2021

By size, do you mean number of rows or size on disk?

0 0
Bradley Larson · Dec 8, 2021

Size on Disk.  We have some tables with narratives.  While only a single row of data, the text can be pretty large.  Thanks

0 0
Vitaliy Serdtsev · Dec 8, 2021

Will the size of the global (with data,index,stream) suit you? If yes, then see this.

0 0
Bradley Larson · Dec 8, 2021

Sure, I can try it and see.  I am only interested in the table size on disk however (don't need index information or anything else).  Thanks

0 0
Vitaliy Serdtsev · Dec 8, 2021

Ok, the index can be omitted.
The table data is stored in different globals: *D - for data, *I - for indexes, *S - for streams. You can get the size of the data parts you want.

Example [Size()]:

select * from %SYS.GlobalQuery_Size('C:\InterSystems\Cache\mgr\samples','','*D,*S',0,0,1) order by "Allocated MB" desc
0 0
Timothy Leavitt · Dec 8, 2021

Here's a full example relating global size back to related classes/tables, expanding on @Vitaliy.Serdtsev's example:

select sdef.parent "Class", class.SqlSchemaName||'.'||class.SqlTableName "Table", ​LIST(sdef.Location) "Globals", SUM(s."Allocated MB") "Allocated MB", SUM(s."Used MB") "Used MB" from %SYS.GlobalQuery_Size('C:\InterSystems\IRIS20\mgr\user','','*D,*S,*I',0,0,1) s
join (
select parent,DataLocation as Location from %Dictionary.StorageDefinition where not parent %STARTSWITH '%'
union all
select parent,StreamLocation from %Dictionary.StorageDefinition where not parent %STARTSWITH '%'
union all
select parent,IndexLocation from %Dictionary.StorageDefinition where not parent %STARTSWITH '%'
) sdef
on sdef.Location = '^'||s.Name
join %Dictionary.CompiledClass class on class.Name = sdef.parent
group by sdef.parent

Note that if you use inheritance with persistent classes sharing an extent this'll be a little bit off (since the same global is used for multiple classes and will be double counted).

0 0
Timothy Leavitt · Dec 8, 2021

Note, this also assumes default global names - if you have customized these, you'll want to just get all globals. Also, replace 'C:\InterSystems\IRIS20\mgr\user' with the appropriate database directory for your instance.

1 0