Optimization of Dynamically Generated SQL Queries for Tiny-Huge, Huge-Tiny Problem
In most new commercial business software applications like Customer Relationship Management, the data is stored in the database layer which is usually a Relational DataBase Management System (RDBMS) like Oracle, DB2 UDB or SQL Server. To access data from these databases, Structured Query Language (SQL) queries are used that are generated dynamically at run time based on defined business models and business rules. One such business rule is visibility-the capability of the application to restrict data access based on the role and responsibility of the user logged in to the application. This is generally achieved by appending security predicates in the form of sub-queries to the main query based on the roles and responsibility of the user.