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
email@example.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.
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:
VORTEXaccelerator also includes a tool to monitor statistics as well as allow a database
administrator to free and/or kill slaves and clients.
- 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.
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
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.
Back To Top
Updated 26 Sep 2005.