Re: Oracle v7, ODBC, and VB

In article <2bocj3$fij_at_panix.com>, rryan_at_panix.com (Rob Ryan) writes...
>
>I agree, app level comparisons are of limited value when debating the
>efficiency of ODBC. Unfortunately I don't have any single tool which can
>use two (ODBC and some other) interfaces.

I can see that you've put a fair bit of thought into this. Still, the only way to do a valid comparison is to write a C program that does exactly the same thing using an proprietary client API and the ODBC driver for the corresponding database.

>
>But this is why I threw VB into the equation. It is widely recognized
>that Access throws in a lot of overhead, but with my VB test, I know
>precisely what ODBC calls were made. VB is no speed demon, admittedly,
>but I did direct ODBC calls, no extra calls to determine return types, no
>local processing, no yielding to Windows while returning the data, just a
>SELECT statement plain and simple, fetching rows one by one. Admittedly,
>I only used ODBC Level 1 (which Access uses too), rather than Level 2
>which can return multiple rows and thus might yield decent performance
>improvments. But still, the PowerBuilder app was 3-4 times faster than
>the VB program with direct ODBC calls.
>

From what you said, I gather that you don't know what client API calls PowerBuilder is doing under the covers. This makes it hard to compare apples to apples. Also, are the queries exactly the same? I thought that Access usually issues one query per row using index values. I would think that this would be much slower that fetching rows from a single query.

Regarding block fetches, it is my experience that protocol message round trips are one of the single biggest factors in protocol performance. If PowerBuilder is using block fetches to retrieve the rows, then that could account for a significant performance difference.

>I further question ODBC's performance as the results of tests with SQL
>Server, too. Queries done via SAF or a native library return much quickly
>(no precise numbers, but easily 3 times as quickly) than ODBC calls.

Microsoft has done performance tests of ODBC vs. dblib to access SQL Server. The tests showed a minimal performance penalty for ODBC. Now, I don't remember too many details of how the tests were carried out, but I do know that it was a C program that was used for the measurments. I cannot say for sure that this test was valid, but it is certainly a data point.

>
>Perhaps a wider acceptance/implementation of ODBC Level 2 and the maturing
>of the products out there will lessen this disparity. But at this point,
>I really think that performance suffers in any ODBC implementations. I
>still use ODBC in many cases (quick prototyping in Access, portability,
>etc.), and I look forward to ODBC support in Excel 5.0. But I have no
>illusions about its performance. It is slow.
>

I'm not an ODBC or a Microsoft fanatic, but I just don't think it is fair to generalize ODBC's performance as "slow". There are many different drivers out there. They are written by different people with different abilities. Most of them are layered on top of existing client APIs (each with different performance characteristics), primarily because this was the fastest way for many suppliers to bring ODBC drivers to market. This will change over time. I'd be willing to bet that a cleverly-written ODBC driver using a well-designed client-server protocol would be as fast or faster than the corresponding proprietary API. However, this good performance could easily nullified by less-than-optimal use of the driver by an application.

I don't believe that ODBC is generally slow. I believe that it is possible that some drivers could be slow. The issue of ODBC performance pales in comparison to the issue of the N-by-M testing problem for ODBC applications and drivers.

Scott Received on Tue Nov 09 1993 - 12:22:37 CET