Otimização do desempenho para mssql-django

Este artigo fornece orientações sobre como otimizar o desempenho das aplicações Django ao utilizar o mssql-django backend com o SQL Server.

Otimização de ligações

Reduza a sobrecarga de conexão ajustando o agrupamento, a persistência e as definições de tempo limite.

Permitir o agrupamento de ligações

O agrupamento de conexões está ativado por predefinição. Confirma se não está desativado no teu settings.py:

# Keep this True (or omit it entirely) for best connection performance
DATABASE_CONNECTION_POOLING = True

Usar CONN_MAX_AGE

Definido CONN_MAX_AGE para manter as ligações à base de dados abertas entre pedidos, evitando a sobrecarga de estabelecer uma nova ligação para cada pedido:

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "<your-database>",
        "USER": "<your-username>",
        "PASSWORD": "<your-password>",
        "HOST": "<your-server>",
        "PORT": "1433",
        "CONN_MAX_AGE": 600,  # Keep connections open for 10 minutes
        "OPTIONS": {
            "driver": "ODBC Driver 18 for SQL Server",
        },
    },
}

Definir o tempo limite da consulta

Evite que consultas de longa duração consumam recursos indefinidamente:

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "<your-database>",
        "USER": "<your-username>",
        "PASSWORD": "<your-password>",
        "HOST": "<your-server>",
        "PORT": "1433",
        "OPTIONS": {
            "driver": "ODBC Driver 18 for SQL Server",
            "query_timeout": 30,
        },
    },
}

Otimização de consultas

Reduza as idas e vindas da base de dados e o número de consultas com estas técnicas ORM.

Evite padrões de consulta N+1

Utilize select_related para relações de chave estrangeira (uma única consulta JOIN) e prefetch_related para relações muitos-para-muitos ou relações inversas (consulta separada com cláusula IN):

# Bad: N+1 queries
orders = Order.objects.all()
for order in orders:
    print(order.customer.name)  # Each access triggers a query

# Good: Single JOIN query
orders = Order.objects.select_related("customer").all()
for order in orders:
    print(order.customer.name)  # No additional queries

# Good: Two queries instead of N+1
orders = Order.objects.prefetch_related("items").all()
for order in orders:
    for item in order.items.all():  # Uses prefetched data
        print(item.name)

Usar apenas() e adiar()

Limite as colunas recuperadas quando não precisa de todos os campos:

# Retrieve only specific fields
products = Product.objects.only("name", "price").all()

# Defer loading of large fields
products = Product.objects.defer("description", "metadata").all()

Use valores() e values_list()

Quando não precisar de instâncias de modelo, use values() ou values_list() para consultas mais leves:

# Returns dictionaries instead of model instances
prices = Product.objects.values("name", "price")

# Returns tuples
names = Product.objects.values_list("name", flat=True)

Trabalho dentro do limite de 2.100 parâmetros

O SQL Server limita cada consulta a 2.100 parâmetros. O Django gera consultas parametrizadas, pelo que operações que produzem cláusulas grandes IN ou listas de valores em massa podem atingir esse limite.

Otimização automática para grandes cláusulas IN:

