8

I am working on a Silverstripe custom report which had been created before my time at my current employer. The report returns CompetitionEntry rows for a specific Competition along with the title of the competition (and a couple of other fields which I have removed to keep the code snippets tidy).

When we tried to export 10,000 or more results, it would timeout. The MySQL general log showed that the query running was looking up the competition each time for every single competition entry:

SELECT DISTINCT Created, LastEdited, URLSegment, Title, MenuTitle, Content, MetaDescription, ExtraMeta, ShowInMenus, ShowInSearch, Sort, HasBrokenFile, HasBrokenLink, ReportClass, CanViewType, CanEditType, Priority, ProvideComments, MetaTitle, CommentNotificationEmail, Version, ParentID, TrackingTagName, CustomQuestion, EnableCustomQuestion, CustomQuestionRequired, TermsAndConditions, SubmittedText, HeaderContent, InactiveContent, BackgroundColour, Active, Image1ID, Image2ID, CASE WHEN ClassName IS NOT NULL THEN ClassName ELSE 'SiteTree' END AS RecordClassName 
FROM SiteTree_Live 
LEFT JOIN Page_Live ON Page_Live.ID = SiteTree_Live.ID 
LEFT JOIN CompetitionPage_Live ON CompetitionPage_Live.ID = SiteTree_Live.ID 
WHERE (SiteTree_Live.ID = 308) AND (SiteTree_Live.ClassName IN ('CompetitionPage')) 
ORDER BY Active DESC 
LIMIT 1

Columns function:

public function columns() {
    return array(
        "Competition.Title" => "Competition",
        "FirstName" => "First name",
        "LastName" => "Last name",
        "Email" => "Email"
    );
}

My getExportFields:

public function getExportFields(){
    return array(
        "Competition.Title" => "Competition",
        "Date" => "Date",
        "FirstName" => "FirstName",
        "LastName" => "LastName",
        "Email" => "Email",
    );
}

By commenting out the reference to Competition.Title the query runs quickly and gives me all of the entries.

I cannot see how to make the query work efficiently, or if it's possible to just lookup the competition once, and then add the title (and one or two further competition fields) to each competition entry.

scrowler
  • 23,403
  • 9
  • 52
  • 87
Alexander Holsgrove
  • 1,687
  • 3
  • 23
  • 51
  • I guess this is difficult because each CompetitionEntry could have a different Competition, but the reports module (probably framework) should probably still be able to cache the Competition by the ID to prevent the DB lookups. Have you considered logging an issue on https://github.com/silverstripe/silverstripe-reports about this? – scrowler May 15 '17 at 22:23
  • 1
    It's been a while since I've worked with Silverstripe reports, but could you override the sourceQuery method and optimise it? For other exports, (not on Silverstripe), I've rewritten the exports so it does the query into an array and then I loop the array to output the file as usually the loops make the queries at the same time as writing the rows which has caused me problems before. Hi Alex too, how are you? – PhilS May 18 '17 at 07:40

0 Answers0