How can i make my python django view code logic very efficient or fast or less mysql query execution..?

debug toolbar sql queries img> I'm working on a Django view that handles a lot of data and performs several operations, including filtering, sorting, aggregating, and pagination. However, the current implementation is running a lot of unnecessary queries, making it inefficient, slow, and time-consuming.

What I Need Help With: I want to optimize this view logic to make it more efficient, ensuring that no unnecessary queries are executed, and the code runs faster. Ideally, I would like to reduce the number of queries being executed and avoid redundant code execution. How can I refactor this code to achieve these goals?

#my views-

 def LendersPeerCompare(request):
    lender_filter = LenderCompareFilt(request.GET)
 
    if request.method == 'GET':
         # Get selected lenders
         Lender1names = request.GET.getlist('Lender1')
         Lender2names = request.GET.getlist('Lender2')

        # Get sorting parameters
        sort_date1 = request.GET.get('sort_date1', 'none')
        sort_date2 = request.GET.get('sort_date2', 'none')

        # Fetch data for selected lenders with sorting
        Lender1_data = lender_filter.qs.filter(charge_holder_name__in=Lender1names)
        Lender2_data = lender_filter.qs.filter(charge_holder_name__in=Lender2names)

        # Apply sorting based on parameters
        if sort_date1 == 'asc':
            Lender1_data = Lender1_data.order_by('date_of_creation')
        elif sort_date1 == 'desc':
            Lender1_data = Lender1_data.order_by('-date_of_creation')

        if sort_date2 == 'asc':
            Lender2_data = Lender2_data.order_by('date_of_creation')
        elif sort_date2 == 'desc':
            Lender2_data = Lender2_data.order_by('-date_of_creation')

        # Determine which comparison to perform
        if request.GET.get('compare_xlsx') == 'true':
            # Calculate total sum of amount for all data
            Lender1_total_amount = Lender1_data.aggregate(total_amount=Sum('amount'))['total_amount']
            Lender2_total_amount = Lender2_data.aggregate(total_amount=Sum('amount'))['total_amount']
        else:
            # Compare the data and find unique entries
            common_cin = Lender1_data.values_list('cin__company_cin', flat=True).intersection(Lender2_data.values_list('cin__company_cin', flat=True))
            unique_Lender1_data = Lender1_data.exclude(cin__company_cin__in=common_cin).order_by('-date_of_creation')
            unique_Lender2_data = Lender2_data.exclude(cin__company_cin__in=common_cin).order_by('-date_of_creation')

            # Update Lender1_data and Lender2_data with the unique data
            Lender1_data = unique_Lender1_data
            Lender2_data = unique_Lender2_data

            # Calculate total sum of amount for each unique data
            Lender1_total_amount = Lender1_data.aggregate(total_amount=Sum('amount'))['total_amount']
            Lender2_total_amount = Lender2_data.aggregate(total_amount=Sum('amount'))['total_amount']

        # Pagination
        page_number = request.GET.get('page')
        paginator1 = Paginator(Lender1_data, 20)

        try:
            Lender1_data = paginator1.page(page_number)
        except PageNotAnInteger:
            Lender1_data = paginator1.page(1)
        except EmptyPage:
            Lender1_data = paginator1.page(paginator1.num_pages)

        paginator2 = Paginator(Lender2_data, 20)

        try:
            Lender2_data = paginator2.page(page_number)
        except PageNotAnInteger:
            Lender2_data = paginator2.page(1)
        except EmptyPage:
            Lender2_data = paginator2.page(paginator2.num_pages)

        # Check for company and director status
        for lender in Lender1_data:
            lender.director_status = "No records found ❌"  # Default value
            try:
                if lender.cin:
                    company = RocCompany.objects.get(company_cin=lender.cin.company_cin)
                    directors = CompanyDir.objects.filter(companycin=company.company_cin)
                    lender.director_status = "Dir. records found ✅" if directors.exists() else "No records found ❌"
            except RocCompany.DoesNotExist:
                lender.director_status = "No records found ❌"  # Handle missing company gracefully

        for lender in Lender2_data:
            lender.director_status = "No records found ❌"  # Default value
            try:
                if lender.cin:
                    company = RocCompany.objects.get(company_cin=lender.cin.company_cin)
                    directors = CompanyDir.objects.filter(companycin=company.company_cin)
                    lender.director_status = "Dir. records found ✅" if directors.exists() else "No records found ❌"
            except RocCompany.DoesNotExist:
                lender.director_status = "No records found ❌"  # Handle missing company gracefully

        context = {
            'lender_filter': lender_filter,
            'Lender1_name': Lender1names,
            'Lender2_name': Lender2names,
            'Lender1_data': Lender1_data,
            'Lender2_data': Lender2_data,
            'Lender1_total_amount': Lender1_total_amount,
            'Lender2_total_amount': Lender2_total_amount,
            'sort_date1': sort_date1,
            'sort_date2': sort_date2,
        }
        return render(request, 'central/Lenderscompare.html', context)
     else:
         context = {'lender_filter': lender_filter}
         return render(request, 'central/Lenderscompare.html', context)

