All posts tagged Oracle

Benchmark Oracle Excel

Un Benchmark Oracle Excel !? Autant comparer un vélo et un TGV ? Les deux sont des outils sympathiques, mais n’ont pas réellement la même utilité… Mais à force de noter que cette chère base de données Oracle est plutôt réactive, et que parfois, la fonction RECHERCHEV de Excel peut causer bien des soucis de performances (attention au dernier paramètre « recherche approchée » !), et si on comparait pour de vrai ?! Avec un vrai protocole…

Avant toute chose, pour bien utiliser RECHERCHEV sur de grands tableaux Excel, une seule recommandation, désactivons le calcul automatique (menu « Formules – Calcul – Options de calcul – Manuel ».

Ainsi, lorsque nous modifions des formules et que nous les recopions sur 200.000 lignes, Excel ne refait pas tous les calculs, ce qui peut parfois prendre plusieurs minutes si le mode de recherche approchée est laissé à FAUX. Pour rafraîchir les résultats des formules, il faut appuyer sur la touche F9.

CONDITION D’EXÉCUTION DU TEST

Il s’agit d’un test fonctionnel : rapprocher 500.000 opérations et 1.000.000 clients. Excel va faire ce rapprochement (jointure), ramener le nom du client pour chaque opération, puis enregistrer le fichier pour le rendre disponible.

Côté Oracle, on va charger exactement les mêmes données. On crée une nouvelle base, des tables toutes neuves, on ne met pas en place d’optimisation particulière, si ce n’est bien sûr une clé primaire sur chaque table. On effectue la jointure entre les deux tables, et on écrit le résultat dans une table de résultats pour enregistrer le résultat. L’outil TOAD nous dira combien de temps tout ceci a pris.

Bien entendu, ces tests s’effectueront sur la même machine, sur laquelle sera désactivé l’antivirus ainsi que tout programme parasite. La machine de test est un PC portable, armé d’un CPU Core2Duo 2GHz, de 2Go de mémoire vive, XP 32 bits, Excel 2007, et la version gratuite d’oracle, la 10g.

TEST DE PERFORMANCE DE JOINTURE EXCEL

Nous avons créé un fichier Excel 2007, avec deux feuilles,  » clients  » et  » operations « . Sur la feuille client, nous stockons un numéro de client incrémental de 1 à 1.000.000 et un nom de client, de « nom1 » à « nom1000000 ». Sur la feuille  » operations « , nous créons 500.000 opérations numérotées de 1 à 500.000, opérations auxquelles il faut associer un numéro de client, généré aléatoirement de 1 à 1.000.000 à l’aide des fonctions ARRONDI et ALEA :

=ARRONDI(ALEA()*1000000;0)+1

Pour copier/coller la formule, rien de plus simple : un double-clic sur la poignée de recopie de formule, entouré en rouge, et la formule va se recopier automatiquement jusqu’à la dernière ligne non vide de la colonne précédente.

Un appui sur F9 pour rafraichir, histoire d’avoir des numéros générés aléatoirement. Nous figeons les valeurs des formules avec une copier collage spécial des valeurs.

Maintenant, le fichier Excel contient les données à rapprocher, des données clients et des données opérations. A chaque opération, nous allons rechercher le nom du client. Cette recherche va s’effectuer à l’aide de la fonction RECHERCHEV sous Excel et avec une jointure SQL sous Oracle.

La jointure Excel va s’écrire ainsi :

=RECHERCHEV(B2;clients!$A$2:$B$1000001;2;FAUX)

Attention ! Insistons bien sur le dernier paramètre de la fonction RECHERCHEV. Ce critère concerne le tri ou non de la plage cible, et surtout, si on le met à VRAI, Excel effectue une recherche approchée et ce n’est pas ce qu’on veut, et à priori, c’est rarement ce qu’on veut quand on implémente cette fonction… En effet, on pense faire une recherche exacte, et bien non, Excel renvoi la valeur inférieure la plus proche, ce qui peut rendre complètement faux nos calculs….

•    Valeur proche à VRAI : une recherche comme en base de données, avec un algorithme de btree, plutôt performante. Problème, dans Excel ce n’est pas une recherche exacte .

•    Valeur proche à FAUX : une recherche de base, il relit séquentiellement toutes les informations de la plage cible pour trouver une correspondance. C’est l’équivalent du Full Table Scan en base de données. Cette recherche renvoie le bon résultat, et nous informe si nous n’avons pas de correspondance exacte.

Reprenons notre fichier avec valeur proche à Faux, un double-clic pour recopier la formule de manière incrémentale jusqu’en bas. Pas de F9, surtout pas, car ça va être beaucoup trop long… Juste le bout de code suivant qui va lui-même rafraichir le document, mesurer et afficher le temps de calcul :

Sub BENCHMARK_EXCEL()
Maintenant1 = Now
Calculate
ActiveWorkbook.Save
maintenant2 = Now
duree = maintenant2 - maintenant1
Sheets("operations").Select
Cells(1, 5).Value = duree
End Sub

Attention, l’exécution de ce code peut durer très longtemps avec la recherche approchée à FAUX…

TEST DE PERFORMANCE DE JOINTURE ORACLE

Pour le test Oracle, il faut tout d’abord créer les tables sous TOAD (version free), à l’aide des scripts suivants :

CREATE TABLE DEXT.CLIENTS
(
NUM_CLI INTEGER NOT NULL,
NOM VARCHAR(32 BYTE) NOT NULL
) NOLOGGING;
CREATE UNIQUE INDEX DEXT.PK_CLIENTS ON DEXT.CLIENTS
(NUM_CLI) NOLOGGING;
CREATE TABLE DEXT.OPERATIONS
(
NUM_OPE INTEGER NOT NULL,
NUM_CLI INTEGER NOT NULL
) NOLOGGING;
CREATE UNIQUE INDEX DEXT.PK_OPERATIONS ON DEXT.OPERATIONS
(NUM_OPE) NOLOGGING;
CREATE TABLE DEXT.RESULTATS
(
NUM_OPE INTEGER NOT NULL,
NOM VARCHAR2(32 BYTE) NOT NULL
) NOLOGGING;
CREATE UNIQUE INDEX DEXT.PK_RESULTATS ON DEXT.RESULTATS
(NUM_OPE) NOLOGGING;

Nous voyons nos tables sous TOAD :

Maintenant, il faut charger mêmes données que celles du fichier Excel. Pour cela nous utilisons l’utilitaire Excel tools for SGBD (version de dev sur les copies d’écran) pour exporter directement des données de Excel vers des SGBD classiques (SQL Server, Oracle, Access…) :

On choisit les Feuilles Excel contenant les données à exporter vers Oracle.

Nous allons exporter les données vers Oracle, en mode SQL Loader (Oracle doit être installé sur le poste, ce que vérifie le plugin en interrogeant la base de registres).

Nous renseignons les paramètres de connexion.

L’étape suivante nous permet de « mapper » les chargements.

Et voici l’exécution de SQL loader :

Et voilà, les tables sont chargées. Si vous avez trouvé cet utilitaire pratique, n’hésitez pas à me le signaler ! Cela m’encouragera à développer la version finale !

Maintenant que les tables sont chargées, pour lancer le test, nous exécutons la requête suivante :

insert into RESULTATS
select
OPERATIONS.NUM_OPE,
CLIENTS.NOM
from
OPERATIONS,
CLIENTS
where
OPERATIONS.NUM_CLI = CLIENTS.NUM_CLI;
Commit;

RESULTATS

Excel (valeur proche à FAUX) : 1h28min

Oracle : 25s

Excel (valeur proche à VRAI, avec tri initial des données) : 4s

SQL Server 2005 : 4s (dans les mêmes conditions que pour Oracle)

CONCLUSION

Il ne faut surtout pas en conclure qu’Excel est plus rapide qu’Oracle (ni bien sûr SQL Server). Dans notre cas de figure oui, et c’est bien heureux, vu qu’Excel travaille en mémoire vive…

Mais la fonction RECHERCHEV est étrange… Lorsque l’on utilise la recherche exacte (ce que l’on veut dans la plupart des cas !), le calcul sur des volumes importants de données est désespérément long. En utilisant la recherche approchée à VRAI, la recherche est très rapide, mais cela a plusieurs inconvénients : tout d’abord il est nécessaire de trier les données, ensuite les résultats sont des résultats approchés (la valeur retournée est soit la valeur exacte, soit la valeur la plus proche si la valeur cherchée n’est pas trouvée). Sachant qu’un tri en mémoire vive de 1.000.000 d’enregistrements (simples) ne prend qu’une seconde, on se demande pourquoi ce n’est pas implémenté par défaut dans la fonction RECHERCHEV…

Pour obtenir les même résultats avec la recherche approchée à VRAI, il faut :

Trier les données de la TABLE MATRICE (valeurs cibles dans lesquelles on cherche les résultats)

Changer la formule de recherche :

=RECHERCHEV(B2;clients!$A$2:$B$1000001;2;FAUX)

Par

=SI(RECHERCHEV(B2;clients!$A$2:$B$1000001;2;FAUX)=B2; RECHERCHEV(B2;clients!$A$2:$B$1000001;2;FAUX);NA())
Top
Page 1 of 1

Contactez-nous contact@expert-data.fr

EXPERT data, solutions pour TPE/PME et grands comptes