Quando uma filter(field__in=list) chamada tem mais de 2.048 valores, o mssql-django backend insere automaticamente os valores numa tabela temporária (em lotes de 1.000) e reescreve a consulta como WHERE field IN (SELECT params FROM #Temp_params). Esta otimização evita o limite de parâmetros sem quaisquer alterações no código. Aplica-se a todas as __in consultas, incluindo aquelas geradas por prefetch_related(). O limiar de 2.048 é definido pelo backend max_in_list_size() para se manter em segurança abaixo do limite de 2.100 parâmetros do SQL Server.

Esta reescrita tem um custo: criar e preencher #Temp_params adiciona idas e voltas adicionais e atividade na tempdb. Para listas próximas do limiar, compara ambas as abordagens na tua carga de trabalho.

Quando ainda é necessária intervenção manual:

A otimização automática da tabela temporária trata de pesquisas __in, mas estas operações podem ainda atingir o limite de 2.100 parâmetros, porque cada valor de um campo é um parâmetro separado:

• bulk_create() ou bulk_update() com muitos objetos e muitos campos
• Expressões complexas Q() com muitas condições encadeadas
• Casos em que queres evitar as viagens de ida e volta necessárias para preencher #Temp_params (por exemplo, quando uma lista mais pequena e uma normal IN (...) seriam mais rápidas)

Soluções:

1. Utilização batch_size em operações em massa para manter cada lote abaixo do limite:

   # Backend cap with 10 fields: min(1000, 2050 // 10 // 2) = 102 rows per batch
   # The backend applies the conservative // 2 divisor for both bulk_create and bulk_update.
   Product.objects.bulk_create(products, batch_size=100)
   

2. Divida consultas de grande dimensão IN em blocos quando pretender contornar o mecanismo automático da tabela temporária:

   from itertools import islice
   
   def chunked_filter(queryset, field, values, chunk_size=2000):
       """Filter a queryset in chunks to stay within the 2,100 parameter limit."""
       results = []
       it = iter(values)
       while chunk := list(islice(it, chunk_size)):
           results.extend(queryset.filter(**{f"{field}__in": chunk}))
       return results
   
   # Returns a list of model instances, not a QuerySet
   products = chunked_filter(Product.objects, "pk", large_id_list)
   

3. Utilize subconsultas em vez de materializar listas de IDs:

   # Instead of: Order.objects.filter(product_id__in=list(Product.objects.values_list("id", flat=True)))
   # Use a subquery (Django generates a single SQL statement with no parameter explosion)
   Order.objects.filter(product__in=Product.objects.filter(active=True))
   

4. Utilize Prefetch com querysets filtrados para limitar o número de IDs passados para prefetch_related():

   from django.db.models import Prefetch
   
   orders = Order.objects.prefetch_related(
       Prefetch("items", queryset=OrderItem.objects.select_related("product"))
   )[:500]  # Limit parent queryset size
   

Operações em massa

Utilize operações em massa para reduzir o número de idas e voltas à base de dados:

from decimal import Decimal

from myapp.models import Product

# Bulk create
new_products = [Product(name=f"Item {i}", price=Decimal("1.99") * i) for i in range(1000)]
Product.objects.bulk_create(new_products, batch_size=500)

# Bulk update: refetch so each instance has a primary key
products = list(Product.objects.filter(name__startswith="Item "))
for product in products:
    product.price *= Decimal("1.10")
Product.objects.bulk_update(products, ["price"], batch_size=500)

Para mais informações sobre os parâmetros return_rows_bulk_insert e default, consulte Operações em massa com mssql-django.

Estratégias de índice

Django cria automaticamente índices para ForeignKey, OneToOneField, e campos com db_index=True. Para índices adicionais, use Meta.indexes:

from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=100, db_index=True)
    category = models.CharField(max_length=50)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        indexes = [
            models.Index(fields=["category", "price"]),
            models.Index(fields=["-created_at"]),
        ]

Para índices específicos do SQL Server (como índices com INCLUDE colunas), use SQL bruto nas migrações:

from django.db import migrations

class Migration(migrations.Migration):
    dependencies = [("myapp", "0001_initial")]
    operations = [
        migrations.RunSQL(
            sql="CREATE INDEX IX_product_category ON myapp_product (category) INCLUDE (name, price);",
            reverse_sql="DROP INDEX IX_product_category ON myapp_product;",
        ),
    ]

O mssql-django backend suporta índices de cobertura (supports_covering_indexes = True em mssql/features.py). Em todas as versões do Django suportadas por mssql-django (3.2 e posteriores), podes usar o include parâmetro em models.Index vez do SQL bruto:

