Running Oracle .NET Applications on TimesTen

Purpose

This tutorial shows how to convert an existing Oracle .NET database application to run with Oracle In-Memory Database Cache, thus improving application transaction response time by caching a performance-critical subset of Oracle tables into a TimesTen in-memory database running in the appplication tier. This tutorial provides a step by step guide to run a pre-created .NET database application against both an Oracle database and a TimesTen in-memory database. The application connects to a database and uses PL/SQL stored procedures to implement its business logic. This tutorial compares the difference in application response time between running an application against a standalone Oracle database, versus an Oracle database with TimesTen used as an in-memory cache database.

Time to Complete

Approximately 30 minutes.

Overview

This tutorial goes through the process of setting up your Windows operating system to run a .NET database application against either an Oracle or TimesTen database.

Assuming that the .NET application currently runs against an Oracle database, the steps necessary to run the same application against a TimesTen database are:

  1. Install TimesTen 11.2.1.6.1 or higher
  2. Create a TimesTen database and set it up to be an in-memory cache database. This involves creating users and running some scripts on both the TimesTen database and the Oracle database from which data will be cached.
  3. Cache database tables from the Oracle database into TimesTen.
  4. If necessary for the application, create additional database objects like regular (non-cache) tables, indexes, sequences, materialized views, synonyms, or PL/SQL packages, within the TimesTen in-memory cache database.
  5. Make sure Oracle Data Provider for .NET (ODP.NET) is 11.2.0.2.0 or higher
  6. Configure ODP.NET to connect to the TimesTen in-memory database by including the appropriate entry in the TNSNAMES.ORA file.
  7. Change the database connection string of the .NET application to connect to TimesTen.

In this tutorial, a .NET database application has been pre-created. We will run the application against an Oracle and TimesTen database. It is assumed that you have TimesTen installed in your operating system, the TimesTen in-memory database (named ordermatching) is created and configured to serve as an in-memory cache database, and that two cache groups, namely READONLY_SYMBOLS and AWT_FILLED_ORDERS are created in ordermatching. The steps to do these are contained in the prerequisite tutorials, which are:

  1. Creating an In-Memory Cache Database on Windows
  2. Caching Oracle Database Tables Using Oracle In-Memory Database Cache

Make sure to complete these tutorials first before starting this one. This tutorial will then show the rest of the process to convert the pre-created .NET database application currently designed to connect to an Oracle database into an application that connects to a TimesTen in-memory cache database.

Scenario

The pre-created .NET database application is a simple trading application that runs 1000 iterations of:

  1. Placing a simulated buy/sell order
  2. Modifying a quote in the market
  3. Matching buyers and sellers based on the orders and quotes in the market

It places a simulated order by generating random values for the order parameters, which are: side (buy or sell), symbol, quantity, and order limit. It submits a simulated quote by generating random values for the quote parameters, which are: symbol, new bid price, new ask price, and new last price.

In the application code, the following database tables are used:

TABLE NAME DESCRIPTION
BLACKLIST contains the list of application users that are not allowed to make trades in the market
FILLED_ORDERS contains the list of buy / sell orders that have been processed
ORDERS contains the list of outstanding / unfilled orders that are currently in the market
ORDER_HISTORY contains the order history of each of the users in the system
QUOTES contains the list of quotes that are used by the application.
SYMBOLS contains the list of stock tickers / acronyms that are used by the application.
USER_DATA contains the information about the users that exist in the system.

In this tutorial, we will compile this .NET application and run it against an Oracle and TimesTen database. The application schema has already been set up on the Oracle database in the prerequisite tutorials.

Prerequisites

Before starting this tutorial, you should:

.

Complete these two prerequisite tutorials:

  1. Creating In-Memory Cache Database on Windows

    In this tutorial, a TimesTen database called ordermatching is created and associated with an Oracle database to be later used as an in-memory cache database

  2. Caching Oracle Database Tables Using Oracle In-Memory Database Cache

    In this tutorial, data from an Oracle database is cached into a TimesTen in-memory cache database called ordermatching.

.

