Comment exporter vers Excel a presque tué notre système

Il y a quelques semaines, nous avons commencé à recevoir des plaintes d'utilisateurs concernant le temps de réponse lent de l'un de nos systèmes. Un rapide coup d'œil aux métriques du serveur a montré une utilisation du processeur supérieure à la normale. Ce système est principalement gourmand en E/S, donc une utilisation élevée du processeur n'est pas quelque chose que nous rencontrons régulièrement.

La première chose que nous avons faite a été d'identifier le processus de travail qui consomme beaucoup de CPU en utilisant htop . Après avoir obtenu l'identifiant de processus (PID) du processus, nous avons utilisé py-spy pour avoir un aperçu de ce qu'il fait :

$ py-spy -p 8187 -r 1000

Cette commande échantillonne le processus 1000 fois par seconde et fournit un top -like view of the results :

Total Samples 17974
GIL: 0.00%, Active: 0.00%, Threads: 1

OwnTime TotalTime Function (filename:line)
0.000s 173.7s get_response (django/core/handlers/base.py:75)
0.000s 173.7s inner (django/core/handlers/exception.py:34)
0.000s 173.7s __call__ (django/utils/deprecation.py:94)
0.000s 173.7s __call__ (django/core/handlers/wsgi.py:141)
0.000s 173.6s view (django/views/generic/base.py:71)
0.000s 173.6s _get_response (django/core/handlers/base.py:113)
0.000s 173.6s dispatch (django/contrib/auth/mixins.py:52)
0.000s 173.6s dispatch (django/contrib/auth/mixins.py:109)
0.000s 173.6s dispatch (django/views/generic/base.py:97)
0.050s 173.6s get (dashboard/views/list_views.py:100)
0.000s 94.69s get_resource_to_export (dashboard/views/list_views.py:70)
0.000s 94.69s export (dashboard/views/list_views.py:73)
0.000s 94.68s export (dashboard/resources.py:215)
0.000s 83.81s __iter__ (django/db/models/query.py:274)
0.040s 82.73s _fetch_all (django/db/models/query.py:1242)
0.000s 78.84s export (dashboard/views/list_views.py:74)
0.000s 70.58s __iter__ (django/db/models/query.py:55)
0.000s 68.98s execute_sql (django/db/models/sql/compiler.py:1100)
68.81s 68.81s _execute (django/db/backends/utils.py:84)
0.000s 68.81s _execute_with_wrappers (django/db/backends/utils.py:76)
0.000s 68.81s execute (django/db/backends/utils.py:67)
0.000s 50.11s save (tablib/packages/openpyxl3/workbook.py:186)
0.000s 50.11s export_set (tablib/formats/_xlsx.py:46)
0.000s 46.41s save (tablib/packages/openpyxl3/writer/excel.py:124)
0.000s 46.41s save_workbook (tablib/packages/openpyxl3/writer/excel.py:141)
0.000s 42.40s _fetch_all (django/db/models/query.py:1244)
0.000s 42.40s _prefetch_related_objects (django/db/models/query.py:771)
0.000s 42.38s prefetch_related_objects (django/db/models/query.py:1625)
0.000s 41.94s prefetch_one_level (django/db/models/query.py:1738)
0.000s 41.25s get_prefetch_queryset (django/db/models/fields/related_descriptors.py:627)
0.000s 32.30s _write_worksheets (tablib/packages/openpyxl3/writer/excel.py:91)

Après avoir surveillé cette vue pendant une minute ou deux, nous avons eu quelques informations :

1. Beaucoup de temps est consacré à la récupération des données.
2. Beaucoup de temps est consacré à certains appeler le prefetch_related .
3. Le problème se situe dans le tableau de bord, et plus précisément dans la vue qui exporte les données.

Avec ces informations, nous voulions passer à l'identification de la vue exacte. Nous nous sommes ensuite tournés vers le journal d'accès nginx :

$ journalctl -u nginx -r | grep dashboard

Nous avons réussi à identifier plusieurs endpoints qui prenaient beaucoup de temps à s'exécuter. Certains d'entre eux ont terminé en un peu moins de 60 secondes, d'autres ont été tués par PostgreSQL après avoir atteint le statement_timeout limite et a renvoyé un code d'état 500.

