Improving Database Integration with ODBC
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:
- Network communication
Reducing network communication may increase ODBC performance multiple times. Arrays of parameters used instead of
INSERTstatements, 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.
- 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.
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.
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.
- Oracle Enhances Its Database with Real Application Testing
- Using Apatar to Integrate Data from/to MySQL
- Open-Source Databases Break into the Enterprise