Have an installation of Oracle Data Provider for .NET (ODP.NET) 11.2.0.2.0 or higher installed on the same machine as TimesTen. The latest ODP.NET production release is part of Oracle Data Access Components (ODAC) 11.2 Release 3 or higher.

Oracle Data Provider for .NET (ODP.NET) is an implementation of the Microsoft ADO.NET interface. ODP.NET support for Oracle TimesTen In-Memory Database provides fast and efficient ADO.NET data access from .NET client applications to TimesTen databases. For more information about ODP.NET in TimesTen, see the ODP.NET Support for TimesTen User's Guide.

.

Have an installation of Visual Studio 2010

.

Download the VS_Ordermatching folder. This folder contains the Visual Studio project used in this tutorial. You can download a zipped version of the folder here.

Set Up the Oracle Database to Run the Application

In the prerequisite tutorials, the database schema for the .NET application was created in the Oracle database, hence no further configuration needs to be done in the Oracle database for the application to run.

Set Up the TimesTen Database to Run the Application

In the prerequisite tutorials, two tables from an Oracle database were cached into the TimesTen in-memory cache database, ordermatching. These tables were the SYMBOLS table, cached as a readonly cache group, and the FILLED_ORDERS table, cached as an asynchronous writethrough (AWT) cache group.

Now that the cache groups have been created, we will configure the ordermatching database so that it has the other necessary tables and PL/SQL procedures used by the application. We will create the rest of the tables as regular TimesTen tables (non-cache tables). After this, we will compile the PL/SQL package used by the application in TimesTen. Note that these tables and PL/SQL procedures are the exact same tables and procedures as the ones earlier created in the Oracle database. To simplify the process of doing this, a SQL script has been created. Running this SQL script will create the tables and PL/SQL package. Download the script here and follow the steps below to run the script.

.

Open ttIsql and connect to the ordermatching database using the TTORAUSER user by opening a Windows command prompt and typing the following:

ttisql -connstr "dsn=ordermatching;uid=ttorauser"

You will be prompted to enter the password for the user, which in this case is timesten.

Note: If the TimesTen environment variables are not part of your operating system's path variables, you must make sure that the environment in which you run ttIsql contains the TimesTen environment variables. You can do this by running <tt_installation_home>/bin/ttenv.bat in the command prompt prior to running the ttIsql command.

 

.

Run the SQL script in ttIsql by typing:

@<disk_location>\create_ordermatching_schema.sql;

 

.

Verify that the tables and PL/SQL package have been created in TimesTen by typing:

select object_type, object_name
from all_objects
where owner = 'TTORAUSER'
order by 1,2;

 

Configure ODP.NET to Use with TimesTen

Before we can run the .NET application against a TimesTen database, we first have to make sure that ODP.NET is installed and configured for TimesTen.

.

Here are a few important items to keep in mind about installing ODP.NET to use with TimesTen:

  1. ODP.NET requires TimesTen Release 11.2.1.6.1 or later.
  2. The installation process for ODP.NET is independent of the TimesTen environment. Nothing is installed into the TimesTen installation directories.
  3. The location of the installed ODP.NET binaries varies depending on your type of Oracle installation and version of .NET.
  4. The 32-bit version of ODP.NET must be used with a 32-bit instance of the TimesTen database or TimesTen client. Likewise, the 64-bit version of ODP.NET must be used with a 64-bit instance of the TimesTen database or TimesTen client.
  5. To use ODP.NET with TimesTen, ODP.NET should be installed on the same system as TimesTen Data Manager or TimesTen Client, and a TimesTen DSN must be configured.

For more information, see the ODP.NET Support for TimesTen User's Guide.

 

.

As noted in the previous step, to use ODP.NET and run a .NET application against a TimesTen database, a TimesTen DSN must be configured. A DSN (data source name) is a logical name that identifies a TimesTen database. In the prerequisite tutorials, we have already specified ordermatching as the DSN of our TimesTen database, however we need to make this DSN visible to the .NET application by including the following entry in the TNSNAMES.ORA file:

ordermatching =
   (DESCRIPTION =
      (CONNECT_DATA =
         (SERVICE_NAME = ordermatching) (SERVER = timesten_direct)))

