Re: MS OLEDB for Oracle

Matt,

I didn't go into the details because I did the necessary testing to make sure these weren't problems, but how are you suppost to know that right?

In total I have 7 functions and 16 packaged procedures, all of which are experiencing this problem. These are all stored procedures that I migrated from SQL Server 7 to Oracle 8 and executing through VB. Of course, it wasn't a straight port and many changes had to be made. On many procedures I am using REF CURSOR (my reason for using MS OLE DB in the first place) so they were converted over to packaged procedures. I haven't done my tuning as of yet because I want to get this problem solved first. I also do not have a central query that all functions and procedures call nor do I have recurrsive calling. However, I did test several queries remotly against Oracle to make sure tuning wasn' the problem I was seeing. In fact they run faster than in SQL Server because our hardware is much better on our Oracle server.

On a side note, if you know a way to return multiple recordsets from a single packaged procedure using REF CURSORs, I'd love to know how. This is a limitation I do not like at this time since I am forced to create an individual procedure for each recordset I'm going to return from Oracle.

Thanks for you help,

Graham Sutcliffe

Matt Houseman <mhousema_at_ix.netcom.com> wrote in message news:8tiat5$72e$1_at_slb6.atl.mindspring.net...
> 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 Mon Oct 30 2000 - 06:48:41 CST