Back to results
US 10,762,099 B2Granted
Syntactical transformation of database interaction statements
AssigneeInternational Business Machines Corporation
InventorsDong Jie Wei, Ke Wei Wei, Xin Ying Yang, Miao Zheng
Life of this patent
Estimating the term…
Abstract
A computer-implemented method includes identifying a primary database interaction statement and determining one or more intermediate database interaction statements based on the primary database interaction statement. The computer-implemented further includes determining, for each of the one or more intermediate database interaction statements, an intermediate statement transformation indicator. The computer-implemented method further includes determining one or more transformed database interaction statements based on each intermediate statement transformation indicator. A corresponding computer program product and computer system are also disclosed.
Claims18
A computer program product, the computer program product comprising:
- one or more computer readable storage media;
- and
- computer code stored on said one or more computer readable storage media, with the computer code including instructions for causing a processor(s) set to perform operations including the following:
- receiving a primary database interaction statement in SQL,
- determining that the primary database interaction statement includes at least one SQL case statement portion, including a first SQL case statement portion,
- determining that the first SQL case statement portion is eligible for an SQL query optimization transformation based, at least in part, on comparing the first SQL case statement portion against a set of SQL transformation patterns, where a first SQL transformation pattern is an SQL case statement which evaluates an equation with a constant-returning function on one side of the equation, and a second SQL transformation pattern is an SQL case statement which evaluates an equation with a constant value on one side of the equation,
- responsive to determining that the first SQL case statement portion comparison yielded a match with the first SQL transformation pattern, generating, by machine logic, a first set of intermediate SQL database interaction statements based, at least in part, on the first SQL case statement portion and a set of SQL intermediate statement guidelines corresponding to the set of SQL transformation patterns, where the set of SQL intermediate statement guidelines includes at least a first SQL intermediate statement guideline corresponding to the first SQL transformation pattern, where the first SQL intermediate statement guideline stipulates that, for each given constant in the first SQL case statement portion, generate a select case statement which returns an integer value of 1 if the given constant is equal to output of the constant-returning function and a value of 0 if they are not equal,
- responsive to generating at least one SQL intermediate statement based upon the first SQL intermediate statement guideline and the first SQL case statement portion, for each given SQL intermediate statement of the first set of intermediate SQL interaction statements, determining, by machine logic, the results of the given SQL intermediate statement of the first set of intermediate SQL interaction statements,
- selecting, by machine logic, a first SQL intermediate statement transformation indicator from a set of SQL intermediate statement indicators based, at least in part, on the determined results of at least one SQL intermediate statement of the first set of intermediate SQL interaction statements, where the first SQL intermediate statement transformation indicator indicates replacing the first SQL case statement portion of the primary database interaction statement with a count function multiplied by a numerical value, where the numerical value is based upon the determined results of the at least one SQL intermediate statement of the first set of intermediate SQL interaction statements, and
- transforming the primary database interaction statement into a transformed database interaction, where the transformed database interactions statement includes replacement of the first SQL case statement portion of the primary database interaction statement with a first transformed SQL query portion that is based on the selected first SQL intermediate transformation indicator, where execution of the transformed database interaction statement requires reduced computation resources relative to the primary database interaction statement.
7 more elements
A computer system (CS) comprising:
- a processor(s) set;
- a machine readable storage device;
- and
- computer code stored on the machine readable storage device, with the computer code including instructions for causing the processor(s) set to perform operations including the following:
- receiving a primary database interaction statement in SQL,
- determining that the primary database interaction statement includes at least one SQL case statement portion, including a first SQL case statement portion,
- the first SQL case statement portion is eligible for an SQL query optimization transformation based, at least in part, on comparing the first SQL case statement portion against a set of SQL transformation patterns, where a first SQL transformation pattern is an SQL case statement which evaluates an equation with a constant-returning function on one side of the equation, and a second SQL transformation pattern is an SQL case statement which evaluates an equation with a constant value on one side of the equation,
- responsive to determining that the first SQL case statement portion comparison yielded a match with the first SQL transformation pattern, generating, by machine logic, a first set of intermediate SQL database interaction statements based, at least in part, on the first SQL case statement portion and a set of SQL intermediate statement guidelines corresponding to the set of SQL transformation patterns, where the set of SQL intermediate statement guidelines includes at least a first SQL intermediate statement guideline corresponding to the first SQL transformation pattern, where the first SQL intermediate statement guideline stipulates that, for each given constant in the first SQL case statement portion, generate a select case statement which returns an integer value of 1 if the given constant is equal to output of the constant-returning function and a value of 0 if they are not equal,
- responsive to generating at least one SQL intermediate statement based upon the first SQL intermediate statement guideline and the first SQL case statement portion, for each given SQL intermediate statement of the first set of intermediate SQL interaction statements, determining, by machine logic, the results of the given SQL intermediate statement of the first set of intermediate SQL interaction statements,
- selecting, by machine logic, a first SQL intermediate statement transformation indicator from a set of SQL intermediate statement indicators based, at least in part, on the determined results of the at least one SQL intermediate statement of the first set of intermediate SQL interaction statements, where the first SQL intermediate statement transformation indicator indicates replacing the first SQL case statement portion of the primary database interaction statement with a count function multiplied by a numerical value, where the numerical value is based upon the determined results of the at least one SQL intermediate statement of the first set of intermediate SQL interaction statements, and
- transforming the primary database interaction statement into a transformed database interaction, where the transformed database interactions statement includes replacement of the first SQL case statement portion of the primary database interaction statement with a first transformed SQL query portion that is based on the selected first SQL intermediate transformation indicator, where execution of the transformed database interaction statement requires reduced computation resources relative to the primary database interaction statement.
8 more elements
Dependencies are inferred from the claim text.