Re: Maximun number of unions in a single query

In the spirit of adding some useful information to this thread for posterity:

I ran a quick test using your example (many simple inserts vs. one insert with many union alls) against our test server, using 5000 inserts. The single statement, union all version took about 3 times longer than the 5000 individual inserts (44 seconds vs. 16 seconds) . What did this tell us?

1. The upper limit on union all's is at least 5000.
2. The single statement version of the insert is much slower than individual statements.

Now, this was executing the statements from sqlplus running on the same box as the server (9.2.0.4.0, on Solaris, by the way). However, running the statements from a pc on our network (100BT as well), the result is 2 times slower for the "union all" version (44 seconds vs. 24 seconds). So it would appear that the round trip time over the 100BT time does add some overhead (about 8 seconds for 5000 statements), but not enough to compensate for the overall pain that the server is suffering for processing the mongo-statement.

This was also not using bind variables, so you could expect the many-insert solution be even more superior in your case.

Another thing to consider - in the case of many-insert statement, the major burden of the processing was being carried by the client & the network, whereas the single statement version is putting most of the burden on your server, which makes it inherently less scalable.

Of course, you could have done these tests yourself - I used Excel to quickly whip up my 5000 test statements in about 2 minutes.

-Steve Received on Thu Jun 09 2005 - 11:36:05 CDT