Using Connect SQL to local database is generally not recommended. But there are scenarios where we are unable to achieve satisfactory performance with standard Pega objects. Specifically, we use Connect SQL when updating a large number of records between different data tables. Connect SQL examples below perform ~1000 times faster when updating tens of thousands of records. How can we accomplish these types of data operations without Connect SQL and without looping through records one at a time?

{SQLPage:SQLErrorPage} /* Set value in one table from another table using UPDATE JOIN */ UPDATE {class:XXX-FW-Data-MasterList} ML     SET amount = FA.amount FROM {class:XXX-FW-Data-ForeignAmount} FA WHERE ML.awardcd = FA.award     AND ML.yearcd = FA.year     AND ML.quartercd = FA.quarter;

{SQLPage:SQLErrorPage} /* Count records in one table, save result to another table using NESTED UPDATE */ UPDATE {class:XXX-FW-Data-MasterList} ML SET DayCnt = (     (SELECT COUNT(*) FROM {class:XXX-FW-Data-Calendar}                           WHERE Calendar = ML.WorkingCalendarCd      AND CalendarStartDt BETWEEN ML.StartDt AND ML.EndDt     ) );

***Edited by Moderator: Pallavi to update platform capability tags***