Join Log in
Score:0
Baud avatar Drupal

simple view of a custom entity is extremely slow

sa flag
Baud

I have created a custom entity to store payment information:

 * @ContentEntityType(
 *   id = "bank_operation",
 *   label = @Translation("Bank operation"),
 *   handlers = {
 ...
 *   },
 *   base_table = "bank_operation",
 *   fieldable = TRUE,
 *   revisionable = FALSE,
 *   translatable = FALSE,
 *   internal = TRUE,
 *   entity_keys = {
 *     "id" = "id",
 *     "uuid" = "uuid"
 *   },
 *   common_reference_target = TRUE,
 *...
 *   },
 * )
  public static function baseFieldDefinitions(EntityTypeInterface $entity_type) {

    // Standard field, used as unique if primary index.
    $fields['id'] = BaseFieldDefinition::create('integer')
      ->setLabel(t('ID'))
      ->setDescription(t('The ID of the BankOperation entity.'))
      ->setReadOnly(TRUE);

    // Standard field, unique outside of the scope of the current project.
    $fields['uuid'] = BaseFieldDefinition::create('uuid')
      ->setLabel(t('UUID'))
      ->setDescription(t('The UUID of the BankOperation entity.'))
      ->setReadOnly(TRUE);

    return $fields;
  }

Through the GUI, I have added 17 other fields: Operation date, Statement date , amount, account number, Statement number, ...

And I have created a simple view (without any relation) to display my Bank operation entities (the count is approx 10 000 items displayed 40 by 40). It display the 7 main fields in a table.

Unfortunately this view is taking +/- 1-2 minutes to be displayed

What should I do to speed up this view? Should I add few indexes? if yes how?

Here are the request made by the view:

SELECT "bank_operation"."id" AS "id", DATE_FORMAT(bank_operation__field_operation_date.field_operation_date_value, '%Y%m%d') AS "bank_operation__field_operation_date_field_operation_date_va"
FROM
{bank_operation} "bank_operation"
LEFT JOIN {bank_operation__field_fake_bo} "bank_operation__field_fake_bo" ON bank_operation.id = bank_operation__field_fake_bo.entity_id AND bank_operation__field_fake_bo.deleted = '0'
LEFT JOIN {bank_operation__field_company} "bank_operation__field_company" ON bank_operation.id = bank_operation__field_company.entity_id AND bank_operation__field_company.deleted = '0'
LEFT JOIN {bank_operation__field_operation_date} "bank_operation__field_operation_date" ON bank_operation.id = bank_operation__field_operation_date.entity_id AND bank_operation__field_operation_date.deleted = '0'
WHERE ("bank_operation__field_company"."field_company_target_id" = '00597853560') AND ("bank_operation__field_fake_bo"."field_fake_bo_value" = '1')
ORDER BY "bank_operation__field_operation_date_field_operation_date_va" DESC

And this ones:

[0.000234 ms] SELECT "cid", "data", "created", "expire", "serialized", "tags", "checksum" FROM "cache_discovery" WHERE "cid" IN ( views:exposed_form ) ORDER BY "cid"

This ones is repeated 11 times (really strange)

[0.000299 ms] SELECT "s".*, "t"."language" AS "language", "t"."translation" AS "translation", "t"."customized" AS "customized"
FROM
"locales_source" "s"
LEFT OUTER JOIN "locales_target" "t" ON t.lid = s.lid AND t.language = fr
WHERE ("s"."source" IN (Is less than)) AND ("s"."context" IN ())

Then those ones (the last is long as 15 pages):

