# US 2023/0053909 A1 · Computerized system and method for optimizing queries in a templated virtual semantic layer

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2023/0053909 A1
- Document ID: US20230053909A1
- Status: Published application
- Kind: A1
- Application number: 17/399,520
- Filed: 2021-08-11
- Published: 2023-02-23
- Earliest claimed: 2021-08-11
- Assignees: Verizon Media Inc.
- Applicants: Verizon Media Inc.
- Inventors: Aaron J. KLISH; Jonathan KILROY; Moiz ARAFAT; Rishi AGARWAL
- Web page: https://patentgrep.com/patent/US20230053909A1

## CPC

- G06F 16/24544: {Join order optimisation}
- G06F 11/3409: {for performance assessment}
- G06F 16/2443: {Stored procedures}
- G06F 16/2452: Query translation
- G06F 16/2453: Query optimisation
- G06F 16/256: {in federated or virtual databases}
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

The disclosed systems and methods provide a novel framework that optimizes SQL queries that are generated from a templated virtual sematic layer. The framework introduces the use of a virtual semantic layer into database management systems' operations, whereby templated SQL queries can be rewritten according to a determined and measured nesting, dimensional structure that produces an optimized search system. This enables templated SQL fragments to be translated for query optimization, thereby reducing the drain on a database's resources and minimizing a query's impact on the database's performance.

## Claims

### Claim 1

A method comprising:
identifying, by a device, a database, the database comprising a plurality of information organized into at least a set of columns;
identifying, by the device, a Column Object (CO) within a column of the database, the CO comprising information related to a column name, a Structured Query Language (SQL) expression and an alias;
analyzing, by the device, the CO;
creating, by the device, based on the analysis, a new CO related to the identified CO, the new CO comprising information related to the column name, a templated expression of the expression, and the alias;
analyzing, by the device, information related to the new CO, and determining a type of dimension nesting, the type of dimension nesting being based on whether a referenced join is to occur as an inner query or outer query; and
performing, by the device, the determined type of dimension nesting.

### Claim 2 (depends on 1)

The method of claim 1, further comprising:
identifying an unnested SQL fragment associated with the CO;
parsing the SQL fragment into a syntax tree;
analyzing the syntax tree, and identifying aggregation function calls;
translating each identified aggregation function call into a set of inner aggregations;
assigning each inner aggregation function a unique alias;
converting each inner aggregation into another CO;
translating each aggregation function call into a set of outer aggregations;
modifying operands of the syntax tree to reference the inner aggregation aliases;
analyzing the modified syntax tree and generating a new native SQL expression;
replacing all aliases in the new native SQL expression with the templated expression; and
converting the outer aggregations into the new CO.

### Claim 3 (depends on 2)

The method of claim 2, further comprising:
further analyzing the syntax tree, wherein the translation of each aggregation function call into a set of outer aggregations is based on the further analysis.

### Claim 4 (depends on 2)

The method of claim 2, wherein the unnested SQL fragment at least corresponds to the expression of the CO.

### Claim 5 (depends on 1)

The method of claim 1, further comprising:
determining, based on the analysis of the new CO information, that the type of dimension nesting is inner dimension nesting.

### Claim 6 (depends on 5)

The method of claim 5, further comprising:
identifying the new CO;
designating the new CO as an inner query CO; and
creating a new outer query CO that references the inner query CO.

### Claim 7 (depends on 5)

The method of claim 5, wherein the referenced join is to occur as the inner query.

### Claim 8 (depends on 1)

The method of claim 1, further comprising:
determining that a referenced join does not exist in relation to the new CO; and
determining that the type of dimension nesting is inner dimension nesting.

### Claim 9 (depends on 1)

The method of claim 1, further comprising
determining, based on the analysis of the new CO information, that the type of dimension nesting is outer dimension nesting, wherein the referenced join is to occur as the outer query.

### Claim 10 (depends on 9)

The method of claim 9, further comprising:
identifying the expression of the new CO;
converting the expression into a reference tree;
analyzing the reference tree, and extracting physical column references;
grouping the physical column references;
creating a set of new inner query COs for each grouping, each new inner query CO being set to a templated expression that references a physical column;
generating an outer query expression by at least partially resolving template variables of the templated expression of the new CO; and
creating a new outer query CO that includes the outer query expression and the alias.

### Claim 11

