I am Anders Karlsson, and I have been working in the RDBMS industry for many, possibly too many, years. In this blog, I write about my thoughts on RDBMS technology, happenings and industry, and also on any wild ideas around that I might think up after a few beers.

Thursday, February 19, 2009

Even more on 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.498suser 0m2.288ssys 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.361suser 0m1.654ssys 0m1.706sStill 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.