Запрос на продолжение занимает слишком много времени

У меня есть следующий запрос в продолжении

Providers.findAll({
    attributes: [
        "provider_id"
    ],
    include: [{
            model: ProvidersInsurance,
            attributes: ["insurance_id"],
            include: [{
                model: Insurance,
                attributes: ["insurance_id", "insurance_name"]
            }, ]
        },
        {
            model: ProvidersClients,
            attributes: ["client_id"],
            include: [{
                model: Clients,
                attributes: ["client_id", "client_name"]
            }, ]
        },
        {
            model: ProvidersCounties,
            attributes: ["county_id"],
            include: [{
                model: Counties,
                attributes: ["county_id", "county_name"]
            }, ]
        },
        {
            model: ProvidersSpecialities,
            attributes: ["speciality_id"],
            include: [{
                model: Specialities,
                attributes: ["speciality_id", "speciality_name"]
            }, ]
        },
        {
            model: ProvidersCategories,
            attributes: ["category_id"],
            include: [{
                model: Categories,
                attributes: ["category_id", "category_name"]
            }, ]
        }, {
            model: Telehealth,
            attributes: ["telehealth_name"]
        }


    ],
    where: {
        updatedAt: {
            [Op.between]: [req.query.fromdate, toDate]
        }
    }

}).then(providerData => {
    console.log(providerData)

}).catch(err => {
    console.log(err);
    res.status(404).send({
        reason: err.message
    })
});

Выполнение занимает более 4 минут. Необработанный запрос вышеизложенного:

SELECT `providers`.`provider_id`,
       `providers`.`providerFirstName`,
       `providers`.`providerLastName`,
       `providers`.`email`,
       `providers`.`phoneNumber`,
       `providers`.`address`,
       `providers`.`agencyName`,
       `providers`.`practiceCounty`,
       `providers`.`city`,
       `providers`.`state`,
       `providers`.`zip`,
       `providers`.`mondayHours`,
       `providers`.`tuesdayHours`,
       `providers`.`wednesdayHours`,
       `providers`.`thursdayHours`,
       `providers`.`fridayHours`,
       `providers`.`saturdayHours`,
       `providers`.`sundayHours`,
       `providers`.`additionalInfo`,
       `providers`.`website`,
       `providers`.`newClientsAcceptance`,
       `providers`.`updatedBy`,
       `providers`.`otherCategories`,
       `providers`.`otherInsurances`,
       `providers`.`otherTelehealthServices`,
       `providers`.`createdAt`,
       `providers`.`updatedAt`,
       `ProviderInsurances`.`provider_insurance_id` AS `ProviderInsurances.provider_insurance_id`,
       `ProviderInsurances`.`insurance_id` AS `ProviderInsurances.insurance_id`,
       `ProviderInsurances->Insurance`.`insurance_id` AS `ProviderInsurances.Insurance.insurance_id`,
       `ProviderInsurances->Insurance`.`insurance_name` AS `ProviderInsurances.Insurance.insurance_name`,
       `ProviderClients`.`provider_client_id` AS `ProviderClients.provider_client_id`,
       `ProviderClients`.`client_id` AS `ProviderClients.client_id`,
       `ProviderClients->Client`.`client_id` AS `ProviderClients.Client.client_id`,
       `ProviderClients->Client`.`client_name` AS `ProviderClients.Client.client_name`,
       `ProviderCounties`.`provider_county_id` AS `ProviderCounties.provider_county_id`,
       `ProviderCounties`.`county_id` AS `ProviderCounties.county_id`,
       `ProviderCounties->County`.`county_id` AS `ProviderCounties.County.county_id`,
       `ProviderCounties->County`.`county_name` AS `ProviderCounties.County.county_name`,
       `ProviderSpecialities`.`provider_speciality_id` AS `ProviderSpecialities.provider_speciality_id`,
       `ProviderSpecialities`.`speciality_id` AS `ProviderSpecialities.speciality_id`,
       `ProviderSpecialities->Speciality`.`speciality_id` AS `ProviderSpecialities.Speciality.speciality_id`,
       `ProviderSpecialities->Speciality`.`speciality_name` AS `ProviderSpecialities.Speciality.speciality_name`,
       `ProviderCategories`.`provider_category_id` AS `ProviderCategories.provider_category_id`,
       `ProviderCategories`.`category_id` AS `ProviderCategories.category_id`,
       `ProviderCategories->Category`.`category_id` AS `ProviderCategories.Category.category_id`,
       `ProviderCategories->Category`.`category_name` AS `ProviderCategories.Category.category_name`,
       `Telehealths`.`telehealth_id` AS `Telehealths.telehealth_id`,
       `Telehealths`.`telehealth_name` AS `Telehealths.telehealth_name`
