Re: Oracle v7, ODBC, and VB

In <2bo711$kt4_at_jac.zko.dec.com> newman_at_broke.enet.dec.com (Scott Newman) writes:

>In article <2bmmma$ne6_at_panix.com>, rryan_at_panix.com (Rob Ryan) writes...
>
>>Tell us your secret, please. I find that retrieving large amounts of
>>information (a very simple query, no joins, two fields, 30k records)
>>is about 6 times slower using Access/ODBC than Powerbuilder v2.0. VB
>>(using direct ODBC calls, not Access's engine) was 10-15% faster than
>>Access, but still notably slower than Powerbuilder.
>
>The important thing to remember is that there are many pieces involved
>in this sort of configuration. It is entirely possible that all of the
>performance difference that you are seeing can be explained by the
>differences between Access, PowerBuilder and VB. Even though your query
>was very simple, there is no guarantee that it is being handled the same
>was by the APs you are comparing. In fact, there are probably significant
>differences.
>
>I am not disputing that there could be a performance difference between
>ODBC access to a back-end db vs. using the back-end's native client
>interface. In fact, there must be at least a little overhead with ODBC
>because most drivers are currently layered on top of existing client
>interfaces. The only way to know for sure is to compare at the level of
>the ODBC API and native client API. Comparing at the AP level introduces
>a whole lot of other variables that can lead to erroneous conclusions.

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.

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.

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.

And using Rochester Software's Showcase ODBC, calls to an AS/400 via ODBC are at least twice as slow as performing the query through the SQL interface on the AS/400 and transferring the resulting file to the PCs local hard disk via a file transfer protocol. (And we've optimized the AS/400's ODBC interface significantly.) So the time to return results via ODBC is much more than just the sum of the time to perform the query and the transmission time.

Every test that I've done suggests that ODBC's interoperability and DBMS independence comes at a significant performance cost. I have yet to come across any benchmarks to the contrary.

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.

• Rob