À ce stade, nous avions une assez bonne idée du problème, mais nous ne savions toujours pas pourquoi. L'étape suivante consistait à inspecter le code problématique et à essayer de le reproduire.

Exporter un QuerySet vers Excel

Le système est utilisé pour signaler et suivre les infractions dans les transports publics. Lors d'une inspection, l'inspecteur documente différents types d'infractions telles qu'un bus sale, un bus en retard, etc. Les modèles de ce système ressemblent à peu près à ceci :

class ViolationType(models.Model):
 name = model.TextField()


class Inspection(models.Model):
 id = models.AutoField(primary_key=True)


class Violation(models.Model):
 inspection = models.ForeignKey(Inspection, on_delete=models.PROTECT)
 violation_type = models.ForeignKey(ViolationType, on_delete=models.PROTECT)
 comments = models.TextField()

De temps à autre, un utilisateur du back-office téléchargeait les informations d'inspection dans Excel pour une analyse plus approfondie.

Le rapport contient de nombreuses informations sur l'inspection, mais surtout, il inclut une liste des types de violation pour chaque inspection :

inspection, violations
1, dirty floors | full trash can
2, full trash can | no light | missing signs

Utilisation django-import-export

Pour produire le rapport Excel, nous utilisons un package appelé django-import-export . En utilisant le package, nous définissons un ModelResource qui peut produire un fichier Excel à partir d'un jeu de requête :

from import_export import resources, fields, widgets

from .models import Inspection, Violation


class InspectionResource(resources.ModelResource):
 violations = fields.Field(
 widget=widgets.ManyToManyWidget(Violation, field='violation_type')
 )

 class Meta:
 model = Inspection
 fields = (
 'id',
 'violations',
 )

La requête produite par ce ModelResource provoque un problème de requêtes N + 1, donc avant de le déployer en production, nous l'avons corrigé et ajouté prefetch_related :

from django.db.models import Prefetch
from import_export import resources, fields, widgets

from .models import Inspection, Violation


class InspectionResource(resources.ModelResource):
 violations = fields.Field(
 widget=widgets.ManyToManyWidget(Violation, field='violation_type')
 )

 class Meta:
 model = Inspection
 fields = (
 'id',
 'violations',
 )

 def export(self, queryset=None):
 queryset = (
 queryset
 .prefetch_related(Prefetch(
 'violations',
 queryset=Violation.objects.select_related('violation_type'),
 to_attr='prefetched_violations',
 ))
 )
 return super().export(list(queryset))

 def dehydrate_violations(self, inspection: Inspection) -> str:
 return '\n'.join(
 v.violation_type.name
 for v in inspection.prefetched_violations
 )

Pour utiliser prefetch_related dans un ModelResource nous avons dû apporter les modifications suivantes :

1. Remplacer export et ajustez la requête pour préextraire les violations à l'aide de prefetch_related . Nous utilisons le Prefetch car nous devions personnaliser la requête de prélecture et ajouter le nom du type de violation à partir d'une table associée.

2. Évaluez la requête et demandez à la fonction d'exportation de renvoyer une liste au lieu d'un ensemble de requêtes. django-import-export utilise iterator pour accélérer la requête. Utilisation du iterator() , l'ORM utilise un curseur pour parcourir les données en morceaux et réduire la mémoire. Bien que cela soit généralement utile, Django est incapable d'utiliser iterator() avec prefetch_related .

3. Ajouter un dehydrate_ personnalisé fonction pour le champ des violations qui affichera une liste délimitée par des virgules des noms de type de violation.

Lié à la prélecture

C'est tout ce que vous devez savoir sur la prélecture dans Django

La ressource a été utilisée par la vue pour produire le rapport Excel :

from django.http import HttpRequest, HttpResponse
from .models import Inspection
from .resources import InspectionResource

LIMIT = 10000

