Evan Levy's Answer: There are core functions that every database supports to allow users (and applications) to query data. Some of these functions include database connection, the actual query dialect or SQL, query submission processing and answer result formatting. In the early days, if a tool wanted to log on and connect to a database server, the tool needed to understand the specific API calls and parameters supported by each individual database product. The APIs that exist to support access to SQL Server, Oracle or DB2 are entirely different. Additionally, each database product has their own unique version (or enhancements) to SQL as well as their own way of representing result data (like binary, floating point and other nonstandard data types).
ODBC was a standard introduced to simplify tool-to-database interaction and support. With ODBC, an interface was specified that allowed a single tool to interact with any database without special programming. The issues associated with database-to-tool development complexity disappeared. With ODBC, every ODBC compatible tool could access and interact with any ODBC database. ODBC introduced the concept of a set of database access interface standards and data presentation standards
When an application connects to a database via an "ODBC connector," all of the API calls as well as the SQL and data is converted by the ODBC connector to the native details required by the database. When the query result is passed from the database to the client application, the ODBC connector converts the data from the native format into a format expected by the client application. As you can probably gather, the impact of this conversion step is decreased performance.
The benefit of using a "native driver" is that the application understands how to interface to the database in the most efficient and performance-oriented manner. SQL, data and the actual interfaces built inside the application have been specially designed for a specific database. This "native" interface is typically used when query or database performance is an issue.
We typically see performance become an issue with ODBC connectors if large data volumes are being loaded into a database in a record-at-a-time fashion. Most native drives support a means for bulk data loading that dramatically improves performance of data loading.
While there will always be a performance impact when using an ODBC driver (instead of a native driver) it's rare that an end user notices this degradation for traditional report processing.
Chuck Kelley's Answer: Native connections are those that are built specifically for the database you are using. For example, SQL*Net is the native connection for Oracle. There is also an ODBC connection for Oracle. I have found that native connections generally perform better than ODBC, but my friend Pat Phelan says that this is true only for Oracle.
Chuck Kelley is an internationally known expert in database and data warehousing technology. He has 30 years of experience in designing and implementing operational/production systems and data warehouses. Kelley has worked in some facet of the design and implementation phase of more than 50 data warehouses and data marts. He also teaches seminars, co-authored four books on data warehousing and has been published in many trade magazines on database technology, data warehousing and enterprise data strategies. He can be contacted at chuckkelley@usa.net.
Evan Levy is a partner and co-founder of Baseline Consulting Group, a multivendor systems integration and consulting firm. As the partner in charge of Baseline’s largest practice, Levy leads both executives and practitioners in delivering technology solutions that help business users make better decisions. He has led strategic technology implementations at commercial and public sector organizations and advises vendors on their product development and delivery strategies. Levy has been published in a wide array of industry magazines and has lectured on a range of technology delivery experiences at leading conferences and vendor events. He has been a featured speaker at the Marcus Evans Analytical CRM symposium, DCI’s Data Warehousing conference, the CRM Association, DAMA International, the AMA and the Data Warehousing Institute. His current work involves delivering and lecturing extensively on the topic of data integration. You can contact him at evanlevy@baseline-consulting.com.
Be the first to comment on this post using the section below.