USE Bind variables not strings. You are causing a lot of parsing which will
slow things down a lot.
Jim
"dean" <deanbrown3d_at_yahoo.com> wrote in message
news:1149866223.764210.63910_at_g10g2000cwb.googlegroups.com...
> Ok thanks for the help, I wasn't sure they were the same thing.
>
> Tha data comes from an in-memory container after a calculation. The
> data (500-2000 records) right now is written to a global temp table and
> then merged into the main data table.
>
> The problem is twofold - I have to create a huge string of UNIONs so
> that I can pass the calculated data to oracle to insert. And I have to
> do this in several steps of the string size approaches around 64K,
> which is the biggest query length I can pass using ADO.
>
> It seems that Loader is the right choice.
>
> Thanks
>
> Dean
>
>
> Mark D Powell wrote:
> > dean wrote:
> > > Or are they two separate processes?
> > >
> > > Here's my problem, any help would be appreciated. I need to be able to
> > > post around 500 to 2000 records into an output table fast, once the
> > > user scrolls off a master record (or, in other words, it has to be
> > > responsive). I have tried all kinds of tricks, such as the MERGE
> > > statement, multi-row inserts using select bla from bla union all
select
> > > bla2 from bla union all ...., etc.
> > >
> > > What are some of the alterntive methods I could try here? I am using
> > > Oracle 9.2i and 10g on a single processor windows environment. The
> > > output table has generally 2 million records with around 20 field.
> > >
> > > Thanks for any help,
> > >
> > > Dean
> >
> > Dean, depending on what tool you are using on the front-end the bulk
> > load feature of pl/sql might be an option. Basically you have to load
> > the data into a pl/sql table (array) and then Oracle uses array inserts
> > to load the data into the table. See the PL/SQL manual.
> >
> > The insert/select statement might be an option depending on where your
> > data source is.
> >
> > More detail of where the data comes from and where it has to go would
> > probably allow someone to suggest viable approaches to the problem.
> >
> > HTH -- Mark D Powell --
>
Received on Sat Jun 10 2006 - 00:18:23 CDT