def export_to_excel(request: HttpRequest) -> HttpResponse:
 inspections = Inspection.objects.all()
 # Apply some filter on the queryset based on request
 data = InspectionResource().export(inspections[:LIMIT]).xlsx
 response = HttpResponse(data, content_type='text/xlsx')
 response['Content-Disposition'] = 'attachment; filename=export.xlsx'
 return response

La vue prend une requête, applique un filtre sur les inspections et produit le fichier xlsx en utilisant le ModelResource .

Rechercher le meilleur format de fichier

Avant de pouvoir commencer à améliorer le processus d'exportation, nous devons établir une base de référence. Pour obtenir les horaires et identifier les points chauds dans la pile d'appels, nous avons utilisé cProfile . Pour identifier et chronométrer l'exécution de la requête, nous avons activé la journalisation SQL dans les paramètres de Django :

# settings.py

LOGGING = {
 # ...
 'loggers': {
 'django.db.backends': {
 'level': 'DEBUG',
 },
 # ...
 },
}

Le benchmark ressemblait à ceci :

import cProfile

from .models import Inspection
from .resources import InspectionResource

qs = VehicleInspection.objects.all()[:10000]
cProfile.run('resources.VehicleInspectionResource().export(qs).xlsx')

Ce sont les résultats de l'exportation de 10 000 lignes au format xlsx en utilisant prefetch_related :

56830808 function calls (47142920 primitive calls) in 41.574 seconds
select 5.009
prefetch 8.009

56660555 function calls (47149065 primitive calls) in 39.927 seconds
select 2.356
prefetch 7.991

Nous avons exécuté le benchmark deux fois pour nous assurer que les résultats n'étaient pas affectés par les caches. La fonction a pris 40 secondes, et seulement 10 secondes (25 %) ont été passées dans la base de données.

À ce stade, nous soupçonnions que le problème pouvait provenir du format de fichier . Cette hypothèse était étayée par l'utilisation élevée du processeur du serveur d'applications.

Ensuite, nous avons voulu essayer le même benchmark, seulement au lieu de xlsx nous avons produit un csv :

cProfile.run('resources.VehicleInspectionResource().export(qs).csv')

Ce sont les résultats de l'exportation de 10 000 lignes au format csv à l'aide de prefetch_related :

9179705 function calls (9107672 primitive calls) in 17.429 seconds
select 1.970
prefetch 8.343

Ouah! C'est une grande amélioration. Cela a confirmé notre suspicion que la production réelle du xlsx était le problème.

Avant de continuer, nous voulions vérifier un autre format de fichier qui pourrait être plus utile à nos utilisateurs, l'ancien format xls :

cProfile.run('resources.VehicleInspectionResource().export(qs).xls')

Ce sont les résultats de l'exportation de 10 000 lignes au format xls en utilisant prefetch_related :

16317592 function calls (15745704 primitive calls) in 20.694 seconds
select 1.922
prefetch 7.976

OK, donc c'est surprenant. Je ne connais pas les composants internes des formats de fichiers Microsoft Office, mais il semble que l'ancien format soit un peu plus lent que le format csv et beaucoup plus rapide que le nouveau format xlsx.

Ces résultats de référence ont soulevé un vieux dilemme. Dans le passé, nous avions l'habitude de ne servir les utilisateurs qu'avec des fichiers csv, mais ils se plaignaient beaucoup des problèmes d'ouverture des fichiers et des problèmes d'encodage et de formatage. Pour cette raison, nous avons décidé de produire xlsx en premier lieu, donc à cette époque, produire des fichiers xls semblait être la meilleure solution.

Je devrais déjà vous le dire, utiliser l'ancien format xls était une mauvaise décision, mais nous ne le savions pas encore.

Amélioration de la requête

Après avoir réduit de moitié le temps d'exécution global, nos prochaines cibles étaient les requêtes. Deux requêtes sont exécutées pour produire le jeu de données à exporter. Avant toute modification, il a fallu environ 2 s pour la requête "principale" et environ 8 s pour la prélecture.

La requête "principale" ressemblait à ceci :

SELECT
 "inspection"."id",
 -- around 50 more fields from joined tables
FROM
 "inspection"
 INNER JOIN "auth_user" ON ("inspection"."user_id" = "auth_user"."id")
 -- around 11 more joined tables

La ressource utilisait beaucoup de données provenant de tables associées, et la requête rejoignait environ 12 tables et comportait de nombreux champs répertoriés dans la clause SELECT. La table est l'une des tables principales de la base de données, elle est donc fortement indexée et les tables de recherche étaient relativement petites, de sorte que la requête n'a pas mis longtemps à se terminer.

La requête de prélecture ressemblait à ceci :

SELECT
 "violation"."id",
 "violation"."inspection_id",
 "violation"."violation_type_id",
 "violation_type"."id",
 "violation_type"."name",
FROM "violation"
 INNER JOIN "violation_type" ON (
 "violation"."violation_type_id" = "violation_type"."id"
 )
WHERE
 "violation"."inspection_id" = ANY([
 2814, 9330, 8848, 8971, 9372, 9084, 78, 3896, 2609, 5177, 2866
 -- another 10,000 inspection IDs
 1399, 9348, 914, 8884, 9082, 3356, 2896, 742, 9432, 8926, 9153
 ])

Cette requête semble innocente, mais en fait, il a fallu environ 8 secondes pour la terminer. Le plan d'exécution de cette requête ressemblait à ceci :

Nested Loop (cost=1000.28..2040346.39 rows=26741 width=181)
 -> Gather (cost=1000.00..2032378.29 rows=26741 width=115)
 Workers Planned: 2
 -> Parallel Seq Scan on violation (cost=0.00..2028704.19 rows=11142 width=115)
 Filter: (vehicle_inspection_id = ANY ('{2814,9330,....,8926,9153}'::integer[]))
 -> Index Scan using violationtype_pkey on violationtype (cost=0.28..0.30 rows=1 width=66)
 Index Cond: (id = violation.violation_type_id)

J'ai coupé le plan d'exécution pour plus de concision, mais le Filter la ligne était longue de trois ou quatre pages, remplie d'identifiants. Cela nous a fait réfléchir, est-il possible que cet énorme ANY filtre est ce qui nous cause des problèmes ?

Remplacer prefetch_related avec Subquery et OuterRef

Pour répondre à cette question, nous avons décidé d'essayer d'implémenter la requête sans prefetch_related . Au lieu de cela, nous avons décidé d'utiliser le nouveau Subquery expression.

Utilisation de Subquery la requête utilisant l'ORM ressemblait à ça :

from django.db.models import OuterRef, Subquery, Value
from django.contrib.postgres.aggregates import ArrayAgg

inspections = inspections.annotate(violations_csv=Subquery(
 Violation.objects
 # Reference the inspection ID of the outer table, inspection.
 .filter(inspection_id=OuterRef('id'))
 # Prevent Django from adding a group by column.
 .annotate(dummy=Value('1')).values('dummy')
 # Construct an array of violation names.
 .annotate(violations=ArrayAgg('violation_type__name', distinct=True))
 .values('violations')
))

Si vous n'avez jamais expérimenté Subquery il y a beaucoup à prendre ici. Avant de la décomposer, voici à quoi ressemble la requête :

SELECT
 "inspection"."id",
 (
 SELECT
 ARRAY_AGG(DISTINCT U2."name") AS "violations"
 FROM
 "violation" U0
 INNER JOIN "violationtype" U2 ON (U0."violation_type_id" = U2."id")
 WHERE
 U0."inspection_id" = ("inspection"."id")

 ) AS "violations_csv",
 -- around 50 more fields from joined tables
FROM
 "inspection"
 INNER JOIN "auth_user" ON ("inspection"."user_id" = "auth_user"."id")
 -- around 11 more joined tables

Maintenant, décomposons-le :

• Subquery est une expression de requête qui ne peut exister qu'à l'intérieur d'une autre requête. Dans ce cas, la requête externe est inspection .
• Subquery dans utilisé dans annotate ainsi le résultat de la sous-requête est stocké dans une autre colonne pour chaque ligne.
• Nous avons ajouté une annotation factice pour empêcher Django de regrouper les résultats. La sous-requête est exécutée pour chaque inspection, c'est ce que filtre le OuterRef Est-ce que. Pour cette raison, nous n'avons pas besoin de regrouper par une autre colonne.
• La sous-requête doit renvoyer au plus une ligne, nous regroupons donc les noms dans un tableau en utilisant ARRAY_AGG .

