My page takes a very long time to access the summary view of the collection Copropriété
Project name: .Sergic
Team name: gestion
Environment name: Développement
Agent (forest package) name & version: 9
Database type: Postgres
Recent changes made on your end if any: upgrade to v9
Hello @Soufiane ,
If your collection “Copropriété” is a smart collection, the default behaviour is that your agent executes a SQL query to fetch all the results in memory, then apply some filters and pagination. If the collection has a lot of records, it can become very long to load the data. The solution to optimise performance is to implement the most used filters in the API route defining the collection, adapting the SQL request to return less records.
According to your screeshot, I see that you have several segments in the collection. It can be a good start to implement the filters that these segments are using.
If your collection is not a smart collection, the latency can be due to smart fields or smart relationships computation if they are too many.
I did a test, I commented smart fields on the collection ‘Copropriété’, but it doesn’t change anything, the loading time of the collection 'Copropriété to access in the summary view takes a long time.
How many records does the collection display ?
Can you share the definition of the collection on your code (which builds the result of the /list query)?
15records per page
fields: [
{
field: 'nombre de lots principaux',
type: 'Number',
get: place => models.parcels.count({
where: { place_id: place.id },
include: [{
model: models.parcelKinds,
as: 'parcelKind',
where: {
principal: true
}
}]
}).then(result => result)
}, {
field: 'nombre de lots annexes',
type: 'Number',
get: place => models.parcels.count({
where: { place_id: place.id },
include: [{
model: models.parcelKinds,
as: 'parcelKind',
where: {
principal: false
}
}]
}).then(result => result)
}, {
field: 'nombre de stationnements',
type: 'Number',
get: place => models.parcels.count({
where: { place_id: place.id },
include: [{
model: models.parcelKinds,
as: 'parcelKind',
where: {
kind: 'Parking',
principal: false
}
}]
}).then(result => result)
}, {
field: 'nombre de garages',
type: 'Number',
get: place => models.parcels.count({
where: { place_id: place.id },
include: [{
model: models.parcelKinds,
as: 'parcelKind',
where: {
kind: 'Garage',
principal: false
}
}]
}).then(result => result)
}, {
field: 'nombre de box/local',
type: 'Number',
get: place => models.parcels.count({
where: { place_id: place.id },
include: [{
model: models.parcelKinds,
as: 'parcelKind',
where: {
kind: 'Local',
principal: false
}
}]
}).then(result => result)
}, {
field: 'nombre de caves',
type: 'Number',
get: place => models.parcels.count({
where: { place_id: place.id },
include: [{
model: models.parcelKinds,
as: 'parcelKind',
where: {
kind: 'Cave',
principal: false
}
}]
}).then(result => result)
}, {
field: 'nombre de batiments',
type: 'Number',
get: place => models.buildings.count(
{ where: { place_id: place.id } }
).then(result => result
)
},
{
field: 'Date de debut de mandat de syndic',
type: 'Date'/*,
get: place => models.managementMandates.findAll({
where: { place_id: place.id }
}).then(managementMandates => {
const today = moment();
const currentMandate = managementMandates.find(managementMandate => moment(managementMandate.startDate) < today && moment(managementMandate.endDate) >= today);
return currentMandate ? currentMandate.startDate : null;
})*/
}, {
field: 'Date de fin de mandat previsionnel',
type: 'Date'/*,
get: place => models.managementMandates.findAll({
where: { place_id: place.id }
}).then(managementMandates => {
const today = moment();
const currentMandate = managementMandates.find(managementMandate => moment(managementMandate.startDate) < today && moment(managementMandate.endDate) >= today);
return currentMandate ? currentMandate.endDate : null;
})*/
}, {
field: 'Honoraires annuels TTC',
type: 'Number'/*,
get: place => models.managementMandates.findAll({
where: { place_id: place.id }
}).then(managementMandates => {
const today = moment();
const currentMandate = managementMandates.find(managementMandate => moment(managementMandate.startDate) < today && moment(managementMandate.endDate) >= today);
return currentMandate ? currentMandate.annualFees : null;
})*/
}, {
field: 'adresse',
type: 'String',
get: place => models.addresses.findOne({
where: {
addressable_id: place.id,
addressable_type: 'Place'
}
}).then(address =>
address && address.street ? `${address.streetNumber ? address.streetNumber : ''} ${address.street} ${address.complementary ? address.complementary : ''} ${address.zipCode} ${address.city.toUpperCase()} ` : ''),
search: async (query, search) => {
const { QueryTypes, Op } = models.objectMapping;
const split = search.split(' ');
// NOTICE: complexe raw query to gather all ids from the places collection that match your business needs
const placesIds = await models.connections.default.query(`
SELECT places.id FROM places
INNER JOIN addresses ON places.id = addresses.addressable_id
WHERE addresses.addressable_type = 'Place'
AND (addresses.street ILIKE '%${split[0]}%'
OR addresses.zip_code ILIKE '%${split[0]}%'
OR addresses.city ILIKE '%${split[0]}%'
OR addresses.street ILIKE '%${split[1]}%'
OR addresses.zip_code ILIKE '%${split[1]}%'
OR addresses.city ILIKE '%${split[1]}%'
)`,
{ type: QueryTypes.SELECT });
// NOTICE: fill the where condition with a simple `id in (ArrayOfIds)` to match ids returned by the complexe query making the polymorphic joins
const searchCondition = { id: { [Op.in]: placesIds.map(a => a.id) } };
query.where[Op.and][0][Op.or].push(searchCondition);
}
}, {
field: 'pre-etats-dates',
type: ['String'],
reference: 'preEtatDateRequests.id'
}, {
field: 'fournisseurs',
type: ['String'],
reference: 'sageProviders.matricule'
}, {
field: 'attestation',
type: 'File'
}, {
field: 'ficheSynthetique',
type: 'File'
}, {
field: 'nom du mandataire',
type: 'String'/*,
get: place => models.propertyRepresentatives.findOne({
where: { place_id: place.id },
order: [['created_at', 'DESC']],
}).then(propertyRepresentative => propertyRepresentative ? propertyRepresentative.fullName : '')*/
}, {
field: 'date de nomination',
type: 'Date'/*,
get: place => models.propertyRepresentatives.findOne({
where: { place_id: place.id },
order: [['created_at', 'DESC']],
}).then(propertyRepresentative => propertyRepresentative ? propertyRepresentative.nominationDate : null)*/
}, {
field: 'date fin de mission mandataire',
type: 'Date'/*,
get: place => models.propertyRepresentatives.findOne({
where: { place_id: place.id },
order: [['created_at', 'DESC']],
}).then(propertyRepresentative => propertyRepresentative ? propertyRepresentative.missionEndsAt : null)*/
}, {
field: 'categories',
type: ['String'],
reference: 'categories.id'
}, {
field: 'collaborateurs',
type: ['String'],
reference: 'roles.id'
}, {
field: 'images',
type: ['String'],
reference: 'attachmentables.id'
}, {
field: 'factures-a-payer',
type: ['String'],
reference: 'place-bills-to-pay.id'
}, {
field: 'factures-payees',
type: ['String'],
reference: 'place-bills-paid.id'
}, {
field: 'fond de travaux disponible',
type: 'Number'
}, {
field: 'tresorerie actuelle',
type: 'Number'
}, {
field: 'depenses',
type: 'Number'
}, {
field: 'total factures a payer',
type: 'Number'
}, {
field: 'budget vote',
type: 'Number'
}, {
field: 'avance de tresorerie',
type: 'Number'
// en cours de modification cote Parthena
}, {
field: 'fonds prevoyance',
type: 'Number'
}, {
field: 'title',
type: 'String',
get: place => `${place.sergicIdFull} - ${place.displayName}`,
search: (query, search) => {
const split = search.split(' ');
const searchCondition = {
[Op.or]: [
{ 'sergic_id_full': { [Op.iLike]: `%${split[0]}%` } },
{ 'sergic_id_full': { [Op.iLike]: `%${split[1]}%` } },
{ 'display_name': { [Op.iLike]: `%${split[0]}%` } },
{ 'display_name': { [Op.iLike]: `%${split[1]}%` } },
],
};
query.where[Op.and][0][Op.or].push(searchCondition);
return query;
}
}, {
field: 'tickets',
type: ['String'],
reference: 'placeTickets.id'
}, {
field: 'alertes-assurances-impayees',
type: ['String'],
reference: 'placeAlerts.id'
}, {
field: 'alertes-tresorerie-insuffisante',
type: ['String'],
reference: 'placeAlerts.id'
}, {
field: 'alertes-rapprochements-bancaires',
type: ['String'],
reference: 'placeAlerts.id'
}, {
field: 'coproprietaires-en-impayes',
type: ['String'],
reference: 'alerts.id'
}, {
field: 'gestionnaire',
type: 'String',
get: place => models.roles.findOne({
where: {
name: 'coowner_manager',
resource_type: 'Place',
resource_id: place.id
},
include: [{
model: models.identities,
as: 'identitiesThroughIdentitiesRoles'
}]
}).then(role => `${role ? role.identitiesThroughIdentitiesRoles[0].firstName : ''} ${role ? role.identitiesThroughIdentitiesRoles[0].lastName.toUpperCase() : ''}`),
search: async (query, search) => {
const { QueryTypes, Op } = models.objectMapping;
const split = search.split(' ');
// NOTICE: complexe raw query to gather all ids from the places collection that match your business needs
const placesIds = await models.connections.default.query(`
SELECT places.id FROM places
INNER JOIN roles ON places.id = roles.resource_id
INNER JOIN identities_roles ON identities_roles.role_id = roles.id
INNER JOIN identities ON identities.id = identities_roles.identity_id
WHERE roles.name = 'coowner_manager'
AND roles.resource_type = 'Place'
AND (identities.first_name ILIKE '%${split[0]}%'
OR identities.last_name ILIKE '%${split[0]}%'
OR identities.first_name ILIKE '%${split[1]}%'
OR identities.last_name ILIKE '%${split[1]}%')`,
{ type: QueryTypes.SELECT });
// NOTICE: fill the where condition with a simple `id in (ArrayOfIds)` to match ids returned by the complexe query making the polymorphic joins
const searchCondition = { id: { [Op.in]: placesIds.map(a => a.id) } };
query.where[Op.and][0][Op.or].push(searchCondition);
}
}, {
field: 'assistant copro',
type: 'String',
get: place => models.roles.findOne({
where: {
name: 'coowner_assistant',
resource_type: 'Place',
resource_id: place.id
},
include: [{
model: models.identities,
as: 'identitiesThroughIdentitiesRoles'
}]
}).then(role =>
`${role ? role.identitiesThroughIdentitiesRoles[0].firstName : ''} ${role ? role.identitiesThroughIdentitiesRoles[0].lastName.toUpperCase() : ''}`),
search: async (query, search) => {
const { QueryTypes, Op } = models.objectMapping;
const split = search.split(' ');
// NOTICE: complexe raw query to gather all ids from the places collection that match your business needs
const placesIds = await models.connections.default.query(`
SELECT places.id FROM places
INNER JOIN roles ON places.id = roles.resource_id
INNER JOIN identities_roles ON identities_roles.role_id = roles.id
INNER JOIN identities ON identities.id = identities_roles.identity_id
WHERE roles.name = 'coowner_assistant'
AND roles.resource_type = 'Place'
AND (identities.first_name ILIKE '%${split[0]}%'
OR identities.last_name ILIKE '%${split[0]}%'
OR identities.first_name ILIKE '%${split[1]}%'
OR identities.last_name ILIKE '%${split[1]}%')`,
{ type: QueryTypes.SELECT });
// NOTICE: fill the where condition with a simple `id in (ArrayOfIds)` to match ids returned by the complexe query making the polymorphic joins
const searchCondition = { id: { [Op.in]: placesIds.map(a => a.id) } };
query.where[Op.and][0][Op.or].push(searchCondition);
}
},```Thank you for the code.
There is obviously a lot going on in this collection ! You said that when you commented the smart fields, it made no difference, so I believe the issue is somewhere else.
I was looking for the code that implements the /get route. Sorry for the ambiguity of my question.
In this route handler, you can certainly optimize your SQL request.
router.get('/places/:recordId', permissionMiddlewareCreator.details(), (request, response, next) => {
// Learn what this route does here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/routes/default-routes#get-a-record
if (request.params.recordId === 'count') {
return next();
}
// const { params } = request;
const { query, user } = request;
const recordGetter = new RecordGetter(models.places, user, query);
recordGetter.get(request.params.recordId)
//recordGetter.get(params.recordId)
.then(async record => {
/*const downloadCoownership = axios.put(`${API_URL}/forest_admin/places/${params.recordId}/download_coownership_register`, null, {
headers: {
'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
'X-CURRENT-USER-EMAIL': process.env.SERVICE_EMAIL
},
});*/
models.managementMandates.findAll({
where: { place_id: request.params.recordId }
}).then(managementMandates => {
const today = moment();
const currentMandate = managementMandates.find(managementMandate => moment(managementMandate.startDate) < today && moment(managementMandate.endDate) >= today);
record['Date de debut de mandat de syndic'] = currentMandate ? currentMandate.startDate : null;
record['Date de fin de mandat previsionnel'] = currentMandate ? currentMandate.endDate : null;
record['Honoraires annuels TTC'] = currentMandate ? currentMandate.annualFees : null;
});
models.propertyRepresentatives.findOne({
where: { place_id: request.params.recordId },
order: [['created_at', 'DESC']],
}).then(propertyRepresentative => {
record['nom du mandataire'] = propertyRepresentative ? propertyRepresentative.fullName : '';
record['date de nomination'] = propertyRepresentative ? propertyRepresentative.nominationDate : null;
record['date fin de mission mandataire'] = propertyRepresentative ? propertyRepresentative.missionEndsAt : null;
});
// models.parcels.count({
// where: { place_id: place.id },
// include: [{
// model: models.parcelKinds,
// as: 'parcelKind',
// where: {
// principal: true
// }
// }]
// }).then(result => {
// });
const sageVotedEstimatedBudgets = axios.get(`${API_URL}/forest_admin/places/${request.params.recordId}/sage_voted_estimated_budgets`, {
headers: {
'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
'X-CURRENT-USER-EMAIL': process.env.SERVICE_EMAIL
}
});
const sagePlaceBalance = axios.get(`${API_URL}/forest_admin/places/${request.params.recordId}/sage_place_balance`, {
headers: {
'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
'X-CURRENT-USER-EMAIL': process.env.SERVICE_EMAIL
}
});
const sageFiscalYearBudgets = axios.get(`${API_URL}/forest_admin/places/${request.params.recordId}/sage_fiscal_year_budgets`, {
headers: {
'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
'X-CURRENT-USER-EMAIL': process.env.SERVICE_EMAIL
}
});
const result = await Promise.allSettled([sageVotedEstimatedBudgets, sagePlaceBalance, sageFiscalYearBudgets, /*downloadCoownership*/]);
//record.ficheSynthetique = result[3]?.value?.data?.ficheSynthetique;
//record.attestation = result[3]?.value?.data?.attestation;
record['fond de travaux disponible'] = result[0]?.value?.data?.prevoyance;
record['avance de tresorerie'] = result[0]?.value?.data?.avance_tresorerie;
record['tresorerie actuelle'] = result[1]?.value?.data?.soldes.find(solde => solde.libelle === 'Trésorerie')?.solde;
record['total factures a payer'] = result[1]?.value?.data?.soldes.find(solde => solde.libelle === 'Facture à payer')?.solde;
record['fonds prevoyance'] = result[1]?.value?.data.soldes.find(solde => solde.libelle === 'Epargne prévoyance')?.solde;
record['depenses'] = result[2]?.value?.data?.exercices.find(p => p.en_cours)?.montant_consomme;
record['budget vote'] = result[2]?.value?.data?.exercices.find(p => p.en_cours)?.montant_vote;
return recordGetter.serialize(record);
})
.then(recordSerialized => response.send(recordSerialized))
.catch(next);
});```This is the query to get one record only, not to display the table.
Anyway I think I see from where does your performance issue come from.
You have a lot of data coming from different places in this table, I’m pretty sure the latency comes from the time your server takes to answer to the /get request. You can try to monitor it to be sure.
If the request takes a lot of time, it’s up to you to try to optimise the bits of code that takes most time. My guess in your situation would be to try using a SQL view, but you’re certainly more qualified to see what suits you best.
I’m sorry not to be able to help you more. Let me know if the request is fast but you still got a long loading.