The SERVICE_NAME attribute specifies the DSN defined in the ODBC Data Source Administrator.
The SERVER attribute specifies what type of connection is made to a TimesTen database; timesten_direct specifies a direct connection to a TimesTen database, while timesten_client specifies a client/server connection.

 

Run the Application Against the Oracle Database

For convenience, a command line .NET database application has already been created as a Visual Studio 2010 project. We will now open the application and run it against the Oracle database.

.

Start Microsoft Visual Studio 2010

Note: If the TimesTen environment variables are not part of your operating system's path variables, you must make sure that the environment in which you run Visual Studio contains the TimesTen environment variables. You can do this by running <tt_installation_home>/bin/ttenv.bat in a command prompt first, then opening Visual Studio from within the same command prompt.

 

.

From the File menu, select Open > Project/Solution....

 

.

Open the Ordermatching.sln file.
It is located inside the Ordermatching folder found in the VS_Ordermatching folder that you downloaded earlier as a prerequisite for this tutorial.

 

.

Ensure that the program references the Oracle.DataAccess.dll assembly file found in your ODAC installation by right clicking References under the Solution Explorer pane, and selecting Add Reference.

 

.

The "Add Reference" menu will appear. Click on the .NET tab and select Oracle.DataAccess from among the options. Cick Ok.

Including Oracle.DataAccess as a reference enables the application to connect to an Oracle or TimesTen database.

 

.

You should now see Oracle.DataAccess listed among the list of references in the Solution Explorer pane. Expand the References list if necessary.

 

.

Still within the Solution Explorer pane, double click on Program.cs to display the application code in the main Visual Studio tab. Review the code.

 

.

Scroll to the line in the application that contains the connection string. Notice that currently it is:

const string connStr = "Data Source=ttorcl;User ID=ttorauser;Password=oracle";

If necessary, change this connection string to match the appropriate connection string to the Oracle database by changing the Data Source entry to match the TNS Net Service Name of the Oracle database where the application schema was created.

 

.

After reviewing the code, hit the Start Debugging button. This will run the application.

 

.

The application will then be run against the Oracle database, which will open a new Windows command prompt displaying the results of the application's run.

The application runs 1000 simulated transactions. At the end of the application's run, it displays the number of selects, inserts, and updates to database tables, as well as the number of executed orders (the number of buy/sell orders that were matched) and the number of submitted orders (which should be 1000). Below is a sample of the application's output.

 

.

Take note of the time value outputted by the application, as later in this tutorial we will compare this to the value outputted by running the same application against a TimesTen in-memory cache database.

Note: Timing results will vary depending on machine environment. You may get a different result than what is shown here.

 

Run The Application Against the TimesTen Database

We can run the application against TimesTen similar to the way we ran it against Oracle, simply by changing the connection string.

.

To change the application's connection string so it connects to a TimesTen in-memory database, scroll to the line where the connection string is indicated and change the Data Source, User ID and Password fields to reflect the ordermatching database connection attributes. Change the connection string to:

const string connStr = "Data Source=ordermatching;User ID=ttorauser;Password=timesten";

 

.

After reviewing the code, hit the Start Debugging button. This will run the application.

Note: If running the application displays an error, one likely cause is that the TimesTen environment variables are not part of your operating system's path variables. You must make sure that the environment in which you run Visual Studio contains the TimesTen environment variables. You can do this by opening a command prompt, running <tt_installation_home>/bin/ttenv.bat, and opening Visual Studio within that command prompt.

 

.

The application will then be run against the TimesTen ordermatching database, which will open a new Windows command prompt displaying the results of the application's run.

 

.

Take note of the time value outputted by the application, and compare this to the value outputted by running the same application against the Oracle database.

Summary

The application is made using standard ODP.NET code. This means there is no new API to learn for ODP.NET developers, and migrating existing .NET applications to use TimesTen can be done with minimal effort. Thus, if you find out late in the development process that your application needs faster performance, you should consider using In-Memory Database Cache. See the ODP.NET Support for TimesTen User's Guide for more information.

In this tutorial, you have learned how to:

Resources

Hardware and Software Engineered to Work Together Copyright © 2011, Oracle and/or its affiliates. All rights reserved