topic Re: SQL for all entries performance in Application Development and Automation Discussions

SQL for all entries performance

former_member600392 — Fri, 11 Jan 2019 11:19:17 GMT

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?

Re: SQL for all entries performance

former_member600392 — Fri, 11 Jan 2019 11:23:21 GMT

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

Re: SQL for all entries performance

Sandra_Rossi — Fri, 11 Jan 2019 14:53:52 GMT

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

Re: SQL for all entries performance

former_member600392 — Fri, 11 Jan 2019 17:00:39 GMT

Thank you Sandra. That helped

Re: SQL for all entries performance

Sandra_Rossi — Fri, 11 Jan 2019 18:45:42 GMT

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

Re: SQL for all entries performance

former_member600392 — Thu, 17 Jan 2019 11:18:48 GMT

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.

Re: SQL for all entries performance

Sandra_Rossi — Thu, 17 Jan 2019 11:50:00 GMT

Thanks for the feedback !