How do you retrieve the data? Do you use a datasource microflow or retrieve from the database? 300k records in a datagrid that retrieves from the database should work just fine.
Well, I kinda found the problem. When upgrading to MX8 we had to replace the EmailTemplate module. With the replacement, the default constraint settings were reapplied. So there was a Entity-level contraint, limiting the editability of already sent e-mails.
The query to the database was VERY effected by this. Check this out.
First the query that was fired what seemed to be my problem. Very weird and unreadable in my opinion...
SELECT
"emailtemplate$email"."id",
"emailtemplate$email"."subject",
"emailtemplate$email"."status",
CASE WHEN TRUE THEN TRUE END AS "__sec_1",
CASE WHEN TRUE THEN TRUE END AS "__sec_2"
FROM "emailtemplate$email"
WHERE (("emailtemplate$email"."id" IN(
SELECT "exist_1077587876"."id" FROM "emailtemplate$email" "exist_1077587876"
WHERE ("exist_1077587876"."status" != ? OR "exist_1077587876"."status" IS NULL))
OR "emailtemplate$email"."id" IN(
SELECT "exist_1077587876"."id" FROM "emailtemplate$email" "exist_1077587876"
WHERE "exist_1077587876"."status" = ?)))
AND "emailtemplate$email"."status" = ?
ORDER BY "emailtemplate$email"."date" ASC, "emailtemplate$email"."id" ASC
LIMIT ?
And this is the query after removing the entity level xpath constraint.
SELECT
"emailtemplate$email"."id",
"emailtemplate$email"."subject",
"emailtemplate$email"."status"
FROM
"emailtemplate$email"
ORDER BY
"emailtemplate$email"."date" ASC,
"emailtemplate$email"."id" ASC
LIMIT ?
Now it’s all fast again :)
(Can anyone please explain why this query is so increadably slow? I know there are subqueries involved, but the page limit was set to 50. But that query took ages to finish...)