FROM `providers` AS `providers`
LEFT OUTER JOIN `provider_insurances` AS `ProviderInsurances` ON `providers`.`provider_id` = `ProviderInsurances`.`provider_id`
LEFT OUTER JOIN `insurance` AS `ProviderInsurances->Insurance` ON `ProviderInsurances`.`insurance_id` = `ProviderInsurances->Insurance`.`insurance_id`
LEFT OUTER JOIN `provider_clients` AS `ProviderClients` ON `providers`.`provider_id` = `ProviderClients`.`provider_id`
LEFT OUTER JOIN `clients` AS `ProviderClients->Client` ON `ProviderClients`.`client_id` = `ProviderClients->Client`.`client_id`
LEFT OUTER JOIN `provider_counties` AS `ProviderCounties` ON `providers`.`provider_id` = `ProviderCounties`.`provider_id`
LEFT OUTER JOIN `counties` AS `ProviderCounties->County` ON `ProviderCounties`.`county_id` = `ProviderCounties->County`.`county_id`
LEFT OUTER JOIN `provider_specialities` AS `ProviderSpecialities` ON `providers`.`provider_id` = `ProviderSpecialities`.`provider_id`
LEFT OUTER JOIN `specialities` AS `ProviderSpecialities->Speciality` ON `ProviderSpecialities`.`speciality_id` = `ProviderSpecialities->Speciality`.`speciality_id`
LEFT OUTER JOIN `provider_categories` AS `ProviderCategories` ON `providers`.`provider_id` = `ProviderCategories`.`provider_id`
LEFT OUTER JOIN `categories` AS `ProviderCategories->Category` ON `ProviderCategories`.`category_id` = `ProviderCategories->Category`.`category_id`
LEFT OUTER JOIN `telehealth` AS `Telehealths` ON `providers`.`provider_id` = `Telehealths`.`provider_id`
WHERE `providers`.`updatedAt` BETWEEN '2019-08-06 18:30:00' AND '2019-08-08 00:00:00';

Когда я выполняю вышеуказанный запрос на сервере mysql, он дает результат менее чем за 1 секунду. Принимая во внимание, что когда я выполняю через узел и продолжаю, это занимает слишком много времени, то есть примерно 4 минуты

Я хотел бы знать, почему запрос на продолжение занимает слишком много времени


node.js sequelize.js query-optimization sequelize-typescript
person rji rji    schedule 08.08.2019    source источник
comment
Какое приблизительное количество записей возвращает запрос?   -  person Ihor Sakailiuk    schedule 08.08.2019
comment
Потому что sequelize отображает каждую строку в модель данных с использованием всей вложенности (include), фильтрации атрибутов (attributes) и множества других правил, которые вы предоставили.   -  person Ihor Sakailiuk    schedule 08.08.2019

Ответы (1)


arrow_upward
1
arrow_downward

если вы используете sequlize последнюю версию, вы можете использовать следующие параметры, это наверняка сократит много времени, потому что raw:true остановит sequlize создание экземпляра модели на основе результата:

{
    raw : true, //  sequelize will not try to format the results of the query, or build an instance of a model from the result
    nest : true // this will format your data
}

Providers.findAll({
    attributes: [
        ...
    ],
    include: [
        ....
    ],
    where: {
        ...
    },
    raw : true, // <---- HERE
    nest : true // <---- HERE
})
.then(providerData => {
    console.log(providerData)

}).catch(err => {
    console.log(err);
    res.status(404).send({
        reason: err.message
    })
});
person Vivek Doshi    schedule 08.08.2019