Ajuste de desempenho para mssql-django

Este artigo fornece diretrizes sobre como otimizar o desempenho do aplicativo Django ao usar o mssql-django back-end com SQL Server.

Otimização de conexão

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

Habilitar o pool de conexões

O pool de conexões é habilitado por padrão. Verifique se ele não está desabilitado em seu settings.py:

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

Usar CONN_MAX_AGE

Defina CONN_MAX_AGE para manter as conexões de banco de dados abertas entre solicitações, evitando a sobrecarga de estabelecer uma nova conexão para cada solicitação:

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

Impedir 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 a contagem de consultas e viagens de ida e volta do banco de dados com essas técnicas de ORM.

Evitar padrões de consulta N+1

Use select_related para relações de chave estrangeira (uma única consulta JOIN) e prefetch_related para relações muitos-para-muitos ou reversas (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)

Use only() e defer()

Limite as colunas recuperadas quando você não precisar 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()

Usar valores() e values_list()

Quando você 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)

Trabalhar dentro do limite de 2.100 parâmetros

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

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

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

Essa reescrita tem um custo: criar e preencher #Temp_params adiciona round-trips adicionais e atividade no tempdb. Para listas próximas ao limite, faça o benchmark de ambas as abordagens em sua carga de trabalho.

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

A otimização automática de tabela temporária lida com consultas __in, mas essas operações ainda podem atingir o limite de 2.100 parâmetros porque cada valor de 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 você deseja evitar as viagens de ida e volta necessárias para preencher #Temp_params (por exemplo, quando uma lista menor e um normal IN (...) seriam mais rápidos)

Soluções:

1. Usar 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 grandes IN em partes quando quiser contornar o mecanismo automático de 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. Use 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. Use Prefetch com conjuntos de consultas filtrados para limitar o número de IDs passadas 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

Use operações em massa para reduzir o número de viagens de ida e volta do banco 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 obter 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

O Django cria índices automaticamente para ForeignKey, OneToOneFielde 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 de SQL Server (como índices com INCLUDE colunas), use SQL bruto em 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 oferece suporte a í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), você pode usar o parâmetro include em models.Index em vez de SQL puro:

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"),
        ]

Posicionamento do grupo de arquivos

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

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

    class Meta:
        db_tablespace = "ARCHIVE_FG"

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

Funções da janela

O back-end dá suporte às funções de janela do SQL Server (supports_over_clause = True). Use 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 log de consulta interno 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 homologação e produção, use as ferramentas de desempenho do SQL Server para analisar o SQL gerado pelo Django:

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

   • Para SQL Server e Instância Gerenciada de SQL do Azure, use o Painel de Desempenho no SQL Server Management Studio.
   • Para Banco de Dados SQL do Azure, utilize Query Performance Insight for Banco de Dados SQL do Azure.

   Esses relatórios geralmente são a maneira mais rápida de encontrar consultas caras, esperas, bloqueio e pressão de recursos com menos espaço para erros do que consultas DMV ad hoc.

2. Use o Repositório de Consultas para identificar as consultas que mais consomem recursos e as consultas que tiveram regressão recentemente.

3. Use os modos de exibição Consultas que Mais Consomem Recursos, Consultas com Regressão e Estatísticas de Espera da Consulta no SQL Server Management Studio para determinar se o gargalo é de CPU, E/S, memória ou esperas. Para obter diretrizes, consulte As práticas recomendadas para monitorar cargas de trabalho com Repositório de Consultas.

4. Abra um plano de execução real da instrução lenta para procurar varreduras, buscas de chave dispendiosas, estimativas de linhas imprecisas e índices ausentes.

5. Se uma consulta ficou mais lenta após uma implantação ou alteração de esquema, compare seus planos em Repositório de Consultas antes de alterar o código do aplicativo. Um DBA pode forçar temporariamente um plano conhecido e confiável enquanto você corrige o problema subjacente no índice, nas estatísticas ou na estrutura da consulta.

Se o Repositório de Consultas mostrar esperas em vez de alto tempo de CPU, use Identificar gargalos para distinguir problemas de CPU, memória, E/S de disco, sobrecarga de conexões e bloqueio.

Conteúdo relacionado

• Operações em massa com mssql-django
• Pool de conexõ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
• Análise de desempenho de consulta para o Banco de Dados SQL do Azure
• Monitorar o desempenho usando o Repositório de Consultas
• Analisar um plano de execução real
• Otimização de banco de dados do Django