System and method for SQL query load balancing
Systems and methods for SQL query load balancing are disclosed. A method may include establishing a ranking scheme, assigning a rank to an SQL query from the ranking scheme based on a plurality of rank assignment parameters, determining a threshold rank based on a plurality of gathered operational characteristics, comparing the assigned rank to the threshold rank, and, if the assigned rank is greater than the threshold rank, executing the SQL query on a database server. Otherwise, if the assigned rank is not greater than the threshold rank, executing the SQL query on a centralized storage system.
1. A method for allocating the execution of a database query in a system comprising a database server and a storage system communicatively coupled to the database server via a communication network, comprising:
establishing a ranking scheme comprising a plurality of rank values;
assigning a first rank from the plurality of rank values to the database query, comprising:
determining whether the database query comprises at least an outer table query and an inner table query;
if the database query comprises at least an outer table query and an inner table query, assigning the first rank to an outer table query of the database query and assigning a second rank to the inner table query of the database query;
determining a threshold rank from the plurality of rank values;
comparing the first rank to the threshold rank;
if the first rank is greater than the threshold rank, communicating the database query to the database server for execution; and
if the first rank is not greater than the threshold rank, communicating the database query to the storage system for execution.
2. The method of claim 1 , wherein assigning the second rank to the inner table query of the database query comprises:
determining whether the inner table query uses an index scan; and
if the inner query uses an index scan, assigning the highest value in the ranking scheme to be the value of the second rank; and
if the inner query does not use an index scan, assigning the second rank from a second plurality of rank values, wherein the second plurality of rank values are based at least on the number of rows resulting from the outer query of the database query.
3. A system for allocating the execution of a database query between a database server and a storage system coupled to the database server via a communication network, comprising a load balancing module communicatively coupled to the database server, the load balancing module configured to:
establish a ranking scheme comprising a plurality of rank values;
assign a first rank from the plurality of rank values to the database query;
determine whether the database query comprises at least an outer table query and an inner table query;
if the database query comprises at least an outer table query and an inner table query, assign the first rank to an outer table query of the database query and assign a second rank to the inner table query of the database query;
determine a threshold rank from the plurality of rank values;
compare the first rank to the threshold rank; and
if the first rank is greater than the threshold rank, communicate the database query to the database server for execution; and
if the first rank is not greater than the threshold rank, communicate the database query to the storage system for execution.
4. The system of claim 3 , wherein the load balancing module is further configured to:
determine whether the inner table query uses an index scan; and
if the inner query uses an index scan, assign the highest value in the ranking scheme to be the value of the second rank; and
if the inner query does not use an index scan, assign the second rank from a second plurality of rank values, wherein the second plurality of rank values are based at least on the number of rows resulting from the outer query of the database query.