José A. Blakeley

Experiences building the open OODB query optimizer

This paper reports our experiences building the query optimizer for TIs Open OODB system. To the best of our knowledge, it is the first working object query optimizer to be based on a complete extensible optimization framework including logical algebra, execution algorithms, property enforcers, logical transformation rules, implementation rules, and selectivity and cost estimation. Our algebra incorporates a new materialize operator with its corresponding logical transformation and implementation rules that enable the optimization of path expressions. Initial experiments on queries obtained from the object query optimization literature demonstrate that our optimizer is able to derive plans that are as efficient as, and often substantially more efficient than, the plans generated by other query optimization strategies. These experiments demonstrate that our initial choices for populating each part of our optimization framework are reasonable. Our experience also shows that having a complete optimization framework is crucial for two reasons. First, it allows the optimizer to discover plans that cannot be revealed by exploring only the alternatives provided by the logical algebra and its transformations. Second, it helps and forces the database system designer to consider all parts of the framework and to maintain a good balance of choices when incorporating a new logical operator, execution algorithm, transformation rule, or implementation rule. The Open OODB query optimizer was constructed using the Volcano Optimizer Generator, demonstrating that this second-generation optimizer generator enables rapid development of efficient and effective query optimizers for non-standard data models and systems.

Architecture of an open object-oriented database management system

The architecture of the open object-oriented database (OODB) management system, its requirements, and its computational model, which builds database functionality as an extensible collection of transparent extensions to existing programming languages, are described. The way in which open OODBs system architecture is decomposed into a kernal meta-architecture and a collection of modules implementing specific behavioral extensions is discussed. The potential risks of the open OODB architecture are reviewed.<<ETX>>

Data access for the masses through OLE DB

This paper presents an overview of OLE DB, a set of interfaces being developed at Microsoft whose goal is to enable applications to have uniform access to data stored in DBMS and non-DBMS information containers. Applications will be able to take advantage of the benefits of database technology without having to transfer data from its place of origin to a DBMS. Our approach consists of defining an open, extensible Collection of interfaces that factor and encapsulate orthogonal, reusable portions of DBMS functionality. These interfaces define the boundaries of DBMS components such as record containers, query processors, and transaction coordinators that enable uniform, transactional access to data among such components. The proposed interfaces extend Microsofts OLE/COM object services framework with database functionality, hence these interfaces are collectively referred to as OLE DB. The OLE DB functional areas include data access and updates (rowsets), query processing, schema information, notifications, transactions, security, and access to remote data. In a sense, OLE DB represents an effort to bring database technology to the masses. This paper presents an overview of the OLE DB approach and its areas of componentization.

Anatomy of the ADO.NET entity framework

Traditional client-server applications relegate query and persistence operations on their data to database systems. The database system operates on data in the form of rows and tables, while the application operates on data in terms of higher-level programming language constructs (classes, structures etc.). The impedance mismatch in the data manipulation services between the application and the database tier was problematic even in traditional systems. With the advent of service-oriented architectures (SOA), application servers and multi-tier applications, the need for data access and manipulation services that are well-integrated with programming environments and can operate in any tier has increased tremendously. Microsofts ADO.NET Entity Framework is a platform for programming against data that raises the level of abstraction from the relational level to the conceptual (entity) level, and thereby significantly reduces the impedance mismatch for applications and data-centric services. This paper describes the key aspects of the Entity Framework, the overall system architecture, and the underlying technologies.

Maintaining materialized views without accessing base data

Abstract Access to a database through a user view can be serviced quickly when the view is materialized, i.e. the transformed data is explicitly stored. In the presence of database updates, however, the materialized view can become costly to maintain; often it must be completely rederived from the base data using the view definition. Under some conditions the view can be updated directly given only the view definition, the current contents of the materialized view, and the update operation (still expressed against the base data), without accessing the base data itself. In this paper, we consider relational views defined by projection, selection, and join. We present necessary and sufficient conditions on the view definition, contents, and update operations for insertions and deletions to be reflected in the view without reference to base data. Because the possibility of such view-based updating is dependent on the current contents of view, we call the update conditionally autonomously computable .

Universal data access with OLE DB