class Product(models.Model):
    name = models.CharField(max_length=100)
    category = models.CharField(max_length=50)
    price = models.DecimalField(max_digits=10, decimal_places=2)

    class Meta:
        indexes = [
            models.Index(fields=["category"], include=["name", "price"], name="ix_product_cat_cover"),
        ]

Colocação de grupos de ficheiros

O backend mssql-django mapeia o db_tablespace do Django para a cláusula ON filegroup do SQL Server. Use isto para colocar tabelas ou índices em grupos de ficheiros específicos:

class LargeAuditLog(models.Model):
    timestamp = models.DateTimeField(auto_now_add=True)
    message = models.TextField()

    class Meta:
        db_tablespace = "ARCHIVE_FG"

Isto gera: CREATE TABLE ... ON [ARCHIVE_FG].

Funções do Windows

O backend suporta as funções de janela do SQL Server (supports_over_clause = True). Utilize as expressões Window do Django para classificação, totais acumulados e cálculos particionados:

from django.db.models import F, Window
from django.db.models.functions import Rank, RowNumber

# Rank products by price within each category
products = Product.objects.annotate(
    price_rank=Window(
        expression=Rank(),
        partition_by=F("category"),
        order_by=F("price").desc(),
    )
)

# Row numbers across the full result set
products = Product.objects.annotate(
    row_num=Window(
        expression=RowNumber(),
        order_by=F("created_at").asc(),
    )
)

Monitorar o desempenho da consulta

Use o registo de consultas integrado do Django para identificar consultas lentas durante o desenvolvimento:

LOGGING = {
    "version": 1,
    "handlers": {
        "console": {
            "class": "logging.StreamHandler",
        },
    },
    "loggers": {
        "django.db.backends": {
            "level": "DEBUG",
            "handlers": ["console"],
        },
    },
}

Para cargas de trabalho de staging e produção, utilize ferramentas de desempenho do SQL Server para analisar o SQL que o Django gera:

1. Comece pelos relatórios de desempenho integrados antes de consultar diretamente as DMVs.

   • Para SQL Server e Azure SQL Managed Instance, utilize Performance Dashboard no SQL Server Management Studio.
   • Para Base de Dados SQL do Azure, utilize Query Performance Insight for Base de Dados SQL do Azure.

   Estes relatórios são geralmente a forma mais rápida de encontrar consultas caras, esperas, bloqueios e pressão de recursos, com menos margem para erros do que consultas ad hoc no DMV.

2. Use a Query Store para identificar as consultas que consomem mais recursos e as que recentemente regrediram.

3. Use as vistas de Consultas que Consomem Mais Recursos, Consultas Regressadas e Estatísticas de Espera de Consultas no SQL Server Management Studio para determinar se o gargalo é a CPU, E/S, memória ou esperas. Para orientação, consulte Melhores Práticas para monitorizar cargas de trabalho com a Query Store.

4. Abra um plano de execução real para a instrução lenta, de modo a identificar operações de análise, pesquisas de chave dispendiosas, estimativas de linhas imprecisas e índices em falta.

5. Se uma consulta se tornou mais lenta após uma implementação ou alteração de esquema, compare os seus planos na Query Store antes de alterar o código da aplicação. Um DBA pode forçar temporariamente um plano já validado enquanto corrige o problema subjacente relacionado com o índice, as estatísticas ou a estrutura da consulta.

Se a Query Store mostrar esperas em vez de tempo de CPU elevado, use Identificar gargalos para distinguir problemas de CPU, memória, E/S de disco, sobrecarga de ligações e bloqueios.

Conteúdo relacionado

• Operações em massa com mssql-django
• Agregação de ligações no mssql-django
• Lógica de repetição e resiliência da conexão com mssql-django
• Referência de configuração MSSQL-Django
• Painel de Desempenho
• Query Performance Insight para a Base de Dados SQL do Azure
• Monitorize o desempenho usando o Query Store
• Analise um plano de execução real
• Otimização de bases de dados Django