Deriving Efficient SQL Sequences Via Prefetching
Modern information architectures place business logic in an application server and persistent objects in a relational DBMS. To effectively realize such architectures, we must surmount the problem of effectively fetching objects from DBMS to the application server. Object access patterns are not random; they are driven by applications and user behaviors. Naive implementations retrieve objects from the DBMS as such objects are requested by the application, costing a DBMS roundtrip for each query. This fact, coupled with the growing performance bottleneck of computer storage systems, has resulted in a significant amount of research improving object access behavior through predicting future access objects because throughput will continue to improve but latency will not. Latency will be an ever-increasing component of object access cost. In turn, object access cost is usually the bottleneck for modern high performance systems. This yields unacceptably poor performance when application server submit sequence of relational queries to DBMSs. A reasonable approach would be to generate prefetch queries that retrieve objects that would later be requested by the application. However, whereas some prefetch queries would be beneficial, some would not. Distinguishing between them is nontrivial in practice, because commercial DBMSs do not expose efficient query response-time estimators. First, there is no standardized interface for an application server to force the database system to calculate the costs (e.g., response time) for a given query. Second, we still have the entire roundtrip costs between application servers and the DBMSs to estimate the total cost of query evaluation. Consequently, in current practice, programmers spend enormous amounts of time tuning the queries by which objects are retrieved by an application. This dissertation develops an application-independent approach for generating prefetch queries that can be implemented in conventional middleware systems. The main contribution of this dissertation is a set of application-independent guidelines for selecting, based on application's access patterns and additional parameters, efficient ways of merging the application's data requests into prefetch queries. Our guidelines take the current configuration such as local or wide area networks into account, allowing it to select strategies that give good performance in a wider range of configurations. The ensuing performance gains are evaluated via realistic settings based on a retail database inspired by the SPECJ performance test suite.
Advisor:Christopher G. Healey; Alan L. Tharp; Munindar P. Singh; Rada Y. Chirkova
School:North Carolina State University
School Location:USA - North Carolina
Source Type:Master's Thesis
Date of Publication:01/04/2008