[drupal-devel] [feature] Block query consolidation

[Dries]

Issue status update for 
http://drupal.org/node/30801
Post a follow up: 
http://drupal.org/project/comments/add/30801

 Project:      Drupal
 Version:      cvs
 Component:    block.module
 Category:     feature requests
 Priority:     normal
 Assigned to:  Anonymous
 Reported by:  Allie Micka
 Updated by:   Dries
 Status:       patch (ready to be committed)

This patch reduces the SQL overhead but adds some CPU-overhead and
memory-overhead.  Hence, it's better to benchmark the number of
requests/second (for example using ab or ab2) ...




Dries



Previous comments:
------------------------------------------------------------------------

Fri, 09 Sep 2005 20:12:03 +0000 : Allie Micka

Attachment: http://drupal.org/files/issues/block_query.patch (1.25 KB)

The blocks module is running a separate query for each region, e.g.:


   SELECT * FROM blocks WHERE theme = 'bluemarine' AND status = 1 AND
region='left' ORDER BY weight, module
   SELECT * FROM blocks WHERE theme = 'bluemarine' AND status = 1 AND
region='right' ORDER BY weight, module
   SELECT * FROM blocks WHERE theme = 'bluemarine' AND status = 1 AND
region='header' ORDER BY weight, module
   SELECT * FROM blocks WHERE theme = 'bluemarine' AND status = 1 AND
region='footer' ORDER BY weight, module


Ultimately, we're going to need all of these so let's save a few
queries.  I have changed the query to:


   SELECT * FROM blocks WHERE theme = 'bluemarine' AND status = 1 ORDER
BY region, weight, module


And set all results to the static $blocks array on the first pass.




------------------------------------------------------------------------

Sun, 11 Sep 2005 23:50:53 +0000 : m3avrck

Attachment: http://drupal.org/files/issues/block.module_5.patch (1.27 KB)

Awesome patch! Noticed the version was set wrong so changed this to CVS.


Also, rerolled the patch, there was an extraneous $region in there no
longer needed. Reducing 4 queries is definetly a great start in
optimizing Drupal :)


Take this patch along with this one (might need to be updated now with
this query rewrite I'm thinking): http://drupal.org/node/27157 ... and
we'll have Drupal's loading of blocks pretty much maxed out with
optimizations, then on to the slooow alias ones... ;)




------------------------------------------------------------------------

Mon, 12 Sep 2005 14:24:56 +0000 : Souvent22

Gotta love when something is just logical. Makes sense, and the patch
worked. I'd say this is ready. Get these blocks sped up a bit. This
coupled with 'block primary key' patch should make the blocks solid.




------------------------------------------------------------------------

Mon, 12 Sep 2005 14:36:00 +0000 : Dries

Has this been benchmarked?  I would expect this to be faster, but it's
good practice nonetheless.  Feel free to throw that other block query
patch into the mix.




------------------------------------------------------------------------

Mon, 12 Sep 2005 14:49:15 +0000 : m3avrck

Dries, I ran some simple benchmarks with the devel.module. Queries with
a default install were reduced from 125 to 121. Query times averaged
around 50ms and this was reduced to roughly 47-48ms. It's not a *huge*
savings, but on a a dev machine, with only one user, this is still good
nonetheless. I imagine on a hard hit Drupal site this would start to
scale up nicely with time/load savings. I'll reroll a patch that
combines the two in a few.