Hi,

Obj methods are always the first choice. However, there may be times when you need to opt for RDB methods. In this post, I will share such use cases based on my experiences.

1. Differences between Obj methods and RDB methods

Some people seem to believe that Obj methods must be exclusively used for internal table, and RDB methods must be exclusively used for external table. This is not correct. Obj methods can be used for both internal and external tables. Traditional relational tables without Blob in customer's external database site can be accessed by Obj methods. Data Type tables created in "CustomerDATA" have no Blob columns and this can be also accessed by Obj methods.

Obj methods supports transaction management or deferred save while in RDB methods update is auto-committed implicitly in the step right away. This may sometimes cause inconvenience - for example, when you want to manage transaction on multiple tables, if the first update fails for whatever reasons, then you want to rollback and cancel other update to avoid data inconsistency. This is only manageable in Obj methods. Now, if Obj methods are always recommended, when exactly should we use RDB methods over Obj methods? I will explain four examples below.

2.    Things that only RDB methods can do

2-1. Complex SQL

Obj methods can only search instances of a single class with simple SQL whereas RDB methods can handle complex SQL that includes involving more than one class along with JOIN clause, subqueries, UNION clause, etc. For example, Assign-Worklist and Assign-Workbasket are similar but two separate tables derived from Assign- class. If you want to construct a single Page List of result set combining the data from the result of two tables, you need to write SQL with UNION clause in Connect SQL as below (*). Obj method can't handle this.

* As an alternative, this UNION requirement can be also achieved by creating a View at database level so from PRPC you can easily query against a single class.

2-2. Calling stored procedure

Stored procedure is a group of one or more pre-compiled SQL statements that are stored in a database. In general, stored procedure is not recommended if you are creating it from scratch because this approach will reduce the portability of your Pega application and it is hard to maintain. However, if customer already has lots of stored procedures that interact with their existing database, it will be easier to just reuse their assets (Wrap and Renew).

In order to call stored procedure, you need to use RDB methods (Connect SQL). I have documented how to call stored procedure in a separate article. Please see https://support.pega.com/discussion/how-call-stored-procedure-oracle.

2-3. Calling DBMS function

All database software provides vendor-specific DBMS function. It is not recommended to use it because it will reduce the portability of your Pega application. However, in certain situations you may not have other choice. In my experience, I had to use Oracle's function to convert character set of text that are integrated from external systems. For example, to convert string "あいうえお" from Shift-JIS to EUC, CONVERT function is required. You need to use Connect SQL to call DBMS function.

SELECT CONVERT('あいうえお', 'JA16EUC', 'JA16SJIS') FROM DUAL;

2-4. Performance

If some of you believe RDB methods are always much faster than Obj methods, that is not true. I have measured the performance of Obj-Browse and RDB-List against very large dataset, but no significant difference was observed. What makes a big difference is when you update or delete multiple records at once filtering by certain conditions. No such equivalent Obj methods are currently available and if you want to accomplish this by Obj methods, you need to first obtain a set of records by Obj-Browse, and then looping over the result set perform acquire lock, update, and commit successively. In Obj methods, acquiring / releasing a lock involves PR_SYS_LOCKS table insert / delete for each record. Also, inserting history records are triggered by itself. On the other hand, in RDB methods, only the main table is updated and no locking or histories are required. If the number of records is a couple of hundreds, Obj method may be acceptable, but if the it is massive, consider switching to RDB methods. I have conducted a quick experiment of DELETE / UPDATE operations against 1,000,000 records to get some idea how much they are different. You can see RDB methods are much faster than Obj methods.

Hope this helps.

Thanks,