Re: Maximun number of unions in a single query

From: <BigBoote66_at_hotmail.com>
Date: 10 Jun 2005 12:21:48 -0700
Message-ID: <1118431308.888089.242000@z14g2000cwz.googlegroups.com>

> I would wrap both tests in stored procedures, then run them, so that we
> can compare only execution time, rather than parse+compile+execution.
>
> Makes sense?

Sure, although I think we're straying from the question asked by the original poster (not me - I was only providing one empirical case to demonstrate an answer to his question). It doesn't really apply in this case because the OP was wondering if using a single insert statement with unions would be faster than many single inserts because of reduced round-trip cost. However, in the interests of science, I tried it out.

Interestingly enough, however, following your suggestion did not exactly lead to the results I was expecting. Turning the "many" script into a single procedure call, like this:

create procedure smb_many as
begin

insert into smb values ( 1 , 'abc1',  10 , 15 );
insert into smb values ( 2 , 'abc2',  20 , 30 );
insert into smb values ( 3 , 'abc3',  30 , 45 );

[...]

insert into smb values ( 4998 , 'abc4998',  49980 , 74970 );
insert into smb values ( 4999 , 'abc4999',  49990 , 74985 );
insert into smb values ( 5000 , 'abc5000',  50000 , 75000 );

commit;
end;
/

did result in the process being signficantly faster, as you would expect (about 6 seconds now instead of 16, after repeated executions). However, the single-statement procedure:

create or replace procedure smb_few as begin insert into smb

select  1 , 'abc1',  10 , 15 from dual union all
select  2 , 'abc2',  20 , 30 from dual union all
select  3 , 'abc3',  30 , 45 from dual union all

[...]

select  4998 , 'abc4998',  49980 , 74970 from dual union all
select  4999 , 'abc4999',  49990 , 74985 from dual union all
select  5000 , 'abc5000',  50000 , 75000 from dual;

commit;
end;
/

didn't really result in any significant improvement - the procedural version finishes in 41 seconds compared to the original 44.

What's also odd is that the truncate table I used to clean things out in between runs was much slower for the "many" procedure than the "few" procedure (5 seconds vs. 0.5 seconds). Presumably this was due to the something about how the data was dropped into the extents.

Another interesting data point: Merely putting the many sql statements into an anonymous block actually makes things quite slow (27 seconds, compared to the 16 for straight statements), so apparently the cost of parsing the large block of many statements is worse than incurring the round-trip cost from the many individual statements. This is probably pointing to a memory issue on the SGA of our test server - given that the test files amount to about 300K of sql, it's possible we're overrunning some budget when it comes to parsing. It would be interesting to see the results on a different machine (yours, perhaps?)

-Steve Received on Fri Jun 10 2005 - 14:21:48 CDT