Using query persistence for efficient subquery evaluation in federated databases
View Patent ↗The processing of a query that is submitted to a central controlling server where the central controlling server sources data from one or more backend databases and where such data is referenced by subqueries associated with the query is disclosed.
1. A computer-implemented method comprising:
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 said data stored in said first backend database, wherein
said executing said subquery comprises
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.
2. The method of claim 1 further comprising:
performing further processing on said first set of results at said front-end database server if needed before said act of storing.
3. The method of claim 1 further comprising:
pushing said modified query to said second backend database.
4. The method of claim 1 further comprising:
executing said modified query at said second backend database to obtain a second set of results.
5. The method of claim 4 further comprising:
returning said second set of results to said front-end database server.
6. The method of claim 1 further comprising:
deleting said temporary table when said temporary table is no longer needed.
7. The method of claim 1 , further comprising:
if there are more than one subquery in said query wherein each of said more than one subquery references data in a corresponding one of a plurality of backend databases, then storing a corresponding set of results associated with each of said more than one subquery into corresponding temporary tables in said second backend database.
8. 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.
9. The computer-readable medium of claim 8 further comprising instructions, wherein execution of said instructions by said one or more processors causes said one or more processors to perform the acts of:
performing further processing on said first set of results at said front-end database server if needed before said act of storing.
10. The computer-readable medium of claim 8 further comprising instructions, wherein execution of said instructions by said one or more processors causes said one or more processors to perform the acts of:
pushing said modified query to said second backend database.
11. The computer-readable medium of claim 8 further comprising instructions, wherein execution of said instructions by said one or more processors causes said one or more processors to perform the acts of:
executing said modified query at said second backend database to obtain a second set of results.
12. The computer-readable medium of claim 11 further comprising instructions, wherein execution of said instructions by said one or more processors causes said one or more processors to perform the acts of:
returning said second set of results to said front-end database server.
13. The computer-readable medium of claim 8 further comprising instructions, wherein execution of said instructions by said one or more processors causes said one or more processors to perform the acts of:
performing clean up by deleting said temporary table when said temporary table is no longer needed.
14. The computer-readable medium of claim 8 further comprising instructions, wherein execution of said instructions by said one or more processors causes said one or more processors to perform the acts of:
if there are more than one subquery in said query wherein each of said more than one subquery references data in a corresponding one of a plurality of backend databases, then storing a corresponding set of results associated with each of said more than one subquery into corresponding temporary tables in said second backend database.
15. 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.