Performances Curseurs SQL-Server VS Oracle

Version imprimable

30/09/2011, 08h08
iberserk

Performances Curseurs SQL-Server VS Oracle

Bonjour à tous, pour une fois c'est moi qui ouvre une discussion...
Je suis en audit chez un client qui se plaint de la lenteur d'une procédure de calcul de besoin depuis qu'il a migré ses serveurs de ORACLE 10 vers SQL SERVER 2008 R2.

Avant d'y aller j'ai pensé a un problème de curseur.... réputés moins performants sous MSSQL...

Bingo... il n'y a que ca...

Maintenant problème: c'est un magnifique ERP derrière tout çà... en l'occurence Dynamics AX en version 3 (old quoi :-)).

En regardant le code (entièrement fait côté client bien sûr:aie:) j'ai été choqué de voir que le protocole d'accès aux données (X++) de AX ne savait travailler.... qu'avec parcours de RecordSet... qui génère un curseur pour tous les appels!

La différence de performance est vraiment flagrante (4mn pour ORACLE, + de 8 pour MSSQL!) et inacceptable pour le client...

La solution serait évidemment de réécrire tous cela avec un bonne SP ensembliste qui ferait la même chose en moins d'une minute à n'en pas douter...

Voilà cette discussion juste pour parler de cette différence de performance...
Quelles sont vos expériences là dessus?

NB: j'ai gagné plus d'une minute en désactivant les indexes sur les tables de destination avant de les réactiver mais ca reste vraiment de la petite cuisine...

Je n'ai pas détecte de pression mémoire ou de contention disque (pour la base de test tempDB est sur le même (et unique) disque que les logs et les datas...

Le serveur est une VM sur un ESX les disques sont des 15000 tr/min sur SAN
30/09/2011, 09h05
SQLpro

En dehors des problématiques matérielles, pensez à placer vos curseurs en :
LOCAL , FORWARD_ONLY , STATIC ou FAST_FORWARD, READ_ONLY

A +
30/09/2011, 19h14
iberserk

Merci Frédéric pour ce retour, je n'ai malheureusement pas la main sur les curseurs générés puisque c'est le fournisseur d'accès aux données natif de X++ qui les génère.
30/09/2011, 19h37
StringBuilder

Citation:

Envoyé par iberserk

La différence de performance est vraiment flagrante (4mn pour ORACLE, + de 8 pour MSSQL!) et inacceptable pour le client...

La solution serait évidemment de réécrire tous cela avec un bonne SP ensembliste qui ferait la même chose en moins d'une minute à n'en pas douter...

Voilà cette discussion juste pour parler de cette différence de performance...
Quelles sont vos expériences là dessus?

Travaillant habituellement avec Oracle, j'ai été surpris de voir " l'intégrisme " anti-curseurs qu'il peut y avoir sur le forum SQL Server, alors que personne ne sourcille sur le forum Oracle.

Ce résultat ne me surprend donc pas outre mesure : Oracle semble mieux gérer les curseurs que SQL Server. Une différence de performances du simple au double ne me semble donc pas forcément surprenante, et explique de façon très claire pourquoi sur le forum SQL Server les personnes sont si "anti-curseur" alors que ça semble moins déranger celles du forum Oracle.
30/09/2011, 20h52
Invité

Citation:

Envoyé par StringBuilder

Travaillant habituellement avec Oracle, j'ai été surpris de voir " l'intégrisme " anti-curseurs qu'il peut y avoir sur le forum SQL Server, alors que personne ne sourcille sur le forum Oracle.

Il y a peut-être des personnes qui le martèlent plus fort ici que du côté Oracle, mais ça y est dit et répété régulièrement aussi du côté Oracle.
Mais il reste que les curseurs sont contre-performant des deux bords. Je ne sais pas si Oracle s'en sort un peu mieux avec les curseurs que MS SQL Server mais Oracle s'en sort mieux indiscutablement sans curseur qu'avec...
30/09/2011, 20h53
mikedavem

Citation:

Envoyé par StringBuilder

Travaillant habituellement avec Oracle, j'ai été surpris de voir " l'intégrisme " anti-curseurs qu'il peut y avoir sur le forum SQL Server, alors que personne ne sourcille sur le forum Oracle.

Ce n'est pas tant un intégrisme mais il faut savoir que les curseurs sont par nature "anti-ensembliste" alors que le langage SQL est un langage ensembliste. Les bases de données sont faites pour travailler de manière ensembliste. On peut se passer des curseurs dans 99% des cas. Maintenant il reste quelques rares cas où cela reste à discuter.

