By Akzhan@drupal.ru on

Hello,

We all know that Path lookups in Drupal make a lot of micro-queries to database.

How to improve its speed?
One query to ask all paths on the page sometimes is 100x faster than a lot of queries to ask every one node.

My solution:

Firstly, we write text macro into content instead of calculation real value.
- drupal_lookup_path returns macro "%%%drupal-lookup-path-item-$id%%%" instead of real substitution.

Lastly, after rendering of all content simple regular expression parser extracts all macros and erplaces them with actual content (that known after one and only one database query).

⋅ Categories: Drupal 7.x

Comments

Akzhan@drupal.ru’s picture

Akzhan@drupal.ru commented

By the way, this path to know all required data by one query is very scalable.

And it can be used not only for Path queries.

Node concept can be rewritten to enable multiple nodees quering per once.

With best wishes,
Akzhan.

nevets’s picture

nevets commented

I am not sure what you suggest is practicle. First off post processing text to do macro replacement is likely to be more time expensive that all the database queries you are trying to replace. Since a path can contain placeholders (examples being node ids, user ids, taxonomy terms) it would need to account for that. In addition as the number of paths on a page increases the query gets more slower.

Akzhan@drupal.ru’s picture

Akzhan@drupal.ru commented

Typical Drupal page requires 30-50 path lookups per rendering. Some path lookups are duplicated.

Path lookup looks like:
SELECT dst FROM {url_alias} WHERE src = '%s'
As You can see, its very simple query. Why one query by IN range always faster than 3-50 queries by one value - answer is latency.

Even query by value calculated 30-50 faster than one query to ask all substitutions - one query eliminates latency of database queing 30-50x.

But in reality - one query more faster than we can predict because relational databases like queries over sets of the data.
And, of course, regular expressions are fast (simple pattern match over text in memory).

Optimizing of path lookups only can increase speed of rendering for 20%, not less.

With best wishes,
Akzhan.

seaji’s picture

seaji commented

Path lookups are NEVER dublicated.
Paths are staticaly cached in the function.

kayo’s picture

kayo commented

I think, that it's a good idea.
Also I suggest instead of replacing through by regular expression use ordinary str_replace() or strtr(), because it's faster. But in this case drupal_lookup_path() must collect real urls as array, and this urls don't need replaced by him aliases here.

For example (pseudo code):

Into the drupal_lookup_path():

$collected_urls[] = $original_path;

After page generating:

$substs = array();
for ($result = db_query('SELECT src, dst FROM url_alias WHERE src IN ("'.implode('","', $collected_urls).'")');
     $alias = db_fetch_object($result); $substs[$alias->src] = $alias->dst);

$page_content = strtr($page_content, $substs);

It's pseudo example.
I may try implement this technique in practice, and publish result here.