Re: MS OLEDB for Oracle

From: <k3ith_at_my-deja.com>
Date: Mon, 30 Oct 2000 17:13:15 GMT
Message-ID: <8tka73$q1b$1@nnrp1.deja.com>

The lastest OLEDB drivers for Oracle from Microsoft now support REF CURSORS.
It's version 2.6 and you can find it at

http://www.microsoft.com/data/download_260rtm.htm

In article <J6eL5.69418
$hD4.16085508_at_news1.rdc1.mi.home.com>,
  "Graham" <_> wrote:
> 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
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 30 2000 - 11:13:15 CST