[development] Advanced DB features

[Larry Garfield]

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
>> agnostic 
> 
> 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).

--Larry Garfield