@Lepilov

Как оптимизировать запросы к базе данных?

Я отправляю в базу данных запрос на чтение /запись данных спаршенных из эксель файла. Данные - это от 4 до 10 тысяч строк json формата. Столкнулся с тем что запрос длится очень продолжительное время . На локальном компе (достаточно мощном ) занимает до 20 сек, а на серваке больше минуты и в итоге отваливается с ошибкой. Мне кажется что данных не так много что бы запрос так долго длился.

Метод create сериализатора выглядит так
def create(self, validated_data):
        # Get data from url context
        rfiid = self.context.get('rfiid')
        vendor_id = self.context.get('vendor')
        analyst_id = self.context.get('analyst')
        vendor = Vendors.objects.get(vendorid=vendor_id)
        round = Rfis.objects.get(rfiid=rfiid)
        current_scoring_round = self.context.get('current_scoring_round')

        # for update rfipartisipatiostatus analyst/vendor response (1 or 0)
        status_info = self.context.get('status_info')

        # save CI
        company_information = self.context.get('Company_info')
        for ci in company_information:
            ciq, _ = CompanyGeneralInfoQuestion.objects.get_or_create(question=ci.get('question'), rfi=round)
            cia, _ = CompanyGeneralInfoAnswers.objects.update_or_create(vendor=vendor, question=ciq,
                                                                        defaults={'answer': ci.get('answer')})

        # Get data from validated data
        sc = validated_data.pop('s')
        cat = validated_data.pop('category')
        pc = validated_data.pop('pc')
        self_score = validated_data.pop('self_score')
        self_description = validated_data.pop('self_description')
        sm_score = validated_data.pop('sm_score')
        analyst_notes = validated_data.pop('analyst_notes')
        attachment = validated_data.pop('attachment')

        parent_category = ParentCategories.objects.filter(parent_category_name=pc)
        if parent_category:
            category, _ = Categories.objects.get_or_create(category_name=cat, pc=parent_category.first())
        else:
            raise serializers.ValidationError({"general_errors": ["Parent categories are not exist"]})
        subcategory, _ = Subcategories.objects.get_or_create(subcategory_name=sc, c=category)

        rfi_part_status, _ = RfiParticipationStatus.objects.update_or_create(vendor=vendor, rfi=round,
                                                                             pc=parent_category.first(),
                                                                             defaults={'last_analyst_response': current_scoring_round,
                                                                                       'last_vendor_response': current_scoring_round}
                                                                             )

        element, _ = Elements.objects.get_or_create(**validated_data, s=subcategory)

        if analyst_id:
            analyst_notes, _ = AnalystNotes.objects.get_or_create(vendor=vendor, e=element, analyst_notes=analyst_notes,
                                                                  rfi=round, analyst_response=current_scoring_round)

            sm_scores, _ = SmScores.objects.get_or_create(vendor=vendor, e=element, sm_score=sm_score, rfi=round,
                                                          analyst_response=current_scoring_round)

            rfi_part_status, _ = RfiParticipationStatus.objects.update_or_create(vendor=vendor, rfi=round,
                                                                                 pc=parent_category.first(),
                                                                                 defaults={
                                                                                     'last_analyst_response': current_scoring_round}
                                                                                 )

        else:
            self_score, _ = SelfScores.objects.get_or_create(vendor=vendor, e=element, self_score=self_score, rfi=round,
                                                             vendor_response=current_scoring_round)

            self_description, _ = SelfDescriptions.objects.get_or_create(vendor=vendor, e=element,
                                                                         self_description=self_description, rfi=round,
                                                                         vendor_response=current_scoring_round)

            attachment, _ = Attachments.objects.get_or_create(vendor=vendor, path=attachment, rfi=round)

            element_attachment, _ = ElementsAttachments.objects.get_or_create(e=element, attachment=attachment,
                                                                              rfi=round,
                                                                              vendor_response=current_scoring_round)

            rfi_part_status, _ = RfiParticipationStatus.objects.update_or_create(vendor=vendor, rfi=round,
                                                                                 pc=parent_category.first(),
                                                                                 defaults={
                                                                                     'last_vendor_response': current_scoring_round}
                                                                                 )

        return self


Пример данных
"Parent Category": "SERVICES ",
        "Category": [
            {
                "S2P Services": [
                    {
                        "General": [
                            {
                                "Element Name": "Breadth of Implementation Services",
                                "Description": "Please describe what is included within your basic/foundational implementation services (e.g., implementation planning, project management, configuration, testing, training, etc.) for your solution(s) and what makes it most successful?  Also, to what extent are you involved in broader business services (e.g., process redesign, benchmarking, best practices, change management, etc.) and technical services (e.g., systems integration, custom development, data/analytics) versus when you tend to work with your 3rd party professional services partners?",
                                "Scoring Scale": "0. We've not yet really offered these services. Not a current priority.\n1. Partial support.  We do some of this on a case-by-case basis, but haven't been pushed by customers to expand beyond this.\n2. Moderate support.  We provide decent support for these services as part of a formal service offering/capability.\n3. Strong support.  We definitely deliver these services robustly and continually because they're critical to our overall value proposition.\n4. Differentiated capability.  We have a material advantage over our peers because of our unique strengths to deliver these high impact services.\n5. World class differentiated capabilities. These are so good that we could spin out as a separate, profitable, business if we wanted to do so.  We win deals just because of this single services capability.",
                                "Self-Score": null,
                                "Self-Description": null,
                                "Attachments/Supporting Docs and Location/Link": null,
                                "SM score": null,
                                "Analyst notes": null
                            },
                            {
                                "Element Name": "Depth of Services Capabilities",
                                "Description": "Describe the depth of your own professional services teams and your partner professional services firms to assist in: operational/IT strategy, implementation planning, and implementation execution (process redesign, system tailoring/customization, testing, training, post implementation support, etc.) Approximately how many internal FTEs are on your team in this overall area? Similarly, approximately how many partner FTEs are certified in total (if a certification program exists) and trained (outside of a formal certification program)?",
                                "Scoring Scale": "0. We've not yet really offered these services. Not a current priority.\n1. Partial support.  We do some of this on a case-by-case basis, but haven't been pushed by customers to expand beyond this.\n2. Moderate support.  We provide decent support for these services as part of a formal service offering/capability.\n3. Strong support.  We definitely deliver these services robustly and continually because they're critical to our overall value proposition.\n4. Differentiated capability.  We have a material advantage over our peers because of our unique strengths to deliver these high impact services.\n5. World class differentiated capabilities. These are so good that we could spin out as a separate, profitable, business if we wanted to do so.  We win deals just because of this single services capability.",
                                "Self-Score": null,
                                "Self-Description": null,
                                "Attachments/Supporting Docs and Location/Link": null,
                                "SM score": null,
                                "Analyst notes": null
                            },
                          .....
                         и сотни подобных блоков


Может есть возможность отрефакторить код для снижения нагрузки? Любые рекомендации .
  • Вопрос задан
  • 75 просмотров
Пригласить эксперта
Ответы на вопрос 1
Sobolev5
@Sobolev5
Python developer
на серваке больше минуты и в итоге отваливается с ошибкой


Вы можете привести код ошибки? И второй вопрос, как вы вызываете данную операцию? К примеру нажатие на кнопку в UI, скрипт в кроне?
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы