← Back to team overview

drizzle-discuss team mailing list archive

Re: [drizzle-discuss] Query Cache syntax


Opps! Yep that was my bad. Sending this back to the list.

On Aug 5, 2010, at 2:51 PM, Roland Bouman wrote:

> Hi!
> (not sure if you intended to PM me - replying to you, feel free to
> repost to the list)
> On Thu, Aug 5, 2010 at 8:53 PM, Tim Soderstrom
> <tim@xxxxxxxxxxxxxxxxxxxxx> wrote:
>> On Aug 5, 2010, at 1:18 PM, Roland Bouman wrote:
>>> Well, but that would require a statement to enable/disable it, right?
>>> That could be expensive, since it would need to be sent to the server
>>> as a separate statement no?
>> I was talking to a colleague about this over lunch. Perhaps this could be part of a protocol extension? If so, you could do much like prepared statements (use SQL or use functions for your connector library). That would avoid expensive round-trips.
> I can't judge this  - I am not familiar enough with the details of the
> protocol, and if this could be implemented without an extra roundtrip.
> If so, great!
>> That said, "BEGIN/COMMIT" require round-trips and people should definitely use those :) Are we really adding that much overhead by doing that? Could be session based so for those that always want caching, they could just set ENABLE CACHE at the beginning and be off and to the races.
> Well, there are things here that I think should be considered:
> 1) in a on-demand scenario, there may be a lot of enabling/disabling
> the cache in a single transaction. so while you're right that
> COMMIT/ROLLBACK already use a rountrip, I don't think adding more
> would be a good idea if it could be avoided.
> 2) the MySQL JDBC driver generates a lot of SET autocommit ON/OFF
> statements and as it happens this actually consumes a lot of time (see
> http://www.jroller.com/mmatthews/). While enabling a cache is very
> different from enabling autocommit, I'm just dragging this in here as
> an example how a seemingly harmless extra roundtrip can unexpectedly
> consume a lot of time.
> Of course, I certainly would never advise someone not to commit
> because of the extra rountrip :) In fact, for most scenarios I would
> recommend opening an explicit transaction, do as much work in it as is
> possible (from a functional/application point of view) and then COMMIT
> the whole set of changes. I wouldn't lose sleep over the extra
> rountrip for that COMMIT statement.
>> Tim
> -- 
> Roland Bouman
> blog: http://rpbouman.blogspot.com/
> twitter: @rolandbouman
> Author of "Pentaho Solutions: Business Intelligence and Data
> Warehousing with Pentaho and MySQL",
> http://tinyurl.com/lvxa88 (Wiley, ISBN: 978-0-470-48432-6)
> Author of "Pentaho Kettle Solutions: Building Open Source ETL
> Solutions with Pentaho Data Integration",
> http://tinyurl.com/33r7a8m (Wiley, ISBN: 978-0-470-63517-9)