Après tout ce travail acharné, nous étions impatients de voir si c'était la solution miracle que nous attendions, mais en fait, lorsque nous avons exécuté cela sur 10 000 lignes, cela s'est étouffé. Pour y parvenir, nous avons exécuté la fonction d'exportation avec seulement 1 000 lignes.

Voici les résultats de l'exportation de 1 000 lignes au format xls à l'aide de la sous-requête :

1571053 function calls (1514505 primitive calls) in 60.962 seconds
select 59.917

La requête est maintenant follement lente. Je ne collerai pas le plan d'exécution car il y avait tellement d'autres tables, mais PostgreSQL a utilisé une jointure de boucle imbriquée au niveau supérieur de la requête pour produire la valeur de ce champ. Étonnamment, la base de données a fait un travail bien pire que l'ORM dans ce cas.

Utiliser un itérateur

Avant d'abandonner complètement cette solution, nous avons voulu vérifier une dernière chose. Nous avons mentionné précédemment que django-import-export utilise iterator() pour créer un curseur sur les résultats. Nous avons également mentionné que l'utilisation de prefetch_related nous empêche d'utiliser iterator() . Eh bien, nous n'utilisons plus prefetch_related donc autant vérifier si on utilise iterator() fait aucune différence.

Voici les résultats de l'exportation de 1 000 lignes au format xls à l'aide d'une sous-requête et d'un itérateur :

1571580 function calls (1514788 primitive calls) in 62.130 seconds
select 60.618

L'itérateur n'a fait aucune différence.

Simplifier la requête

Dans une dernière tentative pour tirer quelque chose de cette expédition, nous avons voulu voir si la complexité de la requête empêchait PostgreSQL de trouver un plan d'exécution optimal. Pour ce faire, nous aurions pu ajuster les paramètres de la base de données from_collapse_limit et join_collapse_limit et laisser PostgreSQL prendre tout le temps et les ressources dont il a besoin pour trouver un plan d'exécution optimal, mais à la place, nous avons décidé de supprimer tous les autres champs des ressources en plus de id et violations .

Voici les résultats de l'exportation de 1 000 lignes contenant uniquement les champs id et violations au format xls à l'aide d'une sous-requête et d'un itérateur :

6937 function calls (6350 primitive calls) in 57.280 seconds
select 57.255

Pas de changement, c'est officiellement une impasse !

Prélecture manuelle

Après une courte pause déjeuner, nous avons décidé qu'il était temps de sortir les gros canons. Si l'implémentation de préchargement de Django ne fonctionnait pas pour nous et que PostgreSQL n'était pas en mesure de produire un plan d'exécution décent, nous devions simplement le faire nous-mêmes.

Pour implémenter notre propre "prefetch", nous devions ajuster certaines des autres fonctions de la ressource :

from import_export import resources, fields

from .models import Inspection, Violation


