SQL for all entries performance

[former_member600392]

Hi All,

We have a select query with for all entries on EQUZ(table does not matter) which is behaving differently in quality and acceptance. We are passing the HEQUI in where condition of the select query(Index is available and is also used). The performance is considerably slow in acceptance and we have about same number of records in quality and acceptance on EQUZ table.

We did a trace on both system and found out that on quality the select is hitting the data base 50 entries at a time from the for all entries table.

But on acceptance the same select is hitting the database 5 entries at a time and because of this the number of executions are high and the performance is slow in acceptance.

Is there any setting that controls this behaviour?

[former_member600392]

this is the trace output

Quality

SELECT "EQUNR","HEQUI" FROM "EQUZ" WHERE "MANDT"=:A0 AND "EQUNR" IN (:A1,:A2,:A3,:A4,:A5,:..... A50) AND "DATBI">=:A51 AND "DATAB"<=:A52 AND "HEQUI"<>:A53

Acceptance

SELECT "EQUNR","HEQUI" FROM "EQUZ" WHERE "MANDT"=:A0 AND "EQUNR" IN (:A1,:A2,:A3,:A4,:A5) AND "DATBI">=:A6 AND "DATAB"<=:A7 AND "HEQUI"<>:A8

[Sandra_Rossi]

Transaction code RZ11, check the profile parameters rsdb/max_blocking_factor and rsdb/max_in_blocking_factor

[former_member600392]

Thank you Sandra. That helped

[Sandra_Rossi]

@ rahul please give more details to other people having the same issue in the future

[former_member600392]

after changing the parameter rsdb/max_in_blocking_factor from 5 to 50, we have notice that system is now hitting the database with 50 entries at a time and performance improved significantly. However we do not want to impact the system by changing this parameter for the sake of few queries and we are going to use DBHints in select query instead of changing the parameter rsdb/max_in_blocking_factor.

[Sandra_Rossi]

Thanks for the feedback !