Even more on Stored Procedure performance

Well now, there has been some commenting and even a follow up blogpost to me previous blog on MySQL Stored Procedure Performance.

One of the issues, and I knew about that one, was that when I compared 100000 client INSERTs with 100000 INSERTs done from a Stored Procedure, the overhead of the former would largely be the Client / Server communication latency. This was noted by Anthony T Curtis in his blog Yet more on Stored Procedure performance.

Me not being one who will put a stop to a debate, or whatever it is, we are mostly fact-fining I think, I have done YET some more tests. What Anthony was doing was to limit the overhead of the network latency by using multi-statement SQL for the INSERT, hence limiting the network roundtrips. So I decided to better that and remove the network altogether, but recompiling, my testprogram spperf.c as a libmysqld program. I will not show the code here, as it is basically the same as what you have already seen. The results were somewhat interesting, I think, at least of you are an acknowledged database geek.

So, here we go, using the same procedures and data and tables as before, but the program is now using libmysqld (the embedded MySQL Server):
First, my straightforward INSERTs that took some 7 s real time before:
-bash-3.00$ time ./spperf_emb 100000 "INSERT INTO foo VALUES(57, 'Some data')"

real 0m3.498s
user 0m2.288s
sys 0m1.165s

As we can see, this was about TWICE as fast! Yo. But libmysqld really IS fast here. What about using my procedure:
-bash-3.00$ time ./spperf_emb 1 "CALL perf(100000)"

real 0m3.361s
user 0m1.654s
sys 0m1.706s
Still fast, not as much faster than the straght INSERTs when I used the networked version of the program, but still faster! And faster than any results performed by Anthony (which is what I hoped to achieve anyway :-). Testing different combinations of things, running the loop inside the procedure and out of it, went slightly faster and faster until I had about 1000 call to the procedure, with the procedure doing 100 loops. After that point, performance was about what I achived above.

/Karlsson