We have a call with SAP vendor Team on Attunity consumption.
We got a request to change the Query what Attunity is running to get the Max Row ID value to improve CPU consumption and Execution time. Please find the attached consumption details.
This is in production environment. We want R&D to look in this and provide fix ASAP as this will help across all the projects which we are using SAP HANA Connector.
Please let me know if you want us to create a incident with P2 or open a feature request.
21.9% of the CPU consumed by the ATTINUTY query and Average execution time 2569645(ms)
Current Query:
SELECT MAX("$rowid$") FROM "ATNTYREPUSR4"."attrep_cdc_log"
Suggested Query:
select max_rowid from m_cs_tables where schema_name = 'ATNTYREPUSR4' and table_name = 'attrep_cdc_log'
Test Results:
Results are following:
STATEMENT_HASH | STATEMENT_STRING | AVG_EXECUTION_TIME (microseconds) |
32ea944315d81af1c7c4b08ffda28a4a | SELECT MAX("$rowid$") FROM "ATNTYREPUSR4"."attrep_cdc_log" | 2569645 |
c72e7372bd5ce9e2c2ab0ddf60d95591 | select table_name, schema_name, max_rowid from m_cs_tables where schema_name = 'ATNTYREPUSR4' and table_name = 'attrep_cdc_log' | 14034 |
select statement_hash, statement_string, execution_count, avg_execution_time from m_sql_plan_cache where statement_hash in ('c72e7372bd5ce9e2c2ab0ddf60d95591', '32ea944315d81af1c7c4b08ffda28a4a');
Using m_cs_columns view is 183 times faster. However it works only for non-partitioned tables.
Regards,
Venkat
