Java:Best Practices SQL/fr

From Alexandre Navarro's Wiki

Jump to: navigation, search

[hide]

1 Comment bien concevoir son modèle une base de données ?
2 Pourquoi ne pas faire des procédures stockées ?
3 Que faut-il faire ?
4 Tips & Memento
- 4.1 Oracle
  - 4.1.1 Informations sur la base (tables, partitions ...)
  - 4.1.2 Tuning
5 =
6 =
- 6.1 Autres
7 Tous les Liens
8 Livres

Comment bien concevoir son modèle une base de données ?

Clés, Contraintes et Colonnes

Faire une clé primaire technique pour chaque table (de préférence un auto-increment).
Mettre une clé unique fonctionnel pour chaque table (si possible) notamment pour faciliter la vie pourles ORM
Utiliser les clés étrangères
Mettre un maximum de contraintes du type NOT NULL ou CHECK valid IN ('V', 'N') ou index unique
Typer vos colonnes avec les bons types (ne pas mettre un VARCHAR pour des données qui ne sont que des nombre)

Index

Mettre un index sur chaque clé étrangère
Mettre des index pertinents
- Des BTree qd la cardinalité est forte, attention à l'odre des colonnes dans un index (un index ne sera jamais utilisé si la clause where ne contient qu'une colonne mis en 2e postion d'un index), mettre plutôt des index très selectifs et dans l'ordre de selectivité décroissant
- Des bitmap qd la cardinalité est faible (attention les index peu séléctifs sont généralement une mauvaise idée)
Penser à l'historisation et à l'archivage (table à part d'archivage voir dans une schéma à part ou utilisation de partition sur la table) pour se prémunir des problèmes liés à 'augmentation du volume de la table
Ne pas mettre non plus trop d'index car les index pénalisent les performances en insert, update et delete

Respecter les formes normales

Les différentes formes

Respecter les formes normales
1FN - première forme normale :
- tout attribut contient une valeur atomique (Une colonne avec des données atomiques ne peut pas contenir plusieurs valeurs du même genre de données)
- tous les attributs sont non répétitifs (Une table avec des données atomiques ne peut pas avoir plusieurs colonnes avec le même genre de données)
- tous les attributs sont constants dans le temps.
2FN - deuxième forme normale
- elle respecte la première forme normale
- tous les attributs non-clés sont totalement dépendants fonctionnellement de la totalité de la clé primaire.
3FN - troisième forme normale
- elle respecte la deuxième forme normale
- tout attribut n'appartenant pas à une clé ne dépend pas d'un attribut

non clé

Ils existent d'autres formes normales voir

(Les différentes formes normales)

Pour se souvenir de l'ordre et des caractéristiques des trois premières formes normales, il suffit de se rappeler le serment que tous les témoins doivent prêter devant la justice : Je jure de dire la vérité, toute la vérité, rien d'autre que la vérité.

Ce qui donne : 1FN = La clé. 2FN = Toute la clé. 3FN = Rien que la clé.

Les avantages des formes normales

Les tables normales n'ont pas de données dupliquées, ce qui réduit la taille de votre base de données.
Généralement, avec moins de données à parcourir, vos requêtes sont plus rapides.

Autres

Ne pas utiliser de procédures stockées (voir paragraphe en dessous)
Ne pas utiliser de trigger (si possible) car cela peut poser des problèmes de maintenabilité et de performance
Ne pas faire de table temporaire

Pourquoi ne pas faire des procédures stockées ?

Conceptuellement

Il faut décorréler l'accession et le stockage des données avec la partie métier (séparation des couches) afin de rendre le code moins dépendant de la façon de stocker les données
Une base de données sert à accéder (de manière ensembliste) ou à stocker de la données et pas à faire autre chose
Pas de possiblité de transformer son stockage de données (en l'occurence en base de données) dans une autre forme (cache clusterisé type Gigaspace ou JBossTreeCache, fichier, base de données objet...)

Difficile à ... comparativement à le faire dans un language évolué type Java/C#

Difficile à debugguer
Difficile à maintenir et faire évoluer pour le type de developpeur que l'on a notamment (pas de spécialiste SQL)
Difficile à profiler
Difficile à monitorer
Difficile à tester (programmation procédurale, pas objet)
Difficile à livrer (opération souvent longue, parfois impossible hors week-end, problème avec les répli de secours ...)
Difficile à être productif par rapport à un ORM pour le type de developpeur que l'on a notamment (pas de spécialiste SQL)

Performance

Pas de possiblité de mettre un cache (type JBossTreeCache) avec un ORM (hibernate par exemple) devant la base de données pour la soulager notamment quand la base est petite et/ou données de référence pas trop grande
Pas threadable
Pas scalable
Pas toutes les facilités liées à un language type Java/C# (structure type map ou list)
Peut-être plus rapide (si l'on diminue les aller/retour réseau notamment) mais aussi moins rapide si l'on commence à faire des conditions ou boucles

qu'un language compilé ou jitté

Dépendance avec le SGBD

Couplage fort entre le langague SQL (Transact-SQL, PL/SQL) et le SGBD

Liens

Why I do not use Stored Procedures

Stored Procedures are EVIL

Don't use stored procedures yet?

To Use Or Not To Use Stored Procedures?

MySQL Stored Procedures problems and use practices

Que faut-il faire ?

Performance

Optimisation Requête

Bien tuner les requêtes en select
- en vérifiant les plans d'éxecution et apprenant à les lire (vérification des index et que l'optimiseur ne se trompe pas dans le plan d'éxecution, sinon hinter la requête notamment en oracle si l'optimiseur est vraiment mauvais)
- en passant par des index ou en faisant des fullscan (le passage par index n'est pas forcément synonyme de meilleur perf notamment quand les données à aller chercher sont dispersées dans toute la table)
- en passant par des jointures en HASH_JOIN s'il n'y pas d'index pertinent sur les colonnes de jointure (ou NESTED_LOOPS si c'est le cas)
- en enlevant les sous-requêtes dans le clauses SELECT ou WHERE (sous-requête exécutée autant de fois que de lignes dans la table), utiliser les vue inline
- en utilisant des vues inline dans le FROM qui sont souvent la clé à pas mal de problèmes de performance
- en utilisant le EXISTS (ou NOT EXISTS) au lieu du IN (ou NOT IN)
- en utilisant plus le UNION_ALL au lieu UNION (si possible)
- en évitant d'utiliser des DISTINCT (si possible)
- en évitant au maximum l'utilisation de fonction (pose souvent problème pour utiliser les index ou au contraire force à utiliser les index alors qu'un fullscan serait plus rapide)

Autour des tables (index, partitions ...)

Mettre des index pertinents (sans non plus tomber dans le cas où l'on rajoute 20 index sur une table) et plutôt dans l'ordre décroissant de sélectivité
Penser à l'archivage dans d'autres tables ou partionner vos tables (notamment avec l'axe temps)
Défragmenter les tables fragmentées
Créer des vues matérialisées (différents des vues normales car c'est une table à part entière alimenté pas les fichiers de log et si la base le permet comme Oracle) quand on veut souvent requêter sur un ensemble de tables (aggrégées ou pas)

Dénormalisation

Dénormaliser le stockage d'une table (stocker par exemple de manière redondantes certaines données pour y accèder plus rapidement en lecture), attention à bien réflechir avant de la faire

Java

Utiliser un ORM (type hibernate) avec un cache potentiellement clustérisé et/ou transactionnelle (type JBossTreeCache) quand le modèle est bien conçu (clé primaire technique et clé étrangère)
Utiliser un Wrapper Jdbc (type Ibatis ou spring-jdbc) quand il n'est pas possible d'utiliser un ORM (modèle mal conçu)
Changer le fetchSize (mis par défaut à 10 en Oracle, 1 en Sybase) quand le nombre de lignes des résultats des requêtes est important (>1000) afin de limiter le coût réseau (un appel réseau est fait tous les fetchSize)
Utiliser le mode batch en jdbc ou au moins un commit tous les n enregistrements (entre 100 - 5000) pour optimiser l'insertion en base (attention le mode batch ne fonctionne pas si aucune colonne de la table a une séquence ou un auto-increment)
Utiliser un pool de connexions pour ne pas créer à chaque fois une connexion (très coûteux pour la base)

Stockage

Créer des tables d'archivage (potentiellement sur un schéma différent)
Purger régulièrement
Ne pas créer des index inutiles
Utiliser les options de compression si cela existe (oracle 10g par

exemple)

Données Historiques

Comment gérer les données historiques en date de valeur ou date système?

Temporal Issues in a Rich Domain Model

Tips & Memento

Oracle

Informations sur la base (tables, partitions ...)

Voir toutes les tables systèmes

<sql> SELECT * FROM DICTIONARY </sql>

Voir toutes les tables

<sql> SELECT * FROM ALL_TABLES </sql>

Voir toutes les colonnes

<sql> SELECT * FROM user_tab_columns WHERE COLUMN_NAME = 'CODTYPINS' </sql>

Voir tous les index

<sql> select * from all_ind_columns where table_name='TFRTDONTPE' </sql>

Voir le code des procédures stockées

<sql> select text from all_source where name = 'K_DER_BIL_TPE' select text from user_source where name = 'F_GET_DAT_DER_BIL_CAS_TPE' </sql>

Voir les partitions

<sql> select * FROM all_ind_partitions select * FROM all_tab_partitions select * from sys.DBA_TAB_MODIFICATIONS where partition_name like 'TVALQTDADVTPE%' </sql>

Voir les connexion à la base

<sql> SELECT * FROM v$session </sql>

Tuning

Voir un Explain plan

<sql> EXPLAIN PLAN SET STATEMENT_ID = 'test' FOR select idftra from tvalqtdnewtpe where DATVAL = '24-nov-2005' AND IDFTRA = 'TPP2107368' </sql>

<sql> SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options, object_name, position ,cost

   FROM plan_table
   START WITH id = 0 AND statement_id = 'test'
   CONNECT BY PRIOR id = parent_id AND statement_id = 'test'

</sql>

Voir les IO

<sql> set autotrace on statistics </sql>

Informations sur le rowid

Extended Rowids

Extended rowids use a base 64 encoding of the physical address for each row selected. The encoding characters are A-Z, a-z, 0-9, +, and /. For example, the following query:

SELECT ROWID, last_name FROM employees WHERE department_id = 20;

can return the following row information:

ROWID LAST_NAME

----------

AAAAaoAATAAABrXAAA BORTINS AAAAaoAATAAABrXAAE RUGGLES AAAAaoAATAAABrXAAG CHEN AAAAaoAATAAABrXAAN BLUMBERG

An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:

OOOOOO: The data object number that identifies the database segment (AAAAao in the example). Schema objects in the same segment, such as a cluster of tables, have the same data object number.

FFF: The tablespace-relative datafile number of the datafile that contains the row (file AAT in the example).

BBBBBB: The data block that contains the row (block AAABrX in the example). Block numbers are relative to their datafile, not tablespace. Therefore, two rows with identical block numbers could reside in two different datafiles of the same tablespace.

RRR: The row in the block.

You can retrieve the data object number from

Information de la distribution des données en oracle à partir du rowid (très intéressant pour savoir s'il faut mieux passer par index ou par fullscan), d'expérience si > 5-10 % des blocs accédés même si le nombre de lignes ramenézs est < 1%, il vaut mieux accéder par fullscan que par index, sinon utiliser l'index. Bien entendu, il faut toujours tester.

- Voir le nombre de blocs accédés (à comparer au nombre de blocs total de

la table) <sql> SELECT COUNT(DISTINCT (SUBSTR(rowid, 0, 15))) FROM TVALQTDADVTPE WHERE DATVAL = '03-08-2006' AND STAVLD = 'V' </sql>

- Voir le nombre de lignes modifiées entre 2 dates

<sql> SELECT COUNT(*) FROM TFLXTPE WHERE DATSYS >= '22-10-2006' AND scn_to_timestamp(ora_rowscn) <= to_timestamp('2006/10/23 07:00:00', 'YYYY/MM/DD HH:MI:SS') AND scn_to_timestamp(ora_rowscn) > to_timestamp('2006/10/23 05:00:00', 'YYYY/MM/DD HH:MI:SS') </sql>

=== Hint (A N'UTILISER QUE SI L'OPTIMISEUR SE TROMPE DE PLAN D'EXECUTION)

=

Forcer l'optimiseur stat (ou lieu de heuristique ou rule) (utilisé par défaut à partir d'oracle 10g) pratique quand l'optimiseur rule a tendance à priviliger les index au passage par fullscan (d'expérience ne sert pas

beaucoup) <sql> SELECT /*+ CHOOSE */ * FROM t1 </sql>

Forcer l'optimiseur rule (utilisé par défaut dans les version Oracle jusqu'à 9), pratique quand l'optimiseur stat a tendance à priviliger les fullscan au passage par index (d'expérience sert beaucoup car l'optimiseur statistique d'Oracle est très mauvais et fait beaucoup de fullscan à tort)

<sql> SELECT /*+ RULE */ * FROM t1 </sql>

Forcer un index

<sql> select /*+ INDEX(scott.emp ix_emp) */ from scott.emp emp_alias </sql>

Forcer un fullscan

<sql> select /*+ FULL(scott.emp) */ from scott.emp emp_alias </sql>

Forcer une jointure par table de hash (intéressant quand il n'y a pas

d'index sur la colonne de jointure) le cout étant O(n + m) n étant le nombre de lignes de la maitre et m le nombre de lignes de la table jointe <sql> select /*+ USE_HASH(emp, dept) */ from

  emp,
  dept

where . . . </sql>

Forcer une jointure par nested loops (intéressant quand il y a un index

sur la colonne de la table jointe) le cout étant O(n*m) n étant le nombre de lignes de la maitre et m le nombre de lignes de la table jointe <sql> select /*+ USE_NL (sal, bon) */ from

  sal,
  bon

where . . . </sql>

Forcer l'ordre de jointure des tables (généralement de la plus selective

à la moins selective) <sql> select /*+ ORDERED */ from

  sal,
  bon

where . . . </sql>

Autres

Appel de procédure

<sql> call DBMS_ERRLOG.CREATE_ERROR_LOG('TOTO','LOG_TOTO') </sql>

Formater des dates ou troncquer une date

<sql> SELECT to_char(sysdate,'DD/MM/RRRR Day D Month Year') FROM dual; </sql>

Tronquer une date (retire les heures)

<sql> SELECT TRUNC(SYSDATE, 'DD') "'DD'" FROM DUAL; </sql>

Date courante

<sql> sysdate </sql>

Updater une table à partir des données d'une autre table

<sql> update ttratpe t set (t.datcloobj, t.datiniobj) = (select f.datfinthe, f.datfinthe from tfrtdontpe f where f.idftra = t.idftra) where t.idftra = 'TPP1070543' </sql>

Liens

Oracle Hints

Optimization Modes and Hints

Oracle SQL hints

Sybase

Informations sur la base (table, partitions ...)

Voir les infos d'une table

<sql> sp_helptext AT_OT </sql>

Voir une procedure stockée

<sql> sp_helptext AT_ostview </sql>

Voir dépendance procedure stockée

<sql> sp_depends ST_availability </sql>

Lancer une procedure

<sql> execute AAAA 'a'; </sql>

Max lignes retourn en Sybase

<sql> set rowcount 10 </sql>

Changement de password

<sql> sp_password PASSWORD, "ce_que_tu_veux" </sql>

Quelle est la version de sybase

<sql> select @@version </sql>

Voir toutes les connexions à la base

<sql> select spid, login = suser_name(suid),hostname,program_name,db = db_name(dbid) from master..sysprocesses group by suid </sql>

sp_who => les connexions en cours sur la base <sql> select * from master..sysprocesses where spid = 121 </sql>

Espace utilisée dans la base

<sql> sp_helpdb BDR sp_helpdb sp_spaceused TABLE </sql>

Tuning

Voir le plan d'execution

<sql> set showplan on set no exec on select ….. From …. Where …. set no exec on set showplan off </sql>

Voir les io

<sql> set statistics io on set showplan on

exec AT_tratitprx @p_idftra='TPP1473795',@p_codtit=3976,@p_datpos='Jul 19 2006 12:00AM' ,@p_datbas='Jul 19 2006 10:49AM' ,@p_prxref=2750.00000000,@p_coddevref=18943,@p_error=1

set showplan off set statistics io off go </sql>

=== Hint (A N'UTILISER QUE SI L'OPTIMISEUR SE TROMPE DE PLAN D'EXECUTION)