[development] Caching, caching, caching...

[Moshe Weitzman]

Dries Buytaert wrote:
> 
> On 21 Jul 2006, at 15:15, Moshe Weitzman wrote:
>> i can't think of any reasonable assumptions. to me, the waste is not 
>> that we have to perform so many queries, but rather that we do so on 
>> every page view  even though the data rarely changes. to address that, 
>> one should use mysql query cache or even memcached.
> 
> A query cache only eliminates part of the overhead.  Most of the 
> overhead comes from Drupal's database abstraction layer, not from 
> execution the actual SQL query:
> 
>   http://buytaert.net/drupal-database-interaction
> 
> We gain most if we eliminate calls to db_query().  We use a MySQL query 
> cache on drupal.org, and the one query that retrieves the URL alias is 
> still to most expensive query (accumulated cost).

good point. i guess you have looked at opportunities for improvement in 
db_query() and friends? I suspect that the slowdown is due to regular 
expressions. I'm not fluent enough to evaluate which are slow, but here is 
the list of DB functions that contain a regex.

db_query
db_query_range
db_rewrite_sql
db_escape_table
db_query_temporary - only used for search i think

Also, we call mysqlescapestring() an awful lot, to sanitize data in the 
query. We do so even when the data is known safe. I wonder if this is 
expensive or not?

@Dries - did you have the devel.module query log enabled when you did your 
analysis? That log calls debug_backtrace() for every query and could 
conceivably slow things down.

So, someone please look at these regex and see if they are expensive, and if 
we can improve them. Or identify some other cause of delay in our DB 
abstraction. The timer API might be useful here.

-moshe