Improving Database Integration with ODBC

by Olga BelokurskayaOctober 12, 2009

The performance of your ODBC connection and the way you handle it may have an impact on the success of your data integration project.

Featured image: RDBMS structure (credit)

Table of Contents

How to make ODBC apps work properly?

Successful data integration with database sources depends on many things of which not the least is the performance of ODBC applications. There are several factors that affect ODBC performance. Improvements of those factors help to make ODBC applications faster—which, in its turn, helps to improve and avoid issues with data integration.

In their book “The Data Access Handbook: Achieving Optimal Database Application Performance and Scalability?“, John Goodson and Robert A. Steward explore how one can improve the operation of middleware, such as connectors. In particular, chapter five focuses on “writing good code” for ODBC applications. Here are some of the factors that contribute to making ODBC-related intermediate software work faster, according to the authors:

John Goodson

Network communication
Reducing network communication may increase ODBC performance multiple times. Arrays of parameters used instead of INSERT statements, for example, reduce the time required to complete the operation.
Choosing the way the transactions are handled
To improve ODBC performance it’s essential to choose the right way transactions are handled. Thus, for example, using manual commits instead of auto-commits gives better control over the work committed.

Rob Steward

Connection pooling
When an ODBC application has several users, connection pooling is a good way to increase connection efficiency.
SQL queries
The efficiency of SQL queries is an important factor affecting the speed of ODBC performance. If something is wrong with it, issues may occur with data filtering causing the driver to get unnecessary data (sometimes the amount of this data is very big), which slows down application performance. Using well-formed and rightly executed queries improves the performance greatly.

ODBC provides good opportunities for data integration, granting access to multiple data sources through one application. So, keeping the application’s performance high will benefit the process of data integration.

Enterprise’s multiple integration technologies, including ODBC (image credit)

Now with Apatar

The Apatar team has recently released the connector for ODBC sources, something that plenty of our users have been asking and waiting for. The fact that the connector was quite highly demanded kind of argues the talks heard for about a couple of years of ODBC being an old and forgotten means of accessing databases. Well, actually, ODBC remains widely used in both SMBs and large enterprises. As any technology, it has its pros and cons, of course.

The main ODBC’s advantage is its flexibility and versatility—having appropriate drivers for a connection, users may access different databases they need. So, in fact, the new connector allows anyone to perform needed operations on data exactly in the sources a user’s ODBC has access to. In other words, Apatar may lack a connector for a particular database, but as soon as this database is among the user’s ODBC sources, he/she may integrate or synchronize data from this source with the data from other sources.

Apatar’s connector for ODBC sources

So, I think, the creation of the connector for ODBC sources, positively, was a great thing, which may help users in their data integration and ETL initiatives.

For more, check out the ODBC connector tutorial.