Database query table substitution
View Patent ↗The subject mater herein relates to requesting data from a database and, more particularly, to database query table substitution. Various embodiments provide systems, methods, and software to evaluate requests for data from a database and make table substitutions in the request to reduce a number of tables queried. Some embodiments include receiving a data request including data from two or more database table columns and building a list of all database tables specified in the data request. Some such embodiments further include, for each column in the data request, identifying each table from the list of all tables within which the column is present and identifying a least number of one or more tables that together include all of the columns of the data request. The data request may then be modified by changing the table of one or more of the columns in the received data requests.
1. A computerized method of evaluating and modifying a database data request, the method comprising:
receiving a data request identifying data to retrieve from two or more database table columns from two or more database tables, the data request received as a Structured Query Language (SQL) statement;
building, by executing instructions on a computer processor, a list in a memory device of all database tables specified in the data request;
for each column in the data request, identifying each table from the list of all tables within which the column is present;
determining if any of the identified tables are highly utilized tables;
identifying a least number of one or more non-highly utilized tables that together include all of the columns of the data request; and
modifying the SQL statement of the data request by changing the table of one or more of the columns in the received data request to use a least number of highly utilized tables possible.
2. The computerized method of claim 1 , wherein identifying the least number of one or more tables that together include all of the columns of the data request includes identifying two or more tables that may be joined.
3. The computerized method of claim 1 , wherein:
receiving a data request includes receiving a database query; and
modifying the data request includes modifying the database query.
4. The computerized method of claim 1 , further comprising:
forwarding the modified data request to a query generator to build a query encoded in a query language as a function of the data request.
5. The computerized method of claim 1 , wherein the identified tables within which a column is present is a list of tables that are interchangeable and may be substituted for each other to provide data of the column.
6. The computerized method of claim 1 , wherein modifying the data request includes building a query as a function of the received data request and the identified least number of one or more tables.
7. A system comprising:
a processor;
a memory device;
a data storage device;
a database to store tables on the data storage device, the data stored in the tables including columns and rows;
an interface defined by instructions stored in the memory device and executable by the processor to receive data requests for data stored in the database;
a table substitution module defined by instructions stored in the memory and executable by the processor to process data requests received via the interface prior to retrieving data as a function of the data requests, the instructions of the table substitution module executable by the processor to:
build a list of all database tables specified in a data request, the data request received as a Structured Query Language (SQL) statement identifying data to retrieve from two or more database table columns from two or more database tables;
for each column in the data request, identify each table from the list of all tables within which the column is present;
determine if any of the identified tables are highly utilized tables;
identify a least number of one or more non-highly utilized tables that together include all of the columns of the data request; and
modify the SQL statement of the data request by changing the table of one or more of the columns in the received data request to use a least number of highly utilized tables possible.
8. The system of claim 7 , wherein the table substitution module, when identifying the least number of one or more tables that together include all of the columns of the data request identifies two or more tables that may be joined.
9. The system of claim 7 , wherein:
the interface receives data requests including database queries; and
the table substitution module modifies such data requests by modifying the database queries.
10. The system of claim 7 , further comprising:
a query generator to build a query statement encoded in a query language as a function of a data request; and
wherein the table substitution module is further operable to:
forward the modified data request to the query generator.
11. The system of claim 7 , wherein the identified tables within which a column is present is a list of tables that are interchangeable and may be substituted for each other by the table substitution module to provide data of the column.
12. The system of claim 7 , wherein the table substitution module, when modifying the data request, builds a query as a function of the received data request and the identified least number of one or more tables.
13. A non-transitory computer-readable storage medium, with instructions encoded and stored thereon, which when executed, cause a suitably configured computing system, including a database that holds data in tables having columns and rows, to:
build a list of all database tables specified in a data request, the data request received as a Structured Query Language (SQL) statement identifying data to retrieve from two or more database table columns from two or more database tables;
for each column in the data request, identify each table from the list of all tables within which the column is present;
determine if any of the identified tables are highly utilized tables;
identify a least number of one or more non-highly utilized tables that together include all of the columns of the data request; and
modify the SQL statement of the data request by changing the table of one or more of the columns in the received data request to use a least number of highly utilized tables possible.
14. The non-transitory computer-readable storage medium of claim 13 , wherein:
the data request is a database query; and
the instructions when further executed cause the suitably configured computing system to modify the data request by modifying the database query.
15. The non-transitory computer-readable storage medium of claim 13 , wherein the instructions, when further executed, cause the suitably configured computing system to:
forward the modified data request to a query generator to build a query encoded in a query language as a function of the data request.
16. The non-transitory computer-readable storage medium of claim 13 , wherein the identified tables within which a column is present is a list of tables that are interchangeable and may be substituted for each other to provide data of the column.
17. The non-transitory computer-readable storage medium of claim 13 , wherein the instructions to modify the data request include instructions to build a query as a function of the data request and the identified least number of one or more tables.