DOCUMENT:Q180015  28-JAN-1998  [vbwin]
TITLE   :PRB: Stored Procedures Slower with MS Oracle ODBC Driver 2.0
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINNT:2.0;
OPER/SYS:winnt
KEYWORDS:odbcOracle mdacMisc

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Enterprise Edition for Windows, version 5.0
 - Microsoft ODBC for Oracle, version 2.0 (Build 2.73.7269)
---------------------------------------------------------------------

SYMPTOMS
========

When you call an Oracle stored procedure through RDO or through ADO using
Command.Refresh, the MS Oracle ODBC Driver version 2.0 is slower than
version 1.0.

CAUSE
=====

The underlying ODBC API call (SQLProcedureColumns) was changed in version
2.0 of the MS Oracle ODBC Driver to accommodate Oracle package support. By
making this change, the SQL for the API call increased ten-fold. The larger
SQL statement results in the slower execution of Oracle stored procedures.

RESOLUTION
==========

When using RDO there is no workaround. SQLProcedureColumns is always
called. With ADO, if all of the Command attributes are set, the parameters
collection is defined and Command.Refresh is not used, then
SQLProcedureColumns is not called. For an example of how to use ADO so that
SQLProcedureColumns is not called, please see the following article in the
Microsoft Knowledge Base:

   ARTICLE-ID: Q176936
   TITLE     : INFO: Visual Basic 5.0 Accessing an Oracle Database Using
               ADO

STATUS
======

Microsoft is researching this problem and will post new information here in
the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION
================

Following is the SQL statement that SQLProcedureColumns creates on Oracle
(captured through Oracle SQL trace):

   select null,owner,decode(package_name,NULL,object_name,
   package_name||'.'||object_name),decode(position,0,
   'RETURN_VALUE',nvl(argument_name,chr(0))),
   decode(data_type,'REFCURSOR',3,decode(in_out,
   'IN',1,'IN/OUT',2,'OUT',decode(argument_name,null,5,4)
   ,0)), decode(data_type, 'CHAR', 1, 'DATE', 11, 'FLOAT'
   , 8, 'LONG', -1, 'LONG RAW', -4, 'NUMBER', 3, 'RAW', -3,
   'VARCHAR2', 12, 0), data_type, decode(data_type, 'CHAR',
   decode(data_length, null, 255, data_length), 'DATE', 19,
   'FLOAT', 15, 'LONG', 2147483647, 'LONG RAW', 2147483647,
   'NUMBER', decode(data_precision, null, 38, data_precision),
   'RAW', decode(data_length, null, 255, data_length),
   'VARCHAR2', decode(data_length, null, 2000, data_length),
   data_length), decode(data_type, 'CHAR', decode(data_length,
   null, 255, data_length), 'DATE', 16, 'FLOAT', 8, 'LONG',
   2147483647, 'LONG RAW', 2147483647, 'NUMBER', decode(
   data_precision, null, decode(data_scale, 0, 40, 40), 40),
   'RAW', decode(data_length, null, 255, data_length),
   'VARCHAR2', decode(data_length, null, 2000, data_length),
   data_length), decode(data_type, 'DATE', 0, 'FLOAT', null,
   'NUMBER', 0, data_scale), decode(data_type, 'NUMBER', 10,
   'FLOAT', 10, 'DATE', 10, null), 2, null, overload,  sequence
   from all_arguments where data_level = 0 and
   decode(package_name,NULL,object_name,package_name||'.'||
   object_name) like 'PACKPERSON.ONEPERSON' escape '\'
   order by 2,3,14,15

If you use ADO (as demonstrated in KB article Q176936) instead of RDO, the
above statement is never issued to Oracle. Because of this, ADO can execute
an Oracle stored procedure much faster than RDO.

(c) Microsoft Corporation 1998, All Rights Reserved.
Contributions by Sam Carpenter, Microsoft Corporation

Additional query words: MS Oracle ODBC Driver rdo ado stored procedure
======================================================================
Keywords          : odbcOracle mdacMisc
Technology        : odbc
Version           : WINNT:2.0;
Platform          : winnt
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1998.