US 11,941,001 B2
Optimizing cursor loops in relational database systems using custom aggregates
Karthik Saligrama Ramachandra, Bangalore (IN); Surabhi Gupta, Bangalore (IN); and Sanket Jayant Purandare, Boston, MA (US)
Assigned to MICROSOFT TECHNOLOGY LICENSING, LLC, Redmond, WA (US)
Filed by Microsoft Technology Licensing, LLC, Redmond, WA (US)
Filed on Sep. 14, 2022, as Appl. No. 17/932,173.
Application 17/932,173 is a continuation of application No. 16/938,585, filed on Jul. 24, 2020, granted, now 11,481,390.
Prior Publication US 2023/0026458 A1, Jan. 26, 2023
This patent is subject to a terminal disclaimer.
Int. Cl. G06F 16/00 (2019.01); G06F 8/41 (2018.01); G06F 8/75 (2018.01); G06F 16/242 (2019.01); G06F 16/2452 (2019.01)
CPC G06F 16/24522 (2019.01) [G06F 8/41 (2013.01); G06F 8/75 (2013.01); G06F 16/244 (2019.01)] 20 Claims
OG exemplary drawing
 
1. A system for converting a cursor loop to a declarative structure query language (SQL) query that invokes a custom aggregate function, the system comprising:
one or more processors; and
one or more memory devices that store program code to be executed by the one or more processors, the program code comprising:
a precondition verifier instantiated in a SQL database server that:
receives the cursor loop for execution from a user defined function or a procedure stored in the SQL database server, wherein the received cursor loop comprises a select query and a loop body including a program fragment that can be evaluated iteratively over a result set one row at a time, and wherein the result set is a result of execution of the select query of the received cursor loop; and
verifies that the loop body of the received cursor loop does not include operations that modify a persistent state of the database;
a custom aggregate function constructor instantiated in the SQL database server that automatically constructs the custom aggregate function based on semantics of the loop body of the received cursor loop according to a contract, wherein the automatically constructed custom aggregate function expresses the loop body of the received cursor loop;
a query rewrite engine instantiated in the SQL database server that automatically rewrites the select query to the declarative SQL query that invokes the automatically constructed custom aggregate function.