...
This query has a very broad set of responsibilities which cannot be easily verified in isolation. On closer inspection it appears that it is in fact formed of at least 7 distinct queries. To To effectively unit test the process that this query represents an approach must be applied that separates and encapsulates this process we must encapsulate each of the subqueries into separate components so that they can be tested independently. Possible approaches to this include: VIEWs, sequential To achieve this there are a number of approaches are open to us including:
- Sequential execution of components with intermediate
...
- tables.
- Views.
- Variable substitution of query fragments.
- Functional/procedural SQL approaches.
Limited testing suggests that the VIEW approach is more effective than using sequential execution of components with intermediate tables, both in terms of elegance and performance. Intermediate table solutions (including TEMPORARY tables) take longer to run, Furthermore, the availability of HPL/SQL may provide additional opportunities to modularize. The potential performance implications of such techniques should be considered. For example, using intermediate tables may generate more I/O and splitting large queries may limit the query optimisation opportunities available to the query planner.
...
| title | TODO |
|---|
, and restrict query optimization opportunities. It should also be noted that views do not appear to suffer from performance issues as often prophesied; in fact the execution plans and times for views and monolithic queries were comparable.
Variable substitution was also suggested as an approach for modularizing large queries, but upon inspection it was found to be unsuitable as an additional bash file is required which would make testing more complex. HPL/SQL was also considered, however it does not have the necessary pipelined function feature required for query modularization.
...
Tools and frameworks
When constructing tests it is helpful to have a framework that simplifies the declaration and execution of tests. Typically these tools allow the specification of many of the following:
...