[9.7E-5 ms] SELECT "cid", "data", "created", "expire", "serialized", "tags", "checksum" FROM "cache_discovery" WHERE "cid" IN ( views:pager ) ORDER BY "cid"
[0.000149 ms] SELECT "cid", "data", "created", "expire", "serialized", "tags", "checksum" FROM "cache_discovery" WHERE "cid" IN ( views:access ) ORDER BY "cid"
[0.000137 ms] SELECT "cid", "data", "created", "expire", "serialized", "tags", "checksum" FROM "cache_discovery" WHERE "cid" IN ( views:join ) ORDER BY "cid"
[0.000213 ms] SELECT "cid", "data", "created", "expire", "serialized", "tags", "checksum" FROM "cache_discovery" WHERE "cid" IN ( views:argument_validator ) ORDER BY "cid"
[0.000533 ms] SELECT "cid", "data", "created", "expire", "serialized", "tags", "checksum" FROM "cache_discovery" WHERE "cid" IN ( entity_type_definitions.installed ) ORDER BY "cid"
[0.000288 ms] SELECT "cid", "data", "created", "expire", "serialized", "tags", "checksum" FROM "cache_discovery" WHERE "cid" IN ( bank_operation.field_storage_definitions.installed ) ORDER BY "cid"
[0.000277 ms] SELECT "cid", "data", "created", "expire", "serialized", "tags", "checksum" FROM "cache_discovery" WHERE "cid" IN ( views:cache ) ORDER BY "cid"
[0.037341 ms] SELECT COUNT(*) AS "expression"
FROM
(SELECT 1 AS "expression"
FROM
"bank_operation" "bank_operation"
LEFT JOIN "bank_operation__field_fake_bo" "bank_operation__field_fake_bo" ON bank_operation.id = bank_operation__field_fake_bo.entity_id AND bank_operation__field_fake_bo.deleted = 0
LEFT JOIN "bank_operation__field_company" "bank_operation__field_company" ON bank_operation.id = bank_operation__field_company.entity_id AND bank_operation__field_company.deleted = 0
LEFT JOIN "bank_operation__field_operation_date" "bank_operation__field_operation_date" ON bank_operation.id = bank_operation__field_operation_date.entity_id AND bank_operation__field_operation_date.deleted = 0
WHERE ("bank_operation__field_company"."field_company_target_id" = 00597853560) AND ("bank_operation__field_fake_bo"."field_fake_bo_value" = 1)) "subquery"
[0.050103 ms] SELECT "bank_operation"."id" AS "id", DATE_FORMAT(bank_operation__field_operation_date.field_operation_date_value, '%Y%m%d') AS "bank_operation__field_operation_date_field_operation_date_va"
FROM
"bank_operation" "bank_operation"
LEFT JOIN "bank_operation__field_fake_bo" "bank_operation__field_fake_bo" ON bank_operation.id = bank_operation__field_fake_bo.entity_id AND bank_operation__field_fake_bo.deleted = 0
LEFT JOIN "bank_operation__field_company" "bank_operation__field_company" ON bank_operation.id = bank_operation__field_company.entity_id AND bank_operation__field_company.deleted = 0
LEFT JOIN "bank_operation__field_operation_date" "bank_operation__field_operation_date" ON bank_operation.id = bank_operation__field_operation_date.entity_id AND bank_operation__field_operation_date.deleted = 0
WHERE ("bank_operation__field_company"."field_company_target_id" = 00597853560) AND ("bank_operation__field_fake_bo"."field_fake_bo_value" = 1)
ORDER BY "bank_operation__field_operation_date_field_operation_date_va" DESC
[0.043597 ms] SELECT "cid", "data", "created", "expire", "serialized", "tags", "checksum" FROM "cache_entity" WHERE "cid" IN ( values:bank_operation:10403, values:bank_operation:10381, values:bank_operation:3408, values:bank_operation:2790, values:bank_operation:3639, values:bank_operation:3640, values:bank_operation:3634, values:bank_operation:3647, values:bank_operation:2799, values:bank_operation:9,
 values:bank_operation:3405, values:bank_operation:10379, values:bank_operation:3668, values:bank_operation:10322, values:bank_operation:3643, values:bank_operation:3664, values:bank_operation:3416,
 values:bank_operation:3381, values:bank_operation:3606, values:bank_operation:10286, values:bank_operation:10288, values:bank_operation:3604, values:bank_operation:10290, values:bank_operation:3667,
 values:bank_operation:10321, values:bank_operation:62, values:bank_operation:3160, values:bank_operation:3420, values:bank_operation:3446, values:bank_operation:3575, values:bank_operation:3428,
 values:bank_operation:10402,.....
 ... 15 full pages of values ...

And another 30 pages of the following

[0.019228 ms] SELECT "base"."id" AS "id", "base"."uuid" AS "uuid"
FROM
"bank_operation" "base"
WHERE "base"."id" IN (10403, 10381, 3408, 2790, 3639, 3640, 3634, 3647, 2799, 9, 3405, 10379, 3668, 10322, 3643, 3664, 3416, 3381, 3606, 10286, 10288, 3604, 10290, 3667, 10321, 62, 3160, 3420, 3446, 3575,
 3428, 10402, 3512, 3145, 3520, 3437, 3508, 8807, 8852, 8916, 10394, 8840, 8949, 8898, 8828, 8822, 8816, 8880, 10396, 8925, 8810, 8631, 8919, 8849, 8907, 8952, 8837, 8786, 10398, 8825, 8262, 8819, 8928, 10387,
 10400, ... 3 pages of id ...
  
[0.00032 ms] SELECT "cid", "data", "created", "expire", "serialized", "tags", "checksum" FROM "cache_discovery" WHERE "cid" IN ( entity_bundle_field_definitions:bank_operation:bank_operation:fr ) ORDER BY "cid"
[0.869771 ms] SELECT "t".*
FROM
"bank_operation__field_amount" "t"
WHERE ("entity_id" IN (2913, 7134, 2591, 2586, 7060, 2616, 9744, 3115, 1576, 6496, 821, 5057, 8491, 5844, 579, 6638, 7852, 3807, 3167, 5793, 9467, 6534, 10295, 3363, 7654, 2256, 5125, 2043, 10074, 8873, 7061, 1284,
 10260, 9587, 10206, 1677, 1621, 2826, 463, 4036, 3129, 4887, 3445, 7235, 6240, 2083, 2923, 7564, 5812, 859, 7570, 7807, 10207, 1640, 736, 5417, 1577, 7427, 7792, 8261, 53, 2597, 8747, 2810, 7261, 3985, 1606, 6998, 
 266, 8598, ... 3 pages of id ...
 
 The same happens for each field on approx 30 pages

The following become very huge:

[0.000243 ms] SELECT "cid", "data", "created", "expire", "serialized", "tags", "checksum" FROM "cache_discovery" WHERE "cid" IN ( entity_bundle_info:fr ) ORDER BY "cid"
[6.4E-5 ms] SELECT "tag", "invalidations" FROM "cachetags" WHERE "tag" IN ( entity_bundles )
[0.000244 ms] SELECT "cid", "data", "created", "expire", "serialized", "tags", "checksum" FROM "cache_config" WHERE "cid" IN ( user.settings ) ORDER BY "cid"
[0.000145 ms] SELECT "cid", "data", "created", "expire", "serialized", "tags", "checksum" FROM "cache_config" WHERE "cid" IN ( language.fr:user.settings ) ORDER BY "cid"
[0.000416 ms] SELECT "cid", "data", "created", "expire", "serialized", "tags", "checksum" FROM "cache_discovery" WHERE "cid" IN ( entity_field_map ) ORDER BY "cid"
[0.000133 ms] SELECT "tag", "invalidations" FROM "cachetags" WHERE "tag" IN ( bank_operation_values )
[0.002551 ms] INSERT INTO "cache_entity" ("cid", "expire", "created", "tags", "checksum", "data", "serialized") VALUES (values:bank_operation:2913, -1, 1667477872.318, bank_operation_values entity_field_info, 8818, O
:42:"Drupal\bank_operation\Entity\BankOperation":29:{s:9:"*values";a:18:{s:2:"id";a:1:{s:9:"x-default";s:4:"2913";}s:4:"uuid";a:1:{s:9:"x-default";s:36:"0000de0a-d738-481b-8d45-60f4f5f57539";}s:12:"field_amount";
a:1:{s:9:"x-default";a:1:{i:0;a:1:{s:5:"value";s:9:"-16661.70";}}}s:13:"field_company";a:1:{s:9:"x-default";a:1:{i:0;a:1:{s:9:"target_id";s:11:"00597853560";}}}s:21:"field_company_account";a:1:{s:9:"x-default";
a:1:{i:0;a:1:{s:5:"value";s:16:"BE07363143795466";}}}s:17:"field_company_bic";a:1:{s:9:"x-default";a:1:{i:0;a:1:{s:5:"value";s:8:"BBRUBEBB";}}}s:26:"field_counterparty_account";a:1:{s:9:"x-default";a:1:{i:0;a:1:
... the serialized value are listed on 1000 or 2000 pages ....

I stop here as I have thousand of pages with SQL queries...

156
1 + 11
Kevin avatar
in flag
Kevin
10,000 _all at once_? No limit?
0
Reply
Baud avatar
sa flag
Baud
no, the limit is set to 40 (I update my text)
0
Reply
in flag
Joseph
Let's start by debugging your view query. Go to Structure -> Views -> Settings and enable "Show the SQL query" and "Show other queries run during render during live preview". Then configure your view. The live preview should tell you what your SQL query to generate the view looks like and what other supporting queries were made. Other places to look at is 1) is your view caching data correctly? 2) Is there some logic (e.g. access checks, logging) happening before rendering the individual fields? 3) Does this view have relationships to heavier entities? 4) Is your db under heavy load?
0
Reply
Baud avatar
sa flag
Baud
@Joseph Thank you! I will update my question with those data. For 3) No relationships and 4) I am the only user.
0
Reply
Kevin avatar
in flag
Kevin
Are the results being _rendered_ or are they fields???
0
Reply
Kevin avatar
in flag
Kevin
https://drupal.stackexchange.com/a/221445/57
0
Reply
Baud avatar
sa flag
Baud
@Joseph: I have updated the question with the queries generated by the view. There are thousand of pages... this is so huge!!!!
0
Reply
Baud avatar
sa flag
Baud
@kevin : they are field values (string or number)... I have removed the links to gain time
0
Reply
Baud avatar
sa flag
Baud
@kevin you are pointing me to a question explaining how to add an index. Do you mean that adding an index will speed up this view? If yes, which fields should I index?
0
Reply
Chris4783 avatar
gb flag
Chris4783
Are you using a form? If so, what happens if you remove it. Check if the company ID has an index or primary key. Is the loading time faster, if you are logged in as user 1? Is the loading time faster, if you change the limit to only show 1 entry without a pager?
0
Reply
Baud avatar
sa flag
Baud
@Chris I removed the exposed form and the view is displaying much faster: request time=654.51 ms but rendering time=27493.87 ms. I don't know why the rendering is so slow because I removed all the links and I removed also pathauto which was slowing dow many other views. -- No change for user 1 -- Displaying only one record doesn't change anything [rendering time=27047.45 ms]
0
Reply
Score:0
Baud avatar Drupal
sa flag
Baud

I found the solution. Many thanks for those who tried to help me. The problem was not the SQL but the rendering time and this was due to the module Views Aggregator Plus which was displaying the results of my view.

Sorry for the time spent on such stupid problem

+ 0
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.