Use Dynamic Data Workspace as Database Connection Parameter (19-Aug-2009)

Use Dynamic Data Workspace as Database Connection Parameter

Problem Statement

In the large enterprise level solution , such as Data Center of Banking , Telecom, and Transportation industry , the long run system with huge data volume handling is always challenging . We may often encounter the common problems as bellow :
- Poor system performance due to frequent complex DB queries across the whole huge database , while sometimes we may just care a small portion of the data in database . The searching on the whole database is much CPU and memory consuming .
- The system performance enhancement by setting the data retrieval range in application usually impacts the business logic , which brings much risk to the system stability . E.g.

<< Intention >> only retrieve the data in 2008

<< Problem >> always pass 2008 to the db in all queries

Database

Application

Locate the data partition by 2008

 Data Partition for 2007

 Data Partition for 2008

…..

 Data Partition for 2009

Ivention Points:

1. Propose the idea Dynamic Data Workspace (DDW) to represents a portion of data across tables in the database as the workspace to limit the scope of data retrieval .
2. Propose to use the Dynamic Data Workspace as database connection parameter to minimize the impactto business logic in application layer.
3. Propose a couple of patterns for the Dynamic Data Workspace , including fixed Workspace , floating Workspace, open Workspace, closed

1

Workspace, etc.
4. Propose a well organized physical data modeling method to make use of the Dynamic Data Workspace .
5. Propose to associate the workspace to the database partition .

Terminology

- Dynamic Data Workspace (DDW) is the scope of data across tables in the database by setting one or more workspace keys to limit the scope of data retrieval.
- Workspace Key (WK) can be translated to specified partition key to indicate the boundary of the Dynamic Data Workspace .
- Partition is the existing concept . The table can be separated into many partitions according to specified partition key .
- Partition Key (PK) is the existing concept. A column in a table with specified value can be set aspartition key to indicate the boundary of the partition .

WK

DDW

Patterns of the Dynamic Data Workspace

1. Fixed Workspace is used when just care the data that in a fix period , regardless of the query time . The begin time and end time can be set as the period.
2. Floating Workspace is used when the relative time to the query moment is needed . The earlier timeand later time can be set as the relative period to the query moment .
3. Closed Workspace is used when both sides of the workspace are limited .
4. Open Workspace is used when the boundary in one side or both sides is not limited .

Sample

2

Table:

Partition Key:

Partition