[development] Advanced DB features
larry at garfieldtech.com
Wed Apr 30 18:18:19 UTC 2008
On Wed, 30 Apr 2008 10:25:13 -0700, "Joshua D. Drake" <jd at commandprompt.com> wrote:
> Case in point, Drupal can easily execute 200 queries to draw a page.
> Why do we do that? It's dumb and inefficient. You lose 30ms for every
> single query just in TCP negotiation. You push that to 5 stored
> procedures that draw blocks, and you have shaved ~ 5 seconds off your
> load in just negotiation. Add a cached procedure and you could shave
> another ~ 5 seconds off.
My queries, according to dev module, are quite rarely more than 5 ms. I don't know where you're getting that 30 ms number from. And, AFAIK, an open SQL connection is a persistent TCP connection, therefore it only has to negotiate the connection once. MySQL is also really fast at that setup compared to most databases.
I agree that reducing our query count is a good thing, but not because of a 30 ms overhead per query.
>> Even if I doubt support for stored procedures is going
>> to find its way into Drupal since the core has to remain DB
> I doubt that is actually an issue. I understand that we have to dumb
> things down for the dolphin but that doesn't mean the api can't be
> smart about it.
> If Catalyst can do it...
>>and I think providing an abstraction layer to sp is hard some
>> oversight may help to see which are the features that could be more
>> relevant to support... I'm at least thinking to transactions.
> Ahh but it can. Remember that even the dolphin has stored procedures.
> Joshua D. Drake
MySQL 5 is slated as a requirement for Drupal 7, so stored procedures will be available... IF we can confirm that we have the necessary permissions to create them on a typical shared host. SQL Views I don't think we can use due to needing additional server-level permissions to create them in the first place, but I haven't investigated it in detail. Stored procedures may or may not have a similar issue; I really don't know yet.
At the moment I am not dealing with stored procedures for the D7 database API. It's big enough as is. :-) Once it lands, we can think about it then.
The main reason we have so many queries, though, is flexibility. Right now Drupal has "dumb modularity"; we call hooks and let them do "whatever", which usually involves queries. To further optimize our SQL would require either smarter query preparation across hooks or some sort of super-dynamic prepared statement that gets setup by hooks instead. Neither is a trivial task.
For example, we call path lookup once for each l() function. That can easily eat up 100 queries right there. If we knew which ones we'd need, a single query that builds a targeted lookup table would be enormously faster... if we knew which ones we'd need in advance, which we don't. chx and I were discussing a possible learning/caching algorithm back in February, but I don't know that either of us will have time to do more than discuss due to being busy with other code (testing and databases, respectively, plus the registry).
More information about the development