OLE DB is Microsofts new data access API designed to enable access to all kinds of data sources, both database and non database, regardless of format or location. OLE DB builds on Microsofts Component Object Model (COM), which is the foundation for OLE and ActiveX. OLE DB is core to Microsofts Universal Access strategy. OLE DB aims at providing an environment for business applications to access all kinds of data sources in an integrated way, including desktop data such as spreadsheets, text processing documents, and electronic mail; server data stored in the file system, indexed sequential files, relational, hierarchical and network databases; and data computed by middle tier business objects. Most database companies are pursuing a Universal Storage strategy which provides access to all kinds of data types such as text, spatial, video, and audio, and insist on placing all kinds of data used in an organization inside the database. However, the reality is that a vast amount of mission critical data in a corporation is stored in a combination of database and non database data sources for functionality and performance reasons. Therefore, Microsoft is pursuing a Universal Access strategy which provides an infrastructure that enables the integration of a wide variety of data sources so that applications can be written in an efficient, safe and disciplined manner without losing the advantages of a centralized database system. The paper provides an overview of OLE DB and describes how OLE DB enables the Microsoft Universal Access approach to managing data.

NET database programmability and extensibility in microsoft SQL server

The integration of the .NET Common Language Runtime (CLR) into the SQL Server DBMS enables rich business logic written in modern .NET languages to run close to the data. Database application developers can write business logic as functions, stored procedures, and triggers. They can also extend the native capabilities of the DBMS by adding new scalar data types, and aggregates. A previous paper [2] described the architecture and design principles of the integration of the CLR inside SQL Server. Here we present new aspects of this work. First, we describe the extensibility contracts for user-defined types and aggregates in detail. Second, we present the advances to the CLR integration in SQL Server 2008 which significantly enhances the breath of applications supported by SQL Server. In particular, we describe the support for large (greater than 8000 byte) user-defined types and aggregates, multiple-input user-defined aggregates, and order-aware table valued functions. Third, we show how we leveraged scalar type extensibility to provide a hierarchical identifier data type that enables encoding of keys describing hierarchies as well as built-in support for spatial applications. This support includes both flat- and round-earth spatial types, as well as a spatial index. Fourth, we present how we use Language Integrated Query (LINQ) enhancements in .NET languages to improve developer productivity when creating routines that require data access. Finally, we present preliminary performance results showing the efficiency of streaming TVFs and aggregates relative to equivalent native features.

Using partial information to update materialized views

Abstract This paper lays the theoretical foundations for identifying classes of views and data partitioning strategies that allow efficient incremental view maintenance using “partial information” about the underlying base relations. We present necessary and sufficient conditions for determining how a materialized select-project-join view can be updated using only the view definition, the current view materialization, and the update (i.e., no base relations); and also how to update views using only the view definition, the update, and the updated base relation (i.e., not the other base relations). We generalize the above results to use an arbitrary subset of the base relations and the current view materialization. Our results are especially useful in distributed databases, in disconnected and mobile computing environments where the underlying database is not always accessible, and in data warehousing applications. Our techniques can be used to minimize remote data access and often to completely avoid remote access. This paper gives the intuition and theory necessary for identifying and designing views that are efficiently maintainable using partial information.

Join index, materialized view, and hybrid-hash join: a performance analysis

The problem of efficiently computing a joint between two base relations in the presence of queries and updates to the base relations is dealt with. A performance analysis of three methods: join index, materialized view, and hybrid-hash join, is presented. The first two methods are examples of a strategy based on data caching; they represent two ends of a spectrum of possibilities depending on the attributes projected in the materialization. The third method is an example of a conventional strategy for computing a join from base relations. The results of this study show that the method of choice depends on the environment, and in particular on the update activity on base relations, the join selectivity, and the amount of main memory available. A by-product of this study is a strategy for incrementally maintaining a join index in the presence of updates to the underlying base relations.<<ETX>>

Hosting the .NET Runtime in Microsoft SQL server

The integration of the .NET Common Language Runtime (CLR) inside the SQL Server DBMS enables database programmers to write business logic in the form of functions, stored procedures, triggers, data types, and aggregates using modern programming languages such as C#, Visual Basic, C++, COBOL, and J++. This paper presents three main aspects of this work. First, it describes the architecture of the integration of the CLR inside the SQL Server database process to provide a safe, scalable, secure, and efficient environment to run user code. Second, it describes our approach to defining and enforcing extensibility contracts to allow a tight integration of types, aggregates, functions, triggers, and procedures written in modern languages with the DBMS. Finally, it presents initial performance results showing the efficiency of user-defined types and functions relative to equivalent native DBMS features.