my models-

class RocCompany(models.Model):
    company_name = models.CharField(db_column='CompanyName', blank=True, null=True, max_length=100, db_index=True)
    company_cin = models.CharField(db_column='CIN', primary_key=True, max_length=50)
    incorporation = models.DateField(db_column='Incorporation', blank=True, null=True)
    state = models.CharField(max_length=50, db_column='State', blank=True, null=True, db_index=True)
    city = models.CharField(max_length=50, db_column='City', blank=True, null=True, db_index=True)
    roc_name = models.CharField(max_length=100, blank=True, null=True)
    email = models.CharField(max_length=100, blank=True, null=True)
    registered_address = models.TextField(blank=True, null=True)
    listed = models.CharField(max_length=50, blank=True, null=True)
    category = models.CharField(max_length=50, blank=True, null=True)
    subcategory = models.CharField(max_length=50, blank=True, null=True)
    class_field = models.CharField(db_column='class', max_length=50, blank=True, null=True)
    active_compliance = models.CharField(max_length=50, blank=True, null=True)
    paidup_capital = models.BigIntegerField(blank=True, null=True)
    date_balance_sheet = models.DateField(blank=True, null=True)
    company_status = models.CharField(max_length=50, blank=True, null=True)
    activity_code = models.ForeignKey(Activity, models.DO_NOTHING, db_column='activity_code', blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'cin_format'
        indexes = [
            models.Index(fields=['company_name', 'state', 'city',]),
        ]

    def __str__(self):
        return self.company_cin

class RocCharge(models.Model):
    srn_no = models.CharField(max_length=50, blank=True, null=True)
    charge_id = models.IntegerField(blank=True, null=True)
    charge_holder_name = models.CharField(max_length=100, blank=True, null=True, db_index=True)
    date_of_creation = models.DateField(default=date.today)
    date_of_modification = models.TextField(blank=True, null=True)
    date_of_satisfaction = models.TextField(blank=True, null=True)
    amount = models.BigIntegerField(blank=True, null=True)
    address = models.TextField(blank=True, null=True)
    cin = models.ForeignKey(RocCompany, on_delete=models.CASCADE, db_column='CIN', blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'roc_web'
        indexes = [
            models.Index(fields=['charge_holder_name', 'date_of_creation', 'amount']),
        ]

    def __str__(self):
        return self.charge_holder_name

class CompanyDir(models.Model):
    dir_id = models.AutoField(primary_key=True)
    companycin = models.CharField(db_column='CompanyCIN', max_length=50, blank=True, null=True, db_index=True)
    companyname = models.CharField(db_column='CompanyName', max_length=100, blank=True, null=True, db_index=True)
    directordin = models.IntegerField(db_column='DirectorDIN', blank=True, null=True, db_index=True)
    directorname = models.CharField(db_column='DirectorName', max_length=100, blank=True, null=True)
    designation = models.CharField(db_column='Designation', max_length=50, blank=True, null=True)
    directoremail = models.CharField(db_column='DirectorEmail', max_length=50, blank=True, null=True)
    directorcontact = models.CharField(db_column='DirectorContact', max_length=20, blank=True, null=True)
    source = models.CharField(max_length=2, blank=True, null=True)
    linkedIn_url = models.CharField(max_length=200, blank=True, null=True)
    gender = models.CharField(db_column='Gender', max_length=10, blank=True, null=True)
    dob = models.DateField(db_column='dob', blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'company_dir'
        indexes = [
            models.Index(fields=['companycin', 'companyname', 'directordin', 'dir_id']),
        ]

    def __str__(self):
        return self.companyname    

Back to Top