I spent around a day looking into a performance issue with a specific peice of SQL in an ERP 6 system running on a SAP ASE database.
The system has recently been migrated from Oracle, so we were expecting issues with hints, however this didn’t seem to be an index choice issue.
Look at the following two SQL statements, the first one is the system experiencing a performance problem:
The second picture is a system where the performance issue doesn’t exist:
Can you spot the difference?
Hint: Look at the number of question marks in the prepared statement.
The number of question marks indicate the number of items included in the “IN LIST” of the WHERE clause.
Since the ABAP SQL statement will be interpreted at the Kernel level, there is no way to see any difference in the ABAP layer other than this output from a SQL trace.
The consequence of the first statement (with fewer question marks) are that the SQL statement is executed multiple times in order to query for the same “PERNR” records. This can result in as much as 4 to five times more effort for the SAP layer, plus the database layer. Which adds more to the database response time and a little to the “processing time”.
What impacts the number of question marks? Simple, the parameters “rsdb/*blocking_factor” at the Kernel level, will adjust how many parameters are fed into the prepared statement in the DBSL layer.
SAP Note 1996340 – SYB: Default RSDB profile parameters for SAP ASE will provide all the answers.
The SAP note also answered my specific issue, which was why was one system in the landscape different. The answer was that the production system (where the problem was seen) had it’s parameters mostly carried across during a migration from Oracle. Whereas a smaller “release” system had it’s parameters left behind to die with the Oracle database.
As you will read in the SAP note 1996340, these rsdb parameters are pretty essential and should be re-evaluated when changing database platform.
Always re-evaluate all parameters when migrating from one platform to another.
Don’t assume that someone more experienced has set them with some future knowledge of the landscape/setup.