Re: MS OLEDB for Oracle

Graham,

Several questions:
- are you compiling/dispatching SQL or are you invoking stored procedures (recommended)
- did you simply port your tables/SQL directly from SQLServer to Oracle or did you spend a few minutes tuning your table structure and SQL using EXPLAIN PLAN and other tuning tools (recommended) - have you isolated the most non-performant query and cut-and-pasted it into SQL*Plus in an attempt to isolate a performance problem with the query itself versus a performance problem with MSDAORA - if you're using stored procedures, you could use REF CURSOR's as the return then change over to the ORAOLEDB.Oracle provider in an attempt to solve the performance problem.

Hope this helps,
Matt Houseman

"Graham" <Graham_at_home.com> wrote in message news:XLXK5.68428$hD4.15636656_at_news1.rdc1.mi.home.com...
> I've tried to get this answered on the OLEDB newsgroup, but it has very
> little traffic. Wanted to know if anyone else ran into this while doing
> VB/Oracle development.
>
> Specifications:
> MS OLEDB for Oracle (MDAC 2.5) (local)
> ASP (local)
> Oracle 7.3.4 (remote)
> VB6 SP3 using ADO
>
> I have written an ActiveX DLL using VB6SP3 that goes against SQL Server 7.
> Everthing works fine using the SQLOLEDB provider. I currently have a need
> to change this over to Oracle using MSDAORA provider which is currently
> complete, however I am running into performance issues. It takes well
 over
> a minute longer to complete requests against a remote Oracle database than
> it did against a remote SQL Server database. The ActiveX DLL is loaded on
 a
> different machine from the database and connection pooling is being used
 (I
> made necessary changes to the registry and rebooted, however I don't know
> how to prove this). The delays seem to come about when setting
> ActiveConnection property and to a smaller extent the Execute method
> (although I could attribute that just to the time it takes to execute).
>
> The question is why is there a delay and what can I do to prevent it? The
> final design will have both ActiveX DLL and Oracle DB on the same server,
> but I am not sure if this is going to solve the delays when the permanent
> server is setup. Plus I'm currently testing, and it's obviously a very
 slow
> process. Would it help to access MSDAORA on the remote server containing
> Oracle? If so, how is that setup? Help on this would be greatly
> appreciated, I've racked my brain long enough.
>
> Thanks,
>
> Graham Sutcliffe
>
>
>
>
>
>
Received on Sun Oct 29 2000 - 17:23:46 CST