如何在单个查询中获取Silverstripe中的200多个页面？

Question

One of our Silverstripe sites is on shared hosting and having major performance issues. The issues seem to be caused by the shared SQL server throttling the number of queries that can be made.

The pages that are running the slowest get 200+ hundred pages to place on a Google Map:

$DirectoryItems = DirectoryItem::get()->where("\"Latitude\" IS NOT NULL AND \"Longitude\" IS NOT NULL ")->sort('Title ASC');
$MapItems = new ArrayList();
foreach ($DirectoryItems as $DirectoryItem) {
  $MapItems->push(new ArrayData(array(
      "Latitude" => $DirectoryItem->Latitude,
      "Longitude" => $DirectoryItem->Longitude,
      "MapMarkerURL" => $DirectoryItem->MapMarkerURL,
      "Title" => addslashes($DirectoryItem->Title),
      "Link" => $DirectoryItem->Link()
  )));
}

Each of the 200+ MapItems generate it's own SQL Query which is overloading the shared SQL server.

I started off trying to get the same information with a single query:

$DirectoryItems = DB::query('SELECT `DirectoryItem`.`Latitude`, `DirectoryItem`.`Longitude`, `DirectoryItem`.`MapMarkerURL`, `SiteTree_Live`.`Title`
FROM `DirectoryItem`, `SiteTree_Live`
WHERE `DirectoryItem`.`ID` = `SiteTree_Live`.`ID` 
AND `DirectoryItem`.`Latitude` IS NOT NULL AND `DirectoryItem`.`Longitude` IS NOT NULL
ORDER BY `SiteTree_Live`.`Title`');

$MapItems = new ArrayList();
foreach ($DirectoryItems as $DirectoryItem) {
  $MapItems->push(new ArrayData(array(
      "Latitude" => $DirectoryItem['Latitude'],
      "Longitude" => $DirectoryItem['Longitude'],
      "MapMarkerURL" => $DirectoryItem['MapMarkerURL'],
      "Title" => addslashes($DirectoryItem['Title']),
      "Link" => ??????
  )));
}

But this falls over when it comes to getting the link to the DirectoryItem.

I thought about adding the Link as a DB field in DirectoryItem but that's beginning to feel needless complicated for what should be a straightforward operation.

What is the best way of getting the information for 200+ DirectoryItems in a single query?

Answer 1

Did you have a look into caching? If you show the same items on the map all the time you don't need to hit the db on every request.

See

• Silverstripe Docs for caching
• partial caching of elements of your site
• Static publisher module for real fast, static pages managed with SilverStripe
• Static publish queue module, another approach for generating static pages

It takes a huge load off your server if you cache properly.

If you still have problems when caching you should think about a better server.