Le problème avec les curseurs est que l'on force le moteur SQL à opérer d'une seule façon (ligne à ligne). Avec une requête purement ensembliste le moteur SQL peut faire lui même ses propres choix et proposer de manière heurisitique la meilleure solution.

Citation:

Envoyé par StringBuilder

Ce résultat ne me surprend donc pas outre mesure : Oracle semble mieux gérer les curseurs que SQL Server. Une différence de performances du simple au double ne me semble donc pas forcément surprenante, et explique de façon très claire pourquoi sur le forum SQL Server les personnes sont si "anti-curseur" alors que ça semble moins déranger celles du forum Oracle.[/

Cela semble effectivement se vérifier (j'ai pu moi même le constater). La vrai question serait pourquoi les curseurs sont ils plus performants sur Oracle que sur SQL Server ? Je n'ai malheureusement pas la réponse pour le moment.

++
01/10/2011, 18h36
iberserk

Citation:

Travaillant habituellement avec Oracle, j'ai été surpris de voir " l'intégrisme " anti-curseurs qu'il peut y avoir sur le forum SQL Server, alors que personne ne sourcille sur le forum Oracle.

Ce résultat ne me surprend donc pas outre mesure : Oracle semble mieux gérer les curseurs que SQL Server. Une différence de performances du simple au double ne me semble donc pas forcément surprenante, et explique de façon très claire pourquoi sur le forum SQL Server les personnes sont si "anti-curseur" alors que ça semble moins déranger celles du forum Oracle.

En l’occurrence si les développeur AX avaient bien fait leur boulot, il ne faudrait pas huit heures pour faire un CBN (8 heures sous ORACLE) mais assurément 4 fois moins...
01/10/2011, 19h12
StringBuilder

Qu'on ne se méprenne pas une fois de plus sur ce que je dis : je suis tout à fait d'accord sur le fait qu'une requête (ou une série de requêtes) puisse être bien plus rapide(s) que des traitements dans un (ou plusieurs) curseurs.

Juste que dans l'univers oracle, j'ai jamais vu personne partir au bûcher ou se faire traiter d'abruti après avoir écrit une procédure utilisant un curseur, chose que j'ai vu que le forum SQL Server.

Et en effet, ton expérience montre qu'Oracle semble bien mieux gérer les curseurs que SQL Server (deux fois plus rapide, c'est loin d'être négligeable), et même si un curseur reste moins performant qu'une requête, il ne se transformera pas forcément en goulot d'étranglement avec Oracle, d'où des réactions moins virulentes sur le forum Oracle.
01/10/2011, 19h30
iberserk

Pour info après quelques optimisations faites sur la partie SQL SERVER la différence est moins notable mais reste importante (4mn contre 6mn40).

En effet le client a 3 serveurs car gère lui même trois clients avec des volumétries fort différentes.

Sur le plus gros le CBN prends 8 heures sous ORACLE.
02/10/2011, 17h36
SQLpro
Citation:

Envoyé par iberserk

Merci Frédéric pour ce retour, je n'ai malheureusement pas la main sur les curseurs générés puisque c'est le fournisseur d'accès aux données natif de X++ qui les génère.

En fait la différence fondamentale dans le fonctionnement entre Oracle et SQL Server ne porte pas directement sur les curseurs, mais sur le mode de verrouillage. Oracle (comme PostGreSQL qui a repris cette façon) utilise un mode optimiste basé sur une lecture ancienne des données (snapshot) inconnu de SQL Server dans les versions antérieurs à la 2005.
De ce fait Oracle ne pose donc pas de verrou sur les données manipulées en lecture, au prix cependant d'une erreur de type "snapshot too old" lors des mises à jours dans le cas ou les données vivantes ont évoluées depuis la l'origine du cliché.

Notez que ce mode d'isolation n'existe pas dans la norme SQL qui ne propose que de travailler sur les données vivantes des bases de données avec les niveaux :
- READ UNCOMMITTED (lectures sales)
- READ COMMITTED (lectures de données validées)
- REPEATABLE READ (lectures répétables)
- SERIALIZABLE (mise en série des accès aux données)
Pour un résumé de la chose et ce que cela peut coûter en anomalies transactionnelles, lire le papier que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/isolation-transaction/

Bien entendu vous pouvez activer ce mode dans SQL Server...
Pour ce faire, vous devez d'abord autoriser ce mode au niveau de la base :
Code:

1 2 3 ALTER DATABASE <nom_base> SET ALLOW_SNAPSHOT_ISOLATION ON GO
Puis l'utiliser dans une session :
Code:

1 2 SET TRANSACTION ISOLATION LEVEL { REPEATABLE READ | SNAPSHOT }
C'est probablement possible aussi côté client en modifiant le niveau d'isolation de la connexion.

A +