Re: Is an Array-Insert the same as using SQL Loader?

Jim Kennedy wrote:
> 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 --
> >

Jim has point, if you can write the SQL statement once using bind variables that is much more efficient for concurrentcy than building a unique SQL statement every time.

Why do you need a temp table? What functionality does it provide? Which step in your process is where the most time is being spent?

Hopefully your answering these questions will help you find the best solution.

HTH -- Mark D Powell -- Received on Sat Jun 10 2006 - 09:12:57 CDT