Close popover
Stan Lovric (StanL080)
Intel Corporation

Intel Corporation
StanL080 Member since 2018 1 post
Intel Corporation
Posted: June 11, 2020
Last activity: June 26, 2020

Connect SQL to local database for update performance

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***  

Pega Platform 8.1.3 Enterprise Application Development Dev/Designer Studio Data Integration Manufacturing Senior System Architect