Using query persistence for efficient subquery evaluation in federated databases

A computer-readable medium storing one or more sequences of instructions, wherein execution of said one or more sequences of instructions by one or more processors causes said one or more processors to perform the acts of:

• receiving a query at a front-end database server, wherein
• said query references data stored in a first backend database and a second backend database, and
• said query comprises a subquery referencing data stored in said first backend database;
• if said query cannot be executed, in its entirety, on said data stored in said first backend database, executing said subquery on data stored in said first backend database, wherein
• said executing said subquery is performed by
• pushing said subquery to said first backend database for execution, if said first backend database supports functions in said subquery and includes data referenced by said subquery, and
• pulling into said front-end database server data referenced by said subquery from said first backend database, if said first backend database does not support said functions in said subquery;
• storing a first set of results from execution of said subquery into a temporary table in said second backend database
• when a persist-subquery option is activated,
• when said second backend database supports a subquery predicate, and
• when there is a valid connection pool with a write permission to said second backend database;
• modifying said query to reference said temporary table, thereby generating a modified query;
• executing said modified query on data stored in said second backend database;
• and
• displaying results of said executed modified query.

A system comprising:

• a front-end database server, wherein
• said front-end database server is configured to receive a database query, wherein
• said database query references data stored in a first backend database and a second backend database, and
• said database query comprises a subquery referencing data stored in said first backend database,
• said front-end database server is further configured to
• push said subquery to said first backend database server if said first backend database supports functions in said subquery and includes data referenced by said subquery, and
• pull from said first backend database server data referenced by said subquery if said first backend database server does not support functions in said subquery;
• a first backend database server configured to access said first backend database and to execute said subquery on data stored in said first backend database, if said query cannot be executed, in its entirety, on said data stored in said first backend database;
• a second backend database server configured to
• access said second backend database,
• store a first set of results from an execution of said subquery into a temporary table in said second backend database when a persist-subquery option is activated,
• when said second backend database supports a subquery predicate, and
• when there is a valid connection pool with a write permission to said second backend database,
• execute a modified query on data stored in said second backend database, wherein said modified query references said temporary table, and
• provide results from execution of said modified query to said front-end database server.