PRB: Stored Procedures Slower with MS Oracle ODBC Driver 2.0 (180015)
The information in this article applies to:
- Microsoft Visual Basic Enterprise Edition for Windows 5.0
- Microsoft ODBC for Oracle version 2.0 Build 2.73.7269
- Microsoft ODBC for Oracle version 2.0 Build 2.73.7283.01
- Microsoft ODBC for Oracle version 2.0 Build 2.73.7283.03
- Microsoft ODBC for Oracle version 2.5 Build 2.573.2927
This article was previously published under Q180015 SYMPTOMS
When you call an Oracle stored procedure through Remote Data Objects (RDO)
or through ActiveX Data Objects (ADO) using Command.Refresh, the Microsoft
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: 176936
INFO: Visual Basic 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 176936) 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.
| Modification Type: | Minor | Last Reviewed: | 2/14/2005 |
|---|
| Keywords: | kbDatabase kbDriver kbOracle kbprb kbRDO KB180015 |
|---|
|