class InspectionResource(resources.ModelResource):
 violations = fields.Field()

 class Meta:
 model = Inspection
 fields = (
 'id',
 'violations',
 )

 def export(self, queryset=None):
 # Manually prefetch the violations.
 self.prefetched_violations = dict(
 Violation.objects
 .filter(inspection_id__in=(
 queryset
 # Clean all joins.
 .select_related(None)
 .values_list('pk')
 )
 .annotate(
 violations_csv=ArrayAgg('violation_type__name'),
 )
 .values_list(
 'vehicle_inspection_id',
 'violations_csv',
 )
 )

 return super().export(queryset)

 def dehydrate_violations(self, inspection: Inspection) -> str:
 return '\n'.join(self.prefetched_violations.get(inspection.id, []))

Cela semble beaucoup, mais ce n'est en fait pas :

1. Nous créons notre propre dict prefetched_violations "lié à la prélecture" :

   • La clé est l'ID de violation et la valeur est un tableau contenant les noms de violation (violations_csv ).
   • Pour récupérer uniquement les violations pertinentes, nous utilisons un filtre utilisant queryset pour filtrer uniquement les inspections nécessaires.
   • Nous avons exécuté select_related(None) pour supprimer tous les select_related précédemment définis tables et faites en sorte que l'ORM supprime toutes les jointures inutiles.

2. Nous renvoyons le jeu de requêtes d'origine au export fonction qui produit le fichier Excel.

3. Pour construire la valeur du violations champ, nous utilisons le prefetched_violations nous avons rempli pendant export . Il s'agit de la partie "recherche" du préchargement. En utilisant le prefetch_related de Django nous avons accès à cette valeur sur l'instance, lorsque nous le faisons manuellement, nous devons la rechercher nous-mêmes.

4. Encore une fois, puisque nous n'utilisons plus le prefetch_related de Django nous avons pu utiliser un itérateur. Ainsi, au lieu d'évaluer la requête, nous renvoyons un ensemble de requêtes.

Nous avons déjà été déçus après avoir fait beaucoup d'efforts la dernière fois, voyons si cette fois le travail a porté ses fruits.

Voici les résultats de l'exportation de 10 000 lignes au format xls à l'aide de la prélecture manuelle et de l'itérateur :

15281887 function calls (14721333 primitive calls) in 11.411 seconds
select 0.833
manual prefetch 0.107

Par rapport aux 40 secondes avec lesquelles nous avons commencé, il s'agit d'une amélioration globale de 75 %. 20 secondes ont été réduites en passant au format xls, 10 autres provenant de la prélecture manuelle.

Nous sommes prêts pour la production !

Trouble au paradis

Peu de temps après le déploiement de la nouvelle version en production, nous avons commencé à recevoir des plaintes d'utilisateurs incapables d'ouvrir le fichier.

Rappelez-vous que je vous ai dit qu'utiliser xls était une mauvaise idée ? Eh bien, lorsque les utilisateurs ont commencé à télécharger les fichiers xls, ils ont reçu un message désagréable indiquant que le fichier était corrompu, et Excel, Dieu merci, a réussi à récupérer certaines des données (ce qui est bien pire !).

On pourrait se demander :"mais comment se fait-il que vous n'ayez pas compris cela au contrôle qualité ?" . Eh bien, c'est juste une autre raison pour laquelle nous détestons travailler avec Excel. Lorsque nous l'avons testé localement sur nos bureaux Linux à l'aide de LibreOffice, cela a très bien fonctionné.

Alors récapitulons :

• xlsx est lent et consomme beaucoup de CPU.
• xls n'est pas pris en charge par la version Excel utilisée par nos utilisateurs.
• csv présente de nombreux problèmes d'encodage et de formatage et s'est avéré inutilisable par le passé.

Utiliser un autre rédacteur Excel

Comme toujours, lorsque toutes les options sont nulles et que l'avenir s'annonce sombre, nous nous sommes tournés vers Google.

Une recherche rapide de "python excel performance" a soulevé cet essentiel qui compare 4 écrivains Excel différents en Python (je dois aimer Internet !).

Voici les résultats de référence :

# Source: https://gist.github.com/jmcnamara/ba25c2bf4ba0777065eb

Versions:
 python : 2.7.2
 openpyxl : 2.2.1
 pyexcelerate: 0.6.6
 xlsxwriter : 0.7.2
 xlwt : 1.0.0

Dimensions:
 Rows = 10000
 Cols = 50

Times:
 pyexcelerate : 10.63
 xlwt : 16.93
 xlsxwriter (optimised): 20.37
 xlsxwriter : 24.24
 openpyxl (optimised): 26.63
 openpyxl : 35.75

Selon les résultats, il existe une grande différence entre les bibliothèques xlsx.

Comme mentionné précédemment, nous utilisons django-import-export pour produire des fichiers excel à partir de modèles et de jeux de requêtes Django. Sous le capot, django-import-export utilise le populaire tablib package pour effectuer l'exportation proprement dite.

Tablib offre des capacités d'exportation et d'importation vers et depuis de nombreux formats, mais il ne fait pas le gros du travail lui-même. Pour produire des fichiers xlsx, tablib utilise le package openpyxl .

Un rédacteur Excel plus rapide en Python

En regardant les résultats de l'analyse comparative, openpyxl est le plus lent de tous les packages. Il semble qu'en passant à l'implémentation la plus rapide, pyexcelerate nous pourrions être en mesure d'obtenir des améliorations significatives pour ce processus d'exportation.

Le colis pyexcelerate avait l'air super dès le début. Le slogan est exactement ce dont nous avions besoin :

Même les sous-titres sarcastiques de la section "Utilisation" du README étaient exactement ce que nous voulions :rapide, plus rapide et plus rapide !

Avec des benchmarks aussi prometteurs et un README, il fallait l'essayer !

Correctif tablib

Nous avons déjà un système complet construit sur django-import-export et tablib , et nous ne voulions pas commencer à faire des changements partout. Donc, à la place, nous avons cherché un moyen de patcher tablib et de lui faire utiliser pyexcelerate au lieu de openpyxl .

Après quelques recherches, nous avons découvert que tablib utilise une fonction interne appelée _register_formats pour ajouter des formats d'exportation et d'importation tels que csv, xls et xlsx. Pour obtenir une liste des formats disponibles, tablib importe une collection appelée available du module formats . Le contenu du fichier formats/__init__.py où la collection est définie, ressemble à ceci :

# -*- coding: utf-8 -*-

""" Tablib - formats
"""

from . import _csv as csv
from . import _json as json
from . import _xls as xls
from . import _yaml as yaml
from . import _tsv as tsv
from . import _html as html
from . import _xlsx as xlsx
from . import _ods as ods
from . import _dbf as dbf
from . import _latex as latex
from . import _df as df
from . import _rst as rst
from . import _jira as jira

available = (json, xls, yaml, csv, dbf, tsv, html, jira, latex, xlsx, ods, df, rst)

La partie intéressante est le contenu du fichier _xlsx.py. Le fichier définit certaines fonctions pour exporter et importer depuis Excel en utilisant openpyxl .

Pour patcher tablib , nous devons d'abord implémenter une interface similaire à celle de _xlsx.py en utilisant pyexcelerate , puis enregistrez-le dans tablib .

Commençons par implémenter _xlsx.py en utilisant pyexcelerate :

# fast_xlsx.py
import itertools
from io import BytesIO

from tablib.formats._xlsx import * # noqa
from pyexcelerate import Workbook


# Override the default xlsx implementation
title = 'xlsx'


def export_set(dataset, freeze_panes=True):
 """Returns XLSX representation of Dataset."""
 title = dataset.title or 'Sheet1'
 wb = Workbook()
 wb.new_sheet(title, data=itertools.chain([dataset.headers], dataset))
 stream = BytesIO()
 wb.save(stream)
 return stream.getvalue()


def export_book(databook, freeze_panes=True):
 """Returns XLSX representation of DataBook."""
 assert len(databook._datasets) == 1
 return export_set(databook._datasets[0], freeze_panes)


def dset_sheet(*args, **kwargs):
 assert False, 'How did you get here?'

Il s'agit d'une implémentation simple des fonctions principales. Il manque certaines fonctionnalités telles que plusieurs feuilles, mais cela convenait à nos besoins.

Ensuite, nous devons faire tablib enregistrez ce fichier au lieu du format xlsx existant. Pour ce faire, nous avons créé un nouveau fichier appelé monkeypatches.py :

# monkeypatches.py
import tablib

from . import fast_xlsx

# Override default xlsx format with a faster implementation
# using `pyexcelerate` (export only).
tablib.formats.available += (fast_xlsx, )

Pour appliquer le correctif au tablib , nous importons notre implémentation et l'ajoutons à la liste des formats disponibles. Nous importons ensuite ce fichier dans le __init__.py du module donc à chaque démarrage du système, tablib est patché.

Maintenant, pour le moment de vérité, tout ce travail acharné a-t-il finalement porté ses fruits ?

Ce sont les résultats de l'exportation de 10 000 lignes au format xlsx avec pyexcelerate en utilisant la prélecture manuelle et l'itérateur :

13627507 function calls (13566956 primitive calls) in 10.944 seconds
select 0.137
manual prefetch 2.219

Le travail acharné a définitivement payé! Juste pour que nous ayons une comparaison honnête, voici les résultats de l'exportation de 10 000 lignes au format xlsx sans patcher tablib en utilisant la prélecture manuelle et l'itérateur :

55982358 function calls (46155371 primitive calls) in 29.965 seconds
select 0.137
manual prefetch 1.724

C'est une amélioration de 64 % par rapport à l'implémentation par défaut fournie par tablib , et une amélioration de 75 % par rapport aux années 40 avec lesquelles nous avons commencé.

Résumé des résultats

Voici un résumé de tous les résultats mentionnés dans l'article :

Heure	Lignes	Formater	Méthode
39.927s	10 000	xlsx	prefetch_related (Django)
17.429s	10 000	csv	prefetch_related (Django)
20.694s	10 000	xls	prefetch_related (Django)
60.962	1 000	xls	sous-requête
62.130	1 000	xls	sous-requête et itérateur
57.280s	1 000	xls	requête, sous-requête et itérateur simplifiés
29.965s	10 000	xlsx	tablib par défaut mise en œuvre, prélecture manuelle et itérateur
11.411s	10 000	xls	utilisation de la prélecture manuelle et de l'itérateur
10.944s	10 000	xlsx	en utilisant pyexcelerate , prélecture manuelle et itérateur

Seifa

Nous essayons d'étudier chaque incident et de prendre des mesures pour éviter que des incidents similaires ne se reproduisent à l'avenir. Au cours de cet incident, certains de nos utilisateurs ont connu des lenteurs pendant une courte période, cependant, la fonctionnalité "Exporter vers Excel" n'a pas vraiment tué notre application.

Suite à cet incident, il reste quelques questions ouvertes que nous n'avons pas encore eu l'occasion d'explorer pleinement :

• Pourquoi la requête de prélecture était-elle si lente ? La différence se résume à l'exécution de Model.objects.filter(fk__in = [1,2,3,4....9,999, 10,000]) vs exécution de Model.objects.filter(fk__in = OtherModel.objects.filter( ... ).values_list('pk')) . Lorsque nous avons essayé de comparer les deux dans la base de données, nous n'avons trouvé aucune différence, mais le prefetch_related intégré était nettement plus lent. Est-il possible que du temps soit passé à générer la requête en Python ?

• Peut openpyxl3 les performances peuvent-elles être améliorées ? Lorsque j'ai parlé à John, l'auteur du benchmark des écrivains Excel, il a mentionné que openpyxl3 peut être plus rapide si lxml est installé.

• Xlsx est-il vraiment le meilleur format ? Pouvons-nous éliminer certains des problèmes rencontrés avec csv en passant à un format textuel différent tel que tsv ?

Si vous avez la réponse à l'une de ces questions, n'hésitez pas à les partager avec moi et je serai heureux de publier la réponse.

MISE À JOUR :19 août 2019

Commentaires des lecteurs

Un lecteur de lobste.rs a effectué une analyse comparative rapide pour vérifier la rapidité avec laquelle openpyxl peut obtenir en utilisant lxml . Voici ses résultats :

Versions:
python: 3.6.8
Dimensions:
 Cols = 50
 Sheets = 1
 Proportion text = 0.10
optimised = True

Rows = 10000
Times:
openpyxl: 2.6.3 using LXML True: 3.70
openpyxl: 2.6.3 using LXML False: 6.03

Rows = 1000
Times:
openpyxl: 2.6.3 using LXML True: 0.37
openpyxl: 2.6.3 using LXML False: 0.57

Ce benchmark montre que openpyxl peut être rendu presque deux fois plus rapide simplement en installant lxml . Cependant, pyexcelerate amélioré la vitesse par un facteur de 3.

De nombreux lecteurs sur Reddit et Lobsters ont suggéré qu'une meilleure approche serait de générer le fichier Excel côté client en utilisant Javascript. C'est certainement quelque chose à considérer lors de la conception d'un nouveau système, même si je pense que cette approche pourrait être problématique pour les fichiers très volumineux.