Re: Maximun number of unions in a single query

Look, man,

We shall not mention he who should not be mentioned.

This wasn't an academic exercise to discount and discredit using multiple unions from dual to facilitate multi-row inserts. This was merely a question posed to determine the upper limit of X (see previous posts).

Granted, you may be correct about using a bound array and that insert into Y is still called X times (I haven't tried it out either because I prefer sleeping), but that is no reason to suggest he who should not be mentioned. Furthermore, ColdFusion, being as lobotomized as it is when it comes to Oracle, does not allow bound arrays to be passed, so you still incur the network overhead of passing all those select statements to the DB server. 10 users doing inserts simultaneously is ok on bandwidth. 100,000, simultaneously, may be pushing it a little for a 100BT.

 If you want to mix up a test case, be my guest. We would all like to see your results.

-- 

Andreas Sheriff (Oracle Certified DBA and PL/SQL Developer)
----
"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding, if you don't eat your meat?"

If this is the first email to me,
please place NOSPAM somewhere
in the subject.

"Billy" <vslabs_at_onwe.co.za> wrote in message
news:1118299161.221485.216970_at_g49g2000cwa.googlegroups.com...


> Andreas Sheriff wrote:

> > As for mendicant, yes I did mean the lexical definition.  To call

someone an


> > a$$h0le does not abide by the geek's book of insults.

>

> Yeah, but that book does not count on Usenet as you should well know.

> Or have you forgotten about Godwin's Law? Sheez!

>

> > I'm working with an application that does multiple inserts into a table.

> <snipped>

> > Here's what I want to accomplish:

> >

> > insert into mytable(col1, col2, col3, col4)

> > select  :1,  :2,   :3,    :4 from dual union all

> > select  :5,  :6,    :7,   :8 from dual union all

> > select  :9, :10, :11, :12 from dual union all

> > select :13, :14,:15, :16 from dual union all

> > etc...

> >

> > It seems to me that this method of inserting data into a table using one

> > insert statement

> > would be a hell-of-a-lot better than doing multiple insert statement

between


> > <cfquery></cfquery>.

>

> Nope. I cannot see that as being any faster. How much parsing is that,

> compare to a single hard parse of:

> insert into mytable( :col1, :col2, :col3, :col4)

>

> Followed by a soft parse (assuming no statement handle re-use on the

> client side) for every following INSERT statement.

>

> Better yet would be a single parse and passing the bind variable data

> as an array (for bulk binding on the SQL engine side).

>

> Also not forgetting the execution path. The INSERT is still

> called/execxuted x times irrespective. Only with the UNIONs, there is

> also a SELECT that is called x times. Why the SELECT overhead when you

> can simply execute the INSERT x times?

>

> Please say you agree or you will force me to spend time setting upo a

> test case to show you are wrong.

>

> > Now you know the reasoning behind my question and now you *all* can stop

the


> > flaming, or have I poured kerosene on the whole situation?

>

> Flaming!? This is not even the size of small birthday candle flame.

>

> --

> Billy

>