Static Query is a stored, parameterized procedure. It is optimized for access to a particular data warehouse. It can never be denied that the data warehouse is one of the biggest forms of data repository.
In fact, since a data warehouse contains the business organizations repository of historical data, every single day the volume increases although it may also decrease in some cases depending on the implementation.
Since this is the repository data, the business organization’s end user through the information system will make frequent trip to get to warehouse for data to be input in statistical analysis as in the case of online analytical processing (OLAP).
The data warehouses also needs to constantly process inserts, updates and deletes to address the needs of online transactional processing (OLTP) and other process outputs coming from various data sources and data stores.
A data warehouse is basically a database implementation. And since this is a database, it handles queries.
Hundreds of queries are being processed by the data warehouse and each of these queries differs to some degree in nature. That is why a database, especially the relational database management system, need to have a query plan which is often called a query execution plan.
A query plan is a set of steps that are used in trying to access data and information in a SQL relational database management system. Since SQL is in nature declarative, there is generally so many alternatives ways by which any given query may be executed with a widely varying performance.
Every time a query gets submitted to a database, it is being evaluated by a query optimizer so that the correct possible plans for executing the query can be had and returns what it considers the best alternative. And because query optimizers can never be perfect, some database designers, architects and administrators often need to examine the database manually and tune the plans produced by the query optimizer in order to get better performance.
A static query is part of the query plan and is in fact very effective in adding efficiency in certain circumstances. A static query, as the name implies, is very fixed and will never be change. For instance, all the parameters are already defined and can never be altered anymore. This is in contract to dynamic or ad hoc queries.
An ad hoc query cannot be determined prior to the moment the query is issued and is instead created in order to get information when need arises and it consists of dynamically constructed SQL which is usually constructed by desktop-resident query tools.
A static query is the better choice over ad ah hoc queries for frequently repeated access of information.
For instance, is the business organization wants a report on the sale of a certain branch regardless of the date and the same report is being asked at the end of each month with a month being defined as 30 days, then a static query is the best way to go. This means the same parameters are being used for the regular report requirements from the data warehouse.
Using a static query means that the database will have a lighter processing load and therefore results in higher overall efficiency.
An ad-hoc query in comparison, can have a high potential of performance degradation especially in cases when a complex ad hoc query is executed so database managers sometimes only provide copy of the live database to be regularly refreshed.
A static query on the other hand has predefined and unchanging parameters so whenever a query is issued, the database will not have to processes whatever dynamic parameters need to be set and thus greatly reducing time and computing resources.