Trifox, Inc. -- Database Access Experts

Lighter Load, Faster Performance

And No Code Changes ...

Several hundred users are all running the same application against the same database -- and all satisfied with the response. You have a DBA who smiles in the afternoon, and application programmers who devote their energies to adding features instead of struggling with poor performance.

It's no dream. Putting VORTEXaccelerator in your database access configuration does significantly lighten the load on your database and improve its performance without anyone touching a line of application code.

How? By effectively managing the database cursors.

To see VORTEXaccelerator's impact on your environment, write info@trifox.com and we'll work with you to set up an evaluation.

What are Cursors?

Cursors, specifically database cursors, are the elements that databases use to point to the data that meets the application's request. Cursors are created by the database, and usually exist only as long as it takes for the database to respond to the application. When the application makes another request, the database creates another cursor for that job and closes it when the request is completed.

As you can imagine, when the application is interactive the database can be very busy creating cursors and closing them. In fact, interactive applications most often ask for information from a database. This information is translated into what relational databases call SQL SELECT statements. Creating the cursor is the most resource-intensive part of the entire process.

Database Perspective

A database's relationship with an application is momentary: the application requests some service and the database tries to provide it -- without any context to optimize the process. Beyond the statements they receive from client applications, databases have no way of knowing what kind of work the clients are doing, and they make no assumptions.

VORTEXaccelerator, however, can make good "guesses" about the kind of work the applications are doing, and thus optimize the environment for that work.

In batch application where vast amounts of data are processed serially, an extra open cursor does not seem particularly important. But in an interactive application, such as online order entry, especially in a real-time environment, most activities involve user data validation. A single order, can require hundreds of simple validations such as verifying the availability of an item in inventory, the user's permission to order it, and any special circumstances. The database only sees the SELECT, and has no way of knowing which activity the request is associated with.

The database goes through many steps in responding to a simple SELECT statement. First, the database parses the statement to check that the syntax (language) is correct and that it makes sense. Then it must make sure that any tables or columns in the request actually exist. The database is also responsible for making sure that the user making the request is authorized to get information from the tables or columns in the statement. And finally, the database must determine how to access the data and retrieve it most efficiently.

Only after all these steps have been completed does the database open a cursor and replace the variables in the request with the actual table and column names. The last steps, fetching the data and then closing the cursor complete the transaction.

The first step, which involves many disk operations, is expensive in terms of database resources. Depending on the database engine, the cost can be as high as 10 times more than all the other combined processing steps.

How It Works

Consider a scenario in which 100 people are running the same set of order entry applications. If each application requires between 50-100 open cursors at any given time, even the most capable database engine's performance will suffer. Assuming that each cursor uses 10KB, a worst-case scenario could easily consume up to 100MB for cursor memory alone.

Since most users are running the same application most of the time, VORTEXaccelerator offers the perfect solution: sharing cursors between users saves database resources and preserves the engine's performance so all 100 users are able to order items efficiently.

How VORTEXaccelerator works:

The main process, called vtxmux, starts.
This "transaction cop" initializes a portion of shared memory that contains a control area, SQL hash table, and client and slave work areas (all user-configurable).
When an application (which has already been linked with the VORTEXaccelerator libraries) connects to a database, vtxmux takes control of it as a client.
To run a SQL statement, the client sends a request to vtxmux, which assigns the client to a database slave. If it can't find an available slave or start a new one, it places the client in a "first come, first served" queue.
When the client's request has been served by the database, the slave determines if the client/slave connection should be broken. For INSERT, UPDATE, or DELETE statements, vtxmux maintains the connection until a COMMIT or ROLLBACK is performed. Otherwise, for a SELECT statement, the database slave breaks the connection.

VORTEXaccelerator also includes a tool to monitor statistics as well as allow a database administrator to free and/or kill slaves and clients.

Summary

Reusing cursors is a straightforward process: Once the first step is completed, the following transactions use the open cursor and simply begin fetching the data, binding the parameters as necessary. The cursor is closed only when an application exits or the cursor is required for another statement.

Even though well-written applications usually know how to use existing cursors, not all of them take advantage of the facility. When many users are running the same applications independently on different machines, and you just don't have the time to re-engineer all the applications, getting the most out of cursor-reuse requires an independent manager like VORTEXaccelerator.

Who knows, once users are working at their best speeds, what could happen to business.