Query slow performance sys_databases.m_backup_catalog
We are in the process of deploying Hana and have been playing around with a couple of VM's just to get familiar with HANA before we get our real hardware in a few weeks. I realize that the VM does not meet specs etc but I am not sure this is related to that.
The VM I am working on is a multi-tenant install with 3 tenants. Version 1.00.110 on Suse Linux 3.0.101. The tenants are essentially empty other than the few tables we have created.
Noticed something today that I was not expecting when running a query against sys_databases.m_backup_catalog. The actual query I ran is more complex than the one below but the one below shows the issue. It is taking 7 minutes to query sys_databases.m_backup_catalog.
If I query the tenant's m_backup_catalog directly it runs in < 1 second.
Is that expected behavior? I understand it would be slower because of the combined nature of the sys_databases
view but 7 minutes seems excessive. I have not spent a lot of time looking at the explain plan or anything yet
was just curious if this was to be expected. Part of what lead to this was that using the backup and recovery console
from SYSTEMDB to look at one of the tenants exhibits the same behavior where it takes many minutes for the gui to refresh.
The commands below were run consecutively and used the time command just to show the elapsed time.
select count(*) from sys_databases.m_backup_catalog
where database_name = 'SCOTT';
time hdbsql -d systemdb -u system -p XXXXXXXXXXXXXX -I ./count1.sql -o /tmp/count1.out
select count(*) from m_backup_catalog;
time hdbsql -d scott -u system -p XXXXXXXXXXXXX -I ./count2.sql -o /tmp/count2.out
Repeating the first query results in slightly better time of 5 minutes but still a lot slower than the one directly in the tenant.