Optimize fetching url aliases

So my first attempt to try and help not have to query the {url_alias} table for every single link (drupal_get_path_alias() in url()) during file generation didn't work (see #477072: Optimize the generate chunk SQL query). Trying to perform just a SELECT FROM {xmlsitemap} LEFT JOIN {url_alias} produced undesired results on MySQL vs PostgreSQL and duplicate results. I gave up on doing it with a join and the database. Currently the 6.x-2.x link output code is doing this:

function xmlsitemap_generate_chunk($handle, &$status, $chunk, $language = NULL) {
  $url_options = array('absolute' => TRUE, 'language' => $language);
  ...
  while ($link = db_fetch_array($query)) {
    ...
    $link_output = '<url><loc>' . url($link['url'], $url_options) . '</loc>';
    ...
  }
}

I was trying out the following code that fetches all the aliases for the specific language so they can be used to bypass the drupal_get_path_alias() calls inside url():

function xmlsitemap_get_alias($src, $language) {
  static $aliases;
  static $aliases_langauge;

  if (!isset($aliases) || $aliases_langauge != $language->language) {
    $aliases = array();
    $aliases_langauge = $language->language;
    $query = db_query("SELECT pid, src, dst FROM {url_alias} WHERE language IN ('%s', '') ORDER BY language, pid", $language->language);
    while ($alias = db_fetch_object($query)) {
      $aliases[$alias->src] = $alias->dst;
    }
  }

  return isset($aliases[$src]) ? $aliases[$src] : $src;
}

function xmlsitemap_generate_chunk($handle, &$status, $chunk, $language = NULL) {
  $url_options = array('absolute' => TRUE, 'language' => $language, 'alias' => TRUE);
  ...
  while ($link = db_fetch_array($query)) {
    ...
    $link_alias = xmlsitemap_get_alias($link['url'], $langauge);
    $link_output = '<url><loc>' . url($link_alias, $url_options) . '</loc>';
    ...
  }
}

Ok, so now the benchmarking results!

Old code with 6500 links, 2 sitemap languages, generated 50 times with ab -n 50:

Requests per second:    0.39 [#/sec] (mean)
Time per request:       2549.337 [ms] (mean)
Time per request:       2549.337 [ms] (mean, across all concurrent requests)
Transfer rate:          0.23 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       0
Processing:  2436 2549  41.8   2554    2620
Waiting:     2432 2544  41.7   2550    2614
Total:       2436 2549  41.8   2554    2620

And the new code with xmlsitemap_get_alias() and the same conditions:

Requests per second:    0.89 [#/sec] (mean)
Time per request:       1123.020 [ms] (mean)
Time per request:       1123.020 [ms] (mean, across all concurrent requests)
Transfer rate:          0.52 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       0
Processing:  1092 1123  26.5   1115    1212
Waiting:     1077 1118  27.3   1110    1212
Total:       1092 1123  26.5   1115    1212

With two larger queries instead of one large query with thousands of small queries, the result is a 56% improvement in generation time! I'm about to commit this to 6.x-2.x and wanted to share this with the other maintainers!