Datagrid with MANY records crashes app after upgrading MX6 to MX8

0
Hi there, We use the EmailTemplate module to send e-mails. We keep the sent e-mails in the Email entity as a maillog. When we were on MX6 we had no problem in showing the datagrid. Even when it contained about 300.000 records. Now, in MX8 when the datagrid is being loaded, our app just kind of crashes, because the database will take a VERY long time to execute the query. Can anyone tell me what changed in MX7/MX8 so that we cannot load our datagrid anymore? And how to you people fix this problem? Edit: Just a plain xpath retrieve... Cheers, Bert
asked
2 answers
0

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.

answered
0

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...)

answered