MOD faster or normal counter?

[pando]

Hi

Has anyone tested the perfomance between using MOD function (if MOD(i,5000) = 0 then commit etc) and a normal counter (if counter = 5000 then commit etc) to control commit frequency in a PL/SQL mass insert (insert many rows I mean)?

cheers

btw has anyone got anything to say about my cursor question posted yesterday



[Edited by pando on 08-22-2001 at 07:53 AM]

[jmodic]

There was a discussion about this some time ago in this forum http://www.dbasupport.com/forums/sho...threadid=8447.

I was claiming that using MOD(SQL%ROWCOUNT) should be faster then then incrementing a counter and resetting it after every commit.

In the meantime I've found Tom Kyte's article (either in Oramag or on asktom.oracle.com) that discusses the same topic. He (Tom) proved with PL/SQL profiler that using "IF x = 5000 THEN ...." is faster than using "IF MOD(x,5000) THEN ...". But Tom's test case was a slightly different than the one we discussed in the above thread - it used no cursor so in both cases the counter had to be incremented. And besides, his profiler timings are somewhat skewed. In any case the time difference should be neglectable and I still think the MOD() aproach is less error-prone.

[pando]

Yea I remembered there was this thread but was looking for it for like half an hour but couldnt find it!

Thanx for the link