Larry wrote:
> Holger Baer wrote:
>> You suffer the same problem that many developers suffer. You start out
>> with
>> a small benchmark, and instead of trying to learn something out of it
>> (namely how to use Oracle correctly in the first place) you're
>> prepared to blame anybody else.
> I don't necessarily agree that I was "blaming" anybody.
I was possibly exaggerating to make my point. Sorry.
> Was I puzzled that the per second insert rate was at 40. Yes.
> Was I disappointed that our companys contractors simply said that
> "dotnet" was the problem? Yes.
I overlooked that they were contractors. My bad.
> Anyway, my not knowing that Dotnet was autocommitting was the core
> problem. By changing the code to use a transaction, my insert rate went
> from 40 per second to 1250 per second. That's still not quite the 7000
> per second I get in MySQL using Dotnet, or the 10,000 per second I get
> in Oracle when using SQLplus.
> But it's a start. And a big one at that.
> I thank you...and everyone in this forum that replied for your gracious
> help!
> Larry
Glad I could help. I think much of the difference between dotnet and using
native PL/SQL might stem from the necessary roundtrips through the network
stack and the accompanied technology stacks. Secondly, although you're sending
always the same statement, Oracle will do a soft parse. The PL/SQL version
does only one parse and then reuses the cursor. Parsing is very expensive
(another lesson to be learned) and should be avoided whenever possible.
Oh, and just to give you an idea of what Oracle is capable, try running the
same benchmark(with transactions) against Oracle and MySQL, but this time make
it a multi-user benchmark by running it ten times in parallel. And compare
how MySQL supports transactions. (Last time I looked, it didn't, but that depends
of the version you're using and you have to use InnoDB etc.).
Cheers,
Holger
