Hi!
On Oct 13, 2009, at 9:41 AM, Jay Pipes wrote:
To me, INSERT/UPDATE is where they pay off mainly because of the
lack of needing to escape large objects.
Drizzle doesn't support multiple statements in a single
communication packet, so does the above still make sense?
We can pack statements into a single packet, so it can. With a bulk
insert... big gains.
But in general part of the gain is in not having to scan the varchar/
blob for escape characters. Also, the client doesn't have to do the
"escape this" which is pretty costly when it comes to memory.
A 2 meg blob requires on the server size 2x in order to unescape it.
Moving to prep statements, and encouraging them on the client, is gain
in that on the client side, even if we do the escape, we can do it
while we stream the data to socket.
So big wins all around.
As far as selects go, I certainly have used them there. A common
from page query for slashdot for example had just two parameters
and was called constantly for the page. In that instance a prep
stmt was a big win (same for the SELECT *FROM USER WHERE user_id=?).
? This doesn't make sense to me. In what way does "SELECT * FROM
USER WHERE user_id = ?" benefit from preparation if the connector
does not keep the statement around? In other words, if the
connection is closed upon end of the web request, what point does
this have?
Its minimal but it is the same as the above. "user_id" would be sent
directly to the server and on that side we wouldn't have to worry
about escape. In the longer term there is probably a bigger game when
the server can send a serialized version of the parse tree back to the
client. The client could then just send it to the server each time,
whether a connection is kept or not, saving around 4-12% of the
execution time of the average query.
BTW once we can use this form of serialization for INSERT/UPDATE,
replication will be able to leverage the same cost savings.
To me what prepared statements buys us is more flexibility for the
future in regards to what we can do under the hood without disturbing
current applications. It is also, frankly, better security as well. If
we remove the requirement for users to "escape", we also remove a lot
of the cheap "Bobby Tables" sorts of hacks.
Cheers,
-Brian
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
