Re: MS OLEDB for Oracle
Date: Mon, 30 Oct 2000 17:13:15 GMT
Message-ID: <8tka73$q1b$1_at_nnrp1.deja.com>
The lastest OLEDB drivers for Oracle from
Microsoft now support REF CURSORS.
http://www.microsoft.com/data/download_260rtm.htm
In article <J6eL5.69418
It's version 2.6 and you can find it at
$hD4.16085508_at_news1.rdc1.mi.home.com>,
"Graham" <_> wrote:
>
> 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
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Mon Oct 30 2000 - 18:13:15 CET