How to optimize database queries?

Question

Django

I send a request to the database to read / write data parsed from an excel file. The data is from 4 to 10 thousand lines of json format. Faced with the fact that the request takes a very long time. On a local computer (quite powerful) it takes up to 20 seconds, and on a server more than a minute and eventually falls off with an error. It seems to me that there is not so much data that the request would take so long.

The serializer's create method looks like this

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

Sample Data

"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
                            },
                          .....
                         и сотни подобных блоков

Maybe there is an opportunity to refactor the code to reduce the load? Any recommendation.

Answer 1

on the server for more than a minute and eventually falls off with an error

Can you provide an error code? And the second question, how do you call this operation? For example, clicking on a button in the UI, a script in the crown?