Queries with CASE expressions

In recent years more and more queries are generated automatically by query managers/builders with end-users providing only specific parameters through GUIs. Queries generated automatically can be quite different from queries written by humans. In particular, they contain non-declarative features, most notorious of which is the CASE expression. Current query optimizers are often ill-prepared for the new types of queries as they do not deal well with procedural ‘insertions’. In this paper, we discuss the inefficiencies of CASE expressions and present several new optimization techniques to address them. We also describe experimental evaluation of the prototype implemented in DB2 UDB V8.2.     

Efficient evaluation of generalized tree-pattern queries on XML streams

The streaming evaluation is a popular way of evaluating queries on XML documents. Besides its many advantages, it is also the only option for a number of important XML applications. Unfortunately, existing algorithms focus almost exclusively on tree-pattern queries (TPQs). Requirements for flexible querying of XML data have motivated recently the introduction of query languages that are more general and flexible than TPQs. These languages are not supported by existing algorithms. In this paper, we consider a partial tree-pattern query (PTPQ) language which generalizes and strictly contains TPQs. PTPQs can express a fragment of XPath which comprises reverse axes and the node identity equality (is) operator, in addition to forward axes, wildcards and predicates. They constitute an important subclass of XPath, which is very useful in practice. Unfortunately, previous streaming algorithms for TPQs cannot be applied to PTPQs. PTPQs can be represented as dags enhanced with constraints. We explore this representation to design an original polynomial time streaming algorithm for PTPQs. Our algorithm aggressively filters incoming data that is irrelevant to the query and wisely avoids processing redundant query matches (i.e., matches of the query dag that do not contribute to new solutions). Our algorithm is the first one to support the streaming evaluation of such a broad fragment of XPath. We provide an analysis of it, and conduct an extensive experimental evaluation of its performance and scalability. Compared to the only known streaming algorithm that supports TPQs extended with reverse axes, our algorithm performs better by orders of magnitude while consuming a much smaller fraction of memory space. Current streaming applications have stringent requirements on query response time and memory consumption because of the large (possibly unbounded) size of data they handle. In order to keep memory usage and CPU consumption low for the PTPQ streaming evaluation, we design another streaming algorithm called Eager PSX for PTPQs. Its key feature is that it applies an eager evaluation strategy to quickly determine when node matches should be returned as solutions to the user and also to proactively detect redundant matches. We theoretically analyze Eager PSX, and experimentally test its time and space performance and scalability. We compare it with PSX. Our results show that Eager PSX not only achieves better space performance without compromising time performance, but it also greatly improves query response time for both simple and complex queries, in many cases, by orders of magnitude.     

Photodegradation of psychiatric pharmaceuticals in aquatic environments--kinetics and photodegradation products

Benzodiazepines are widely consumed psychiatric pharmaceuticals which are frequently detected in the environment. The environmental persistence and fate of these pharmaceuticals as well as their degradation products is of high relevance and it is, yet, scarcely elucidated. In this study, the relevance of photodegradation processes on the environmental persistence of four benzodiazepines (oxazepam, diazepam, lorazepam and alprazolam) was investigated. Benzodiazepines were irradiated under simulated solar irradiation and direct and indirect (together with three different fractions of humic substances) photodegradation kinetics were determined. Lorazepam was shown to be quickly photodegradated by direct solar radiation, with a half-life time lower than one summer sunny day. On the contrary, oxazepam, diazepam and alprazolam showed to be highly resistant to photodegradation with half-life times of 4, 7 and 228 summer sunny days, respectively. Apparent indirect and direct photodegradation rates are of the same order of magnitude. However, humic acids were consistently responsible for a decrease in the photodegradation rates while fulvic acids and XAD4 fraction caused an enhancement of the photodegradation. Overall, the results highlight that photodegradation might not be an efficient pathway to prevent the aquatic environmental accumulation of oxazepam, diazepam and alprazolam. Also, nineteen direct photodegradation products were identified by electrospray mass spectrometry, the majority of which are newly identified photoproducts. This identification is crucial to a more complete understanding of the environmental impact of benzodiazepines in aquatic systems.     

Optimization of generic progressive queries based on dependency analysis and materialized views

Progressive queries (PQ) are a new type of queries emerging from numerous contemporary database applications such as e-commerce, social network, business intelligence, and decision support. Such a PQ is formulated on the fly in several steps via a set of inter-related step-queries (SQ). In our previous work, we presented a framework to process a restricted type of PQs. However, how to process generic PQs remains an open problem. In this paper, we develop a novel technique to efficiently process generic PQs based on materialized views. The SQs of an in-process PQ can utilize the results of previous SQs not only from the same PQ but also from other in-process and completed PQs. The key idea is to create a multiple query dependency graph (MQDG), which captures the data source dependency relationships among SQs from multiple PQs. A mathematic model is developed to estimate the benefit of keeping the result of an SQ as a materialized view (critical SQ/node) based on the MQDG. The kept materialized views are used to improve the performance of the future SQs. Strategies for constructing the MQDG and identifying the critical SQs for materialization by using the MQDG are presented. To manage the storage of the materialized views, we introduce two approaches – one employs a greedy method and the other adopts a dynamic programming (DP) based method. Strategies are also suggested to reduce the input problem size for the DP procedure. Experimental results demonstrate that our technique is quite promising in efficiently processing PQs.     

Optimizing complex queries based on similarities of subqueries

As database technology is applied to more and more application domains, user queries are becoming increasingly complex (e.g. involving a large number of joins and a complex query structure). Query optimizers in existing database management systems (DBMS) were not developed for efficiently processing such queries and often suffer from problems such as intolerably long optimization time and poor optimization results. To tackle this challenge, we present a new similarity-based approach to optimizing complex queries in this paper. The key idea is to identify similar subqueries that often appear in a complex query and share the optimization result among similar subqueries in the query. Different levels of similarity for subqueries are introduced. Efficient algorithms to identify similar queries in a given query and optimize the query based on similarity are presented. Related issues, such as choosing good starting nodes in a query graph, evaluating identified similar subqueries and analyzing algorithm complexities, are discussed. Our experimental results demonstrate that the proposed similarity-based approach is quite promising in optimizing complex queries with similar subqueries in a DBMS.     

Direct photodegradation of carbamazepine followed by micellar electrokinetic chromatography and mass spectrometry

Carbamazepine, a widely consumed psychotropic pharmaceutical, is one of the most commonly detected drugs in the environment. To better assess the environmental persistence of carbamazepine in aqueous matrices, the effect of pH and dissolved oxygen on the direct photodegradation rate of this pharmaceutical was evaluated in this study, using simulated solar irradiation. In order to follow the degradation and the emergence of photoproducts, a micellar electrokinetic chromatography based method was developed, consisting on the use of a dynamically coated capillary column. The developed methodology showed good repeatability and efficiency in the separation of carbamazepine and photoirradiation products. Also, seven photodegradation products were identified by electrospray mass spectrometry (ESI-MS), including the known carcinogenic acridine that was produced under all the pH and oxygenation levels studied and one newly identified photoproduct.This paper gives new insights into the role of dissolved oxygen on the photodegradation rate of carbamazepine. The results indicate that acidic pH, combined with the absence of dissolved oxygen in the aqueous matrix, results in very high direct photodegradation rates. At basic pH, dissolved oxygen does not interfere with the process and very low rates were observed. At environmentally relevant conditions, carbamazepine was shown to persist in the environment from 4.5 to 25 days.     

Final cell distribution in a Zener pinned structure

The grain growth phenomenon in polycrystalline materials and its corresponding analogy with domain growth in foams is well documented in the scientific literature. In this work, experimental results on pinned cell growth are communicated for two-dimensional growth in foams. Also, the size distribution of the final arrangement is described in the theoretical framework of the corresponding Fokker–Planck equation. Fair concordance with our reported data is achieved.     

The transient transpiration radiometer: analysis and parameter selection

A large variety of available radiometers require steady-state conditions to obtain output results. This means that in order to quantify the heat transfer, the temperature of the instrument must first be stabilised, consequently lengthening its response time. The purpose of this paper is to present a formulation of the transient response of a transpiration radiometer. The measurement technique of the radiometer under study is based on a porous disc aligned perpendicularly to a flowing gas stream, while one of its faces is exposed to the heat flux to be measured. A discussion of the results is presented using a mathematical model, according to an examination of the simplifying assumptions made in the data reduction process and the examination of the response. Also presented are sensitivity analysis, temperature, heat flux intensity and manufacturing constraints of the relevant design.     

Load balancing and data placement for multi-tiered database systems

A materialized view or Materialized Query Table (MQT) is an auxiliary table with precomputed data that can be used to significantly improve the performance of a database query. A Materialized Query Table Advisor (MQTA) is often used to recommend and create MQTs. The state-of-the-art MQTA works in a standalone database server where MQTs are placed on the same server as that in which the base tables are located. The MQTA does not apply to a federated or scaleout scenario in which MQTs need to be placed on other servers close to applications (i.e. a frontend database server) for offloading the workload on the backend database server. In this paper, we propose a Data Placement Advisor (DPA) and load balancing strategies for multi-tiered database systems. Built on top of the MQTA, DPA recommends MQTs and advises placement strategies for minimizing the response time for a query workload. To demonstrate the benefit of the data placement advising, we implemented a prototype of DPA that works with the MQTA in the IBM^® DB2^® Universal Database™ (DB2 UDB) and the IBM WebSphere^® Information Integrator (WebSphere II). The evaluation results showed substantial improvements of workload response times when MQTs are intelligently recommended and placed on a frontend database server subject to space and load characteristics for TPC-H and OLAP type workloads.     

Star join revisited: Performance internals for cluster architectures

Data warehouse workloads are crucial for the support of on-line analytical processing (OLAP). The strategy to cope with OLAP queries on such huge amounts of data calls for the use of large parallel computers. The trend today is to use cluster architectures that show a reasonable balance between cost and performance. In such cases, it is necessary to tune the applications in order to minimize the amount of I/O and communication, such that the global execution time is reduced as much as possible.

In this paper, we model and analyze the most up-to-date strategies for ad hoc star join query processing in a cluster of computers. We show that, for ad hoc query processing and assuming a limited amount of resources available, these strategies still have room for improvement both in terms of I/O and inter-node data traffic communication. Our analysis concludes with the proposal of a hybrid solution that improves these two aspects compared to the previous techniques, and shows near optimal results in a broad spectrum of cases.