When would you choose RDB-List over Report Definition in a high-performance system?

General
, ,
1

Hi,

I had a question based on my recent project experience.

In our implementation, we are using RDB-List with custom SQL joins extensively, and very minimal usage of Report Definitions. This made me wonder about the design decision behind it.

From what I understand, Report Definitions can also handle joins (via associations) and data access in a more Pega-aligned way. However, in our case, everything is handled through RDB-List.

So I wanted to understand:

  • When would you intentionally choose RDB-List over Report Definition in a high-performance system?

  • Is it mainly for complex joins / query optimization / DB-level control, or are there other reasons?

  • Could the same use cases be handled using Report Definitions + associations/data pages, or are there limitations?

  • What are the trade-offs in terms of maintainability, guardrails, and performance?

1 Like
2

I’m not currently in the development role, but I was previously working on a project that was extensively relying on database and running very complex and heavy database queries. I can share my experience how I dealt with the requirements and how I managed to make these queries performing well and scaling well with the growing amount of data. 1) I tried to use Report Definitions only as I was told anything else wasn’t a best-practice. 2) I first tried to design a SQL-query directly. 3) I learned and understood what each control and checkbox in Report Definition form was reflecting into in SQL query. 4) I created Report Definition that was generating the exact SQL query that I designed previously. 5) I analyzed explain plans to find out inefficiencies and created the necessary indexes and constraints. Not all functionality of database is available in Report Definition, but I would say 80%-90% can be done through Report Definitions. For the performance aspect I highly recommend reading the book SQL Performance Explained by Markus Winand. It doesn’t have any fluff - very practical hands-on book that allowed me to understand and tune performance for successful go-live (as an LSA) and after. I wish every Pega developer to read it honestly.

1 Like
3

Hi @PoojaPalla : In Pega, Report Definitions are the preferred standard for retrieving data because they are easier to maintain and leverage Pega’s built-in optimizations. However, RDB methods (Connect-SQL) are necessary in specific scenarios like executing stored procedures. Please do also note that this is not supported in Pega Cloud.

Though you have this option to use RDB methods, the trade-offs are -

  • Reduced Portability because the SQL you write will be database-specific
  • High chances of introducing SQL Injection
  • Issues with transaction handling because RDB methods will use auto-commit

Thanks.

4

Thanks for sharing your experience - really like the approach of designing SQL first and then aligning RD to it

5

Hi @ArulDevan , Thanks for the insights - that makes sense

I agree RD should be the default, and RDB methods seem more like a last resort for specific cases like stored procedures or DB-specific logic.

6

As previously mentioned, there are no performance gains just because you are using RDB-list and not RD. Having said that to tweak performance for complex queries you might need to use RDB-list if similar outcome is not possible via Report Definition.