Browse Prior Art Database

Empirical generation of Optimized SQL based upon front end usage analysis

IP.com Disclosure Number: IPCOM000205847D
Publication Date: 2011-Apr-06
Document File: 4 page(s) / 75K

Publishing Venue

The IP.com Prior Art Database

Abstract

Data can be retrieved from the database system using Structured Query Language (SQL) which is pre-constructed or can be accessed through the SQLs generated at runtime. Disclosed is a framework to generate the SQL dynamically at runtime by accessing the database based on user's requirement in terms of columns and entities. The SQL generation logic caters to various depth of data retrieval based on user's entitlements and the usage patterns which is derived from the usage analysis. User intervention is minimized to optimize access and constant configuration changes, also SQL generation/editing by an admin user is not required.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 31% of the total text.

Page 01 of 4

Empirical generation of Optimized SQL based upon front end usage analysis

Front end applications for Database Management Systems either use home grown data access logic or use an object relational mapping tool that generate SQLs from metadata. Static SQL generation is a one time activity involving the ORM (Object Relational Mapping) tool and conducted before deployment of the application. However, there are runtime generators for SQL too such as SQL J. These software allow SQL generation logic to be built into the code. Although the SQLs may be generated at runtime, the same depends upon how the logic of building the SQLs is embedded into the application logic using language extension or proprietary scripts.

The SQL is left to the user for optimization either manually or using the DBMS optimizer. The ORM tool may allow user to map object attributes to database tables and columns and transparently generate the SQL. However, present ORM tools are not equipped with granular control of data fetched from the database for a particular kind of query.

Dynamic SQL generators address the coupling of application code with backend SQL at runtime. For example, a certain getCustomer functionality will result in a mapping of a particular query based on design time mapping and SQL generation. However, the same will not consider various flavors of depth of detail retrieved along with the same at runtime. Not all users would like to retrieve the same depth of detail. Hence, it is usually modeled presently using different operations and SQLs altogether.

Secondly, the present ORM tools do not take into consideration patterns involving usage. The data set a user often retrieves on his visit to the application varies significantly based upon such static factors such as role and demographics. However, the dataset may be often influenced by temporary factors as described here. Taking the above example of getCustomer, a customer would like to retrieve such information such as Account Details, Transactions, Bill Payments etc. The dispatch team may be interested in Address and Contact details whereas the Loan Approver may be interested in such information as Credit rating. To analyze further, a customer who has applied for a loan may be also interested in approval status and credit rating along with other information described earlier. Hence, a design time decision of creating such data sets for a specific role with customer and corresponding depth of information is not sufficient. Alternatively a superset of such dataset may be created and relevant parts of it may be accessed by the customer at various times. However this approach falls short of performance requirements.

In other words the same data set may be viewed differently by different users or the same user at different points in time with varying depth of detail.

The framework encompasses a method and an apparatus that embodies an ORM like software component. A data entity is represented in aggreg...