Permit to have faster results even with distant database:
solution would be to send X well established MySQL protocol without reading results, and read those X results after. Basically that permit to avoid a lot of “ping-pong” like COM_MULTI, without any of the problems of COM_MULTI.
“Prepare + execute” cannot work with server other than 10.2, because need the “-1” last insert id that has been done in COM_MULTI.
There is one problem with that approach: networking stack lock problem.
When client send enough data to full remote host (server) network stack, remote host will indicate to client host to wait before sending more data. If at the same time server send results big enough to full client host network stack, since client is waiting to send more data, system will be locked.
There is differents solutions to avoid this problem :
Read result asynchronously. That will even permit to parse results before the last one is send.
Server can send result only when batch is finished like COM_MULTI, but that mean for driver to indicate some “begin_batch” / “end_batch” somewhere.
configure socket option (SO_SNDBUF / SO_SNDBUF) with big enough size, and make sure to write less that SO_SNDBUF size in one go, before switching to reading
Use existing send protocols
Work with previous server versions (< 10.2)
There will be as many results than the number of send command, permitting to know the exact result of each command.
Some performances tests on remote server using async implementation (1000 inserts containing 100kb String each):
Existing = 1000 x (COM_STMT_EXECUTE + read response): 89 seconds
New = 1000 COM_STMT_EXECUTE + 1000 read response: 0.878 seconds