Efficiently deleting multiple rows from a database table
I know that we're supposed to avoid RDB methods, but how would you efficiently do the operation below without it?
| DELETE FROM RATINGS.CERT_PER_SCHED_NOTIFICATION where CERT_PER_ID = {PcsDelete.Per} |
I would also suggest browsing the content of the table using "Obj-Browse" and then performing Obj-Delete.
- Using any of the Obj- operations allows you to take advantage of transactions (deferred database transactions) whereas RDB- operations execute immediately.
- When using Obj-Save and Obj-Delete, you can cancel or rollback all of the related operations without committing the changes to the database. RDB- operations commit immediately.
- Obj- methods can be used to retrieve data from the BLOB (pzpvstream column) and save data to the BLOB. So they can be used for properties that are not exposed along with the exposed properties. While RDB- methods cannot read from or update the BLOB. So if our table has the pzpvstream (BLOB) column then we should always use Obj- methods. This will work on the exposed columns as well as the BLOB.
Benefit of RDB:
- RDB methods support stored procedures. OBJ methods do not.
- RDB method support hand-crafted / optimized SQL. OBJ methods generate all SQL on the fly behind the scenes.
Refer it for more details:- http://myknowpega.com/2017/08/09/obj-rdb-methods/
Hope it helps you in the understanding concept behind both the methods and preferences when RDB or Obj methods should be used. Kindly acknowledge if it helps by marking this reply as helpful/answered for larger Pega audience and tracking purposes.
Regards,
Asif
I am concerned about performance. To me, doing Obj-Browse, looping through deferred Obj-Deletes, has to take more time than a single SQL statement.
I am not sure, which version you are using, if you are using 7.1 or above try
DataSet-Execute method and see if you can pass page list for "Delete by keys".
https://community.pega.com/sites/default/files/help_v74/procomhelpmain…
Hi
The one way I see is, to do a Obj-Browse first on the where clause and get the key also.
Then do an Obj-Delete individually while iterating. Based on the design you can let pega take cake of committing to the database.
Is there anything specific you are looking for?
Regards
Bhavya