Caching Oracle Data for ASP.NET Applications
Introduction
For building scalable and high-performance Web based applications, ASP.NET provides a feature called data caching. Data caching enables programmatic storing of frequently accessed data objects in memory. This feature can be extended to vastly improve performance for ASP.NET applications that query data stored in an Oracle database. This article describes a strategy for caching Oracle database data in ASP.NET Web applications deployed using a Web Farm environment. This technique enables caching of frequently accessed Oracle database data in memory rather than making frequent database calls to retrieve the data. This helps to avoid unnecessary roundtrips to the Oracle database server. Further the article proposes an implementation for maintaining the cached data so it is always in sync with the corresponding data in the Oracle database.
Data Caching in ASP.NET
Data Caching in ASP.NET is facilitated via the Cache class and the CacheDependency class in the System.Web.Caching namespace. The Cache class provides methods for inserting data and retrieving data from the cache. The CacheDependency class enables dependencies to be specified for the data items placed in the cache. An expiration policy for an item can be specified when we add it to the cache using the Insert method or Add method. We can define the life span for an item in the cache by using the absoluteExpiration parameter in the Insert method. This parameter allows one to specify the exact datetime that the corresponding data item will expire. One can also use the slidingExpiration parameter, specifying the elapsed time before the item will expire based on the time it was accessed. Once the item expires, it is removed from the cache. Attempts to access it will return a null value unless the item is added to the Cache again.
Specifying Dependencies for Cache
ASP.NET allows us to define the dependency of a cached item based on an external file, a directory, or another cached item. These are described as file dependencies and key dependencies. If a dependency changes, the cached item gets automatically invalidated and removed from the cache. We can use this approach to delete items from the cache when the corresponding data source changes. For example, if we write an application that retrieves data from an XML file and displays it in a grid, we can store the data from the file in the Cache and specify a Cache dependency on the XML file. When the XML file is updated, the data item gets removed from the cache. When this event occurs, the application reads the XML file again, and the latest copy of the data item is inserted into the cache again. Further, callback event handlers can be specified as a listener for getting notified when the cache item gets deleted from the cache. This eliminates the need to continuously poll the cache to determine whether the data item has been invalidated.
ASP.NET Cache Dependency on Oracle Database
Let us consider a scenario where data is stored in the Oracle database and accessed by an ASP.NET application using ADO.NET. Furthermore, let us assume that the data in the database table(s) is generally static but accessed frequently by the Web application. In a nutshell, there are very few DML operations on the table but lots of Selects on the data. Such a scenario is ideal for data caching. But unfortunately, ASP.NET does not allow a dependency to be specified whereby a cache item is dependent on data stored in a database table. Furthermore, in real world Web based systems, the Web server and the Oracle database server could be potentially running on different machines, making this cache invalidation process more challenging. Also most Web-based applications are deployed using Web farms with instances of the same application running on multiple Web servers for load balancing. This scenario makes the database caching problem slightly more complex.
For exploring the solution to the above problem, let's put together a sample Web application to illustrate how it can be implemented. For our example, we use ASP.NET application implemented in VB .Net communicating with the Oracle 9i database using Oracle Data Provider for .NET (ODP).
In this example, consider a table named Employee in the Oracle database. We define a trigger for insert, update and delete operations on the Employee table. This trigger calls a PL/SQL function that serves as a wrapper for a Java stored procedure. This Java stored procedure in turn will be responsible for updating the Cache dependency file.
ASP.NET Tier Implementation Using VB.NET
On the ASP.NET tier, we have a listener class containing a callback method to handle the notification when the cache item gets invalidated.
The callback method RemovedCallback is registered by using a delegate function. The callback method onRemove declaration must have the same signature as the CacheItemRemovedCallback delegate declaration.
Dim onRemove As CacheItemRemovedCallback = Nothing
onRemove = New CacheItemRemovedCallback(AddressOf RemovedCallback)
The definition for the listener event handler method RemovedCallback responsible for handling the notification from the database trigger is illustrated below. When the cache item gets invalidated, data is retrieved from the database by using the database method call getRecordFromdatabase(). The parameter "key" refers to the index location for the item removed from the cache. The parameter "value" refers to the data object removed from the cache. The parameter "CacheItemRemovedReason" specifies the reason causing the data item to be removed from the cache.
PublicSub RemovedCallback(ByVal key AsString, ByVal value AsObject,
ByVal reason As CacheItemRemovedReason)
Dim Source As DataView
Source = getRecordFromdatabase()
Cache.Insert("employeeTable ", Source, New
System.Web.Caching.CacheDependency("d:\download\tblemployee.txt"),
Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration,
CacheItemPriority.Normal, onRemove)
EndSub
The method getRecordFromdatabase() is responsible for querying the database table Employee and it returns a DataView object reference. It makes use of a stored procedure called getEmployee to abstract the SQL for retrieving the data from the Employee table. The method expects a parameter called p_empid representing the primary key for the Employee table.
PublicFunction getRecordFromdatabase (ByVal p_empid As Int32) As DataView
Dim con As OracleConnection = Nothing
Dim cmd As OracleCommand = Nothing
Dim ds As DataSet = Nothing
Try
con = getDatabaseConnection(
"UserId=scott;Password=tiger;Data Source=testingdb;")
cmd = New OracleCommand("Administrator.getEmployee", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New OracleParameter("employeeId",
OracleDbType.Int64)).Value = p_empid
Dim param AsNew OracleParameter("RC1", OracleDbType.RefCursor)
cmd.Parameters.Add(param).Direction = ParameterDirection.Output
Dim myCommand AsNew OracleDataAdapter(cmd)
ds = New DataSet
myCommand.Fill(ds)
Dim table As DataTable = ds.Tables(0)
Dim index As Int32 = table.Rows.Count
Return ds.Tables(0).DefaultView
Catch ex As Exception
ThrowNew Exception("Exception in Database Tier Method
getRecordFromdatabase () " + ex.Message, ex)
Finally
Try
cmd.Dispose()
Catch ex As Exception
Finally
cmd = Nothing
EndTry
Try
con.Close()
Catch ex As Exception
Finally
con = Nothing
EndTry
EndTry
EndFunction
The function getDatabaseConnection accepts a connectionstring as an argument and returns an OracleConnection object reference.
PublicFunction getDatabaseConnection(ByVal strconnection as string) As
OracleConnection
Dim con As Oracle.DataAccess.Client.OracleConnection = Nothing
Try
con = New Oracle.DataAccess.Client.OracleConnection
con.ConnectionString = strconnection
con.Open()
Return con
Catch ex As Exception
ThrowNew Exception("Exception in Database Tier Method
getOracleConnection() " + ex.Message, ex)
EndTry
EndFunction
Oracle Database Tier Implementation
The Trigger body defined for DML events on the Employee Table is shown below. This trigger simply invokes a PL/SQL wrapper function for updating an operating system file called tblemployee.txt. Copies of this file are updated on two different machines called machine1 and machine2 that are running different instances of the same Web application to enable load balancing. Here administrator refers to the owner of the schema objects in the Oracle database.
begin
administrator.plfile('machine1\\download\\ tblemployee.txt');
administrator.plfile('machine2\\download\\ tblemployee.txt');
end;
For updating the cache dependency file, we will need to write a C function or a Java stored procedure. In our example, we chose a Java stored procedure since Oracle database server has a built-in JVM, making it easy to write Java stored procedures. Adequate memory must be allocated for the Java Pool in the System global area (SGA) of the Oracle instance. The static method updateFile accepts an absolute pathname as a parameter and creates the cache dependency file in the appropriate directory. If the file already exists, it is deleted and created again.
import java.io.*;
public class UpdFile {
public static void updateFile(String filename) {
try {
File f = new File(filename);
f.delete();
f.createNewFile();
}
catch (IOException e)
{
// log exception
}
}
};
The pl/sql wrapper implementation is shown below. The wrapper function accepts the filename as a parameter and invokes the method updateFile in the Java stored procedure.
(p_filename IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'UpdFile.updateFile (java.lang.String)';
Database Caching in a Web Farm Deployment
As illustrated in the example we have discussed, Web Servers machine1 and machine2 constitute the Web farm to provide load balancing for our Web application. Each machine runs an instance of the same Web application. In this scenario, each instance of the Web application can have its own copy of the cached data stored in its Cache object. When the employee table changes, the corresponding database trigger updates the file tblemployee.txt on both of these machines. Each instance of the Web application specifies a cache dependency on the local file tblemployee.txt, and the cache for both the instances in the Web Farm gets updated correctly, enabling the data cache on both the instances to remain in sync with the database table Employee.
Conclusion
Data Caching can be an effective technique for optimizing ASP.NET applications using the Oracle database. Although ASP.NET does not allow database dependency to be specified for the cache, Oracle triggers in conjunction with Java stored procedures can be used to extend the power of the ASP.NET cache to enable Oracle database caching. This technique can also be applied to Web Farm deployments
For building scalable and high-performance Web based applications, ASP.NET provides a feature called data caching. Data caching enables programmatic storing of frequently accessed data objects in memory. This feature can be extended to vastly improve performance for ASP.NET applications that query data stored in an Oracle database. This article describes a strategy for caching Oracle database data in ASP.NET Web applications deployed using a Web Farm environment. This technique enables caching of frequently accessed Oracle database data in memory rather than making frequent database calls to retrieve the data. This helps to avoid unnecessary roundtrips to the Oracle database server. Further the article proposes an implementation for maintaining the cached data so it is always in sync with the corresponding data in the Oracle database.
Data Caching in ASP.NET
Data Caching in ASP.NET is facilitated via the Cache class and the CacheDependency class in the System.Web.Caching namespace. The Cache class provides methods for inserting data and retrieving data from the cache. The CacheDependency class enables dependencies to be specified for the data items placed in the cache. An expiration policy for an item can be specified when we add it to the cache using the Insert method or Add method. We can define the life span for an item in the cache by using the absoluteExpiration parameter in the Insert method. This parameter allows one to specify the exact datetime that the corresponding data item will expire. One can also use the slidingExpiration parameter, specifying the elapsed time before the item will expire based on the time it was accessed. Once the item expires, it is removed from the cache. Attempts to access it will return a null value unless the item is added to the Cache again.
Specifying Dependencies for Cache
ASP.NET allows us to define the dependency of a cached item based on an external file, a directory, or another cached item. These are described as file dependencies and key dependencies. If a dependency changes, the cached item gets automatically invalidated and removed from the cache. We can use this approach to delete items from the cache when the corresponding data source changes. For example, if we write an application that retrieves data from an XML file and displays it in a grid, we can store the data from the file in the Cache and specify a Cache dependency on the XML file. When the XML file is updated, the data item gets removed from the cache. When this event occurs, the application reads the XML file again, and the latest copy of the data item is inserted into the cache again. Further, callback event handlers can be specified as a listener for getting notified when the cache item gets deleted from the cache. This eliminates the need to continuously poll the cache to determine whether the data item has been invalidated.
ASP.NET Cache Dependency on Oracle Database
Let us consider a scenario where data is stored in the Oracle database and accessed by an ASP.NET application using ADO.NET. Furthermore, let us assume that the data in the database table(s) is generally static but accessed frequently by the Web application. In a nutshell, there are very few DML operations on the table but lots of Selects on the data. Such a scenario is ideal for data caching. But unfortunately, ASP.NET does not allow a dependency to be specified whereby a cache item is dependent on data stored in a database table. Furthermore, in real world Web based systems, the Web server and the Oracle database server could be potentially running on different machines, making this cache invalidation process more challenging. Also most Web-based applications are deployed using Web farms with instances of the same application running on multiple Web servers for load balancing. This scenario makes the database caching problem slightly more complex.
For exploring the solution to the above problem, let's put together a sample Web application to illustrate how it can be implemented. For our example, we use ASP.NET application implemented in VB .Net communicating with the Oracle 9i database using Oracle Data Provider for .NET (ODP).
In this example, consider a table named Employee in the Oracle database. We define a trigger for insert, update and delete operations on the Employee table. This trigger calls a PL/SQL function that serves as a wrapper for a Java stored procedure. This Java stored procedure in turn will be responsible for updating the Cache dependency file.
ASP.NET Tier Implementation Using VB.NET
On the ASP.NET tier, we have a listener class containing a callback method to handle the notification when the cache item gets invalidated.
The callback method RemovedCallback is registered by using a delegate function. The callback method onRemove declaration must have the same signature as the CacheItemRemovedCallback delegate declaration.
Dim onRemove As CacheItemRemovedCallback = Nothing
onRemove = New CacheItemRemovedCallback(AddressOf RemovedCallback)
The definition for the listener event handler method RemovedCallback responsible for handling the notification from the database trigger is illustrated below. When the cache item gets invalidated, data is retrieved from the database by using the database method call getRecordFromdatabase(). The parameter "key" refers to the index location for the item removed from the cache. The parameter "value" refers to the data object removed from the cache. The parameter "CacheItemRemovedReason" specifies the reason causing the data item to be removed from the cache.
PublicSub RemovedCallback(ByVal key AsString, ByVal value AsObject,
ByVal reason As CacheItemRemovedReason)
Dim Source As DataView
Source = getRecordFromdatabase()
Cache.Insert("employeeTable ", Source, New
System.Web.Caching.CacheDependency("d:\download\tblemployee.txt"),
Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration,
CacheItemPriority.Normal, onRemove)
EndSub
The method getRecordFromdatabase() is responsible for querying the database table Employee and it returns a DataView object reference. It makes use of a stored procedure called getEmployee to abstract the SQL for retrieving the data from the Employee table. The method expects a parameter called p_empid representing the primary key for the Employee table.
PublicFunction getRecordFromdatabase (ByVal p_empid As Int32) As DataView
Dim con As OracleConnection = Nothing
Dim cmd As OracleCommand = Nothing
Dim ds As DataSet = Nothing
Try
con = getDatabaseConnection(
"UserId=scott;Password=tiger;Data Source=testingdb;")
cmd = New OracleCommand("Administrator.getEmployee", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New OracleParameter("employeeId",
OracleDbType.Int64)).Value = p_empid
Dim param AsNew OracleParameter("RC1", OracleDbType.RefCursor)
cmd.Parameters.Add(param).Direction = ParameterDirection.Output
Dim myCommand AsNew OracleDataAdapter(cmd)
ds = New DataSet
myCommand.Fill(ds)
Dim table As DataTable = ds.Tables(0)
Dim index As Int32 = table.Rows.Count
Return ds.Tables(0).DefaultView
Catch ex As Exception
ThrowNew Exception("Exception in Database Tier Method
getRecordFromdatabase () " + ex.Message, ex)
Finally
Try
cmd.Dispose()
Catch ex As Exception
Finally
cmd = Nothing
EndTry
Try
con.Close()
Catch ex As Exception
Finally
con = Nothing
EndTry
EndTry
EndFunction
The function getDatabaseConnection accepts a connectionstring as an argument and returns an OracleConnection object reference.
PublicFunction getDatabaseConnection(ByVal strconnection as string) As
OracleConnection
Dim con As Oracle.DataAccess.Client.OracleConnection = Nothing
Try
con = New Oracle.DataAccess.Client.OracleConnection
con.ConnectionString = strconnection
con.Open()
Return con
Catch ex As Exception
ThrowNew Exception("Exception in Database Tier Method
getOracleConnection() " + ex.Message, ex)
EndTry
EndFunction
Oracle Database Tier Implementation
The Trigger body defined for DML events on the Employee Table is shown below. This trigger simply invokes a PL/SQL wrapper function for updating an operating system file called tblemployee.txt. Copies of this file are updated on two different machines called machine1 and machine2 that are running different instances of the same Web application to enable load balancing. Here administrator refers to the owner of the schema objects in the Oracle database.
begin
administrator.plfile('machine1\\download\\ tblemployee.txt');
administrator.plfile('machine2\\download\\ tblemployee.txt');
end;
For updating the cache dependency file, we will need to write a C function or a Java stored procedure. In our example, we chose a Java stored procedure since Oracle database server has a built-in JVM, making it easy to write Java stored procedures. Adequate memory must be allocated for the Java Pool in the System global area (SGA) of the Oracle instance. The static method updateFile accepts an absolute pathname as a parameter and creates the cache dependency file in the appropriate directory. If the file already exists, it is deleted and created again.
import java.io.*;
public class UpdFile {
public static void updateFile(String filename) {
try {
File f = new File(filename);
f.delete();
f.createNewFile();
}
catch (IOException e)
{
// log exception
}
}
};
The pl/sql wrapper implementation is shown below. The wrapper function accepts the filename as a parameter and invokes the method updateFile in the Java stored procedure.
(p_filename IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'UpdFile.updateFile (java.lang.String)';
Database Caching in a Web Farm Deployment
As illustrated in the example we have discussed, Web Servers machine1 and machine2 constitute the Web farm to provide load balancing for our Web application. Each machine runs an instance of the same Web application. In this scenario, each instance of the Web application can have its own copy of the cached data stored in its Cache object. When the employee table changes, the corresponding database trigger updates the file tblemployee.txt on both of these machines. Each instance of the Web application specifies a cache dependency on the local file tblemployee.txt, and the cache for both the instances in the Web Farm gets updated correctly, enabling the data cache on both the instances to remain in sync with the database table Employee.
Conclusion
Data Caching can be an effective technique for optimizing ASP.NET applications using the Oracle database. Although ASP.NET does not allow database dependency to be specified for the cache, Oracle triggers in conjunction with Java stored procedures can be used to extend the power of the ASP.NET cache to enable Oracle database caching. This technique can also be applied to Web Farm deployments
0 comments:
Post a Comment