A non-transitory computer-readable storage medium tangibly encoded with computer-executable instructions, that when executed by a processor associated with a device, performs a method comprising:
identifying, by the device, a database, the database comprising a plurality of information organized into at least a set of columns;
identifying, by the device, a Column Object (CO) within a column of the database, the CO comprising information related to a column name, a Structured Query Language (SQL) expression and an alias;
analyzing, by the device, the CO;
creating, by the device, based on the analysis, a new CO related to the identified CO, the new CO comprising information related to the column name, a templated expression of the expression, and the alias;
analyzing, by the device, information related to the new CO, and determining a type of dimension nesting, the type of dimension nesting being based on whether a referenced join is to occur as an inner query or outer query; and
performing, by the device, the determined type of dimension nesting.

### Claim 12 (depends on 11)

The non-transitory computer-readable storage medium of claim 11, further comprising:
identifying an unnested SQL fragment associated with the CO, wherein the unnested SQL fragment at least corresponds to the expression of the CO;
parsing the SQL fragment into a syntax tree;
analyzing the syntax tree, and identifying aggregation function calls;
translating each identified aggregation function call into a set of inner aggregations;
assigning each inner aggregation function a unique alias;
converting each inner aggregation into another CO;
translating each aggregation function call into a set of outer aggregations;
modifying operands of the syntax tree to reference the inner aggregation aliases;
analyzing the modified syntax tree and generating a new native SQL expression;
replacing all aliases in the new native SQL expression with the templated expression; and
converting the outer aggregations into the new CO.

### Claim 13 (depends on 11)

The non-transitory computer-readable storage medium of claim 11, further comprising:
determining, based on the analysis of the new CO information, that the type of dimension nesting is inner dimension nesting.

### Claim 14 (depends on 13)

The non-transitory computer-readable storage medium of claim 13, further comprising:
identifying the new CO;
designating the new CO as an inner query CO; and
creating a new outer query CO that references the inner query CO.

### Claim 15 (depends on 11)

The non-transitory computer-readable storage medium of claim 11, further comprising
determining, based on the analysis of the new CO information, that the type of dimension nesting is outer dimension nesting, wherein the referenced join is to occur as the outer query.

### Claim 16 (depends on 15)

The non-transitory computer-readable storage medium of claim 15, further comprising:
identifying the expression of the new CO;
converting the expression into a reference tree;
analyzing the reference tree, and extracting physical column references;
grouping the physical column references;
creating a set of new inner query COs for each grouping, each new inner query CO being set to a templated expression that references a physical column;
generating an outer query expression by at least partially resolving template variables of the templated expression of the new CO; and
creating a new outer query CO that includes the outer query expression and the alias.

### Claim 17

A device comprising:
a processor configured to:
identify a database, the database comprising a plurality of information organized into at least a set of columns;
identify a Column Object (CO) within a column of the database, the CO comprising information related to a column name, a Structured Query Language (SQL) expression and an alias;
analyze the CO;
create, based on the analysis, a new CO related to the identified CO, the new CO comprising information related to the column name, a templated expression of the expression, and the alias;
analyze information related to the new CO, and determine a type of dimension nesting, the type of dimension nesting being based on whether a referenced join is to occur as an inner query or outer query; and
perform the determined type of dimension nesting.

### Claim 18 (depends on 17)

The device of claim 17, wherein the processor is further configured to:
identify an unnested SQL fragment associated with the CO, wherein the unnested SQL fragment at least corresponds to the expression of the CO;
parse the SQL fragment into a syntax tree;
analyze the syntax tree, and identify aggregation function calls;
translate each identified aggregation function call into a set of inner aggregations;
assign each inner aggregation function a unique alias;
convert each inner aggregation into another CO;
translate each aggregation function call into a set of outer aggregations;
modify operands of the syntax tree to reference the inner aggregation aliases;
analyze the modified syntax tree and generate a new native SQL expression;
replace all aliases in the new native SQL expression with the templated expression; and
convert the outer aggregations into the new CO.

### Claim 19 (depends on 17)

The device of claim 17, wherein the processor is further configured to:
determine, based on the analysis of the new CO information, that the type of dimension nesting is inner dimension nesting;
identify the new CO;
designate the new CO as an inner query CO; and
create a new outer query CO that references the inner query CO.

### Claim 20 (depends on 17)

The device of claim 17, wherein the processor is further configured to:
determine, based on the analysis of the new CO information, that the type of dimension nesting is outer dimension nesting, wherein the referenced join is to occur as the outer query;
identify the expression of the new CO;
convert the expression into a reference tree;
analyze the reference tree, and extract physical column references;
group the physical column references;
create a set of new inner query COs for each grouping, each new inner query CO being set to a templated expression that references a physical column;
generate an outer query expression by at least partially resolving template variables of the templated expression of the new CO; and
create a new outer query CO that includes the outer query expression and the alias.