Discussion :

[PolkovnikMedved]

Bonjour,

C'est la première fois où je travaille avec une DB aussi grande et je dois faire un listing annuel avec des informations provenant de 3 tables sur une DB Oracle 11. Je dois donc faire un inner join entre 3 tables disons A, B et C. Quand je fais un count(*) de cette requête j'obtiens le nombre de 1 269 191 entrées...

J'ai essayé de laisser gérer cette requête à JPQL et en 40 minutes toujours pas de resultats (Ca tourne toujours à l'heure où j'écris ces lignes...)

Est-ce que quelqu'un à déjà été confronté à ce genre de problèmes ? Quelles solutions s'offrent à moi ?

J'ai vu qu'entre-autres je pourrais créer une classe qui contiendrait que les champs nécéssaires de ces 3 entités et que je pourrais recupérer une "List<Object>" et que je pourrais cast cet "Object[]" en mon object representant mes données mais est-ce vraiment efficace ?

J'ai vu aussi que la DB est beaucoup plus performante pour gérer de grandes quantités d'informations et que je pourrais créer une procédure et l'apeller en Java ...

Et quid du multithreading et de la pagination pour faire cela ?

Merci !

[tchize_]

Est-ce que quelqu'un à déjà été confronté à ce genre de problèmes ?

Courrement, dès que ta db une taille "raisonnable" on commence à parler en millions de lignes par table

Quelles solutions s'offrent à moi ?

Le b-a-ba de la gestion de base de données: optimiser tes requêtes, utiliser tes index, ne pas charger un million d'entrée dans ton programme.

J'ai essayé de laisser gérer cette requête à JPQL et en 40 minutes toujours pas de resultats (Ca tourne toujours à l'heure où j'écris ces lignes...)

Je suppose que tu as essayé de charger un million de lignes directement dans le programme; Et maintenant tu vois pourquoi on ne crée pas un million d'objets d'un coup en java. A tu au mois avant de commencé estimé la taille qu'occupe en mémoire tout ce paquet?

J'ai vu qu'entre-autres je pourrais créer une classe qui contiendrait que les champs nécéssaires de ces 3 entités et que je pourrais recupérer une "List<Object>" et que je pourrais cast cet "Object[]" en mon object representant mes données mais est-ce vraiment efficace ?

Ben ce qui est vraiment efficace, c'est de faire une requête qui te retourne les résultats dont tu as besoin et rien que ça. A moins que ton objectif soit de créer un PDF de 10.000 pages regroupant toutes ces données, je doute que ton programme aie besoin ds 1 million de lignes. Je pense plutot qu'il a besoin de résultat aggrégé de ces lignes.

J'ai vu aussi que la DB est beaucoup plus performante pour gérer de grandes quantités d'informations

Sans blague, c'est construit pour ça

et que je pourrais créer une procédure et l'apeller en Java ...

Je vois pas le rapport avec un procédure. Commence par faire une requête correcte

Et quid du multithreading

Ouais, tu va passer de 4 semaines à 3 pour exécuter la requete, peut être....

et de la pagination pour faire cela ?

Ben si tu dois juste parcourir les donnée, oui, réduire en page par page ça peut éviter de charger 99.9% de trucs inutiles.

Si tu commencais par nous montrer tes tables et ce que tu essaie d'obtenir.

[PolkovnikMedved]

Si tu commencais par nous montrer tes tables et ce que tu essaie d'obtenir.

Merci pour ta réponse, désolé si je répond 2 semaines après mais j'ai du bosser sur autre chose en attendant.

J'ai vérifié pour tout ce qui est index et il y en a partout où il en faut.

Pour ce qui est de mes tables, j'ai les liens suivants : Adresse - Affilié - Décomptes - Créances . Et je cherche la somme des créances pour tous les affiliés pour une année spécifique. J'ai bien suivi ton conseil et optimisé ma requête afin qu'elle ne reprenne QUE les données que j'ai besoin pour faire mon listing.

J'ai crée une classe comme ceci qui représente seulement les données que j'ai besoin des 4 tables :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
 
import javax.persistence.*;
import java.math.BigDecimal;
 
@SqlResultSetMapping(
        name = "AffiliateAddressStatements",
        classes = @ConstructorResult(
                targetClass = AffiliateStatementItemsDTO.class,
                columns = {
                        @ColumnResult(name = "aff1", type = Integer.class),
                        @ColumnResult(name = "aff2", type = Integer.class),
                        @ColumnResult(name = "sum1", type = BigDecimal.class),
                        @ColumnResult(name = "sum2", type = BigDecimal.class),
                        @ColumnResult(name = "lan", type = Integer.class),
                        @ColumnResult(name = "pre_tva", type = String.class),
                        @ColumnResult(name = "num_tva", type = Long.class),
                        @ColumnResult(name = "sta_tva", type = String.class),
                        @ColumnResult(name = "no_tva_eur", type = String.class),
                        @ColumnResult(name = "no_agr_bur", type = Integer.class),
                        @ColumnResult(name = "den", type = String.class),
                        @ColumnResult(name = "adr", type = String.class),
                        @ColumnResult(name = "no_cod_pos", type = String.class),
                        @ColumnResult(name = "loc", type = String.class)
                }
        )
)
@Entity
public class AffiliateStatementItemsDTO {
    @Id
    private Integer id;
 
    private Integer aff1;
 
    private Integer aff2;
 
    private BigDecimal sum1;
 
    private BigDecimal sum2;
 
    private Integer languageCode;
 
    private String vatPrefix;
 
    private Long vatNumber;
 
    private String vatStatus;
 
    private String europeanVatNumber;
 
    private Integer agency;
 
    private String addressName;
 
    private String street;
 
    private String zipCode;
 
    private String locality;
 
+ Constructeurs + getters/setter + méthode to string

Ensuite j'ai une requête comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
 
public List<AffiliateStatementItemsDTO> retrieveAffiliates(int aMin, int aMax, LocalDate yearBegin, LocalDate yearEnd)
    {
        logger.info("We are going to retrieve affiliates from aMin = " + aMin + " and aMax = " + aMax);
        long dbBegin = System.nanoTime();
 
        EntityManager em;
        List<AffiliateStatementItemsDTO> result;
 
        List<String> journalIds1 = Arrays.asList("83", "93", "94", "95", "96");
        List<String> vatBaseIds1 = Arrays.asList("44", "48");
        List<Integer> organizations = Arrays.asList(8000, 9000);
        String firstType = "12";
        String secondType = "29";
        String thirdType = "18";
        List<String> types1 = Arrays.asList("06", "41", "42");
 
        List<String> journalIds2 = Arrays.asList("83", "93", "94", "95", "96");
        List<String> vatBaseIds2 = Arrays.asList("44", "48");
        Integer org = 9000;
        List<String> types2 = Arrays.asList("20", "21", "22", "29");
 
        String sql1 = "select a1.no_aff as aff1, a1.COD_LAN as lan, a1.PRE_TVA as pre_tva, a1.NUM_TVA as num_tva, a1.STA_TVA as sta_tva, a1.NO_TVA_EUR as no_tva_eur, a1.NO_AGR_BUR as no_agr_bur, ar1.DEN as den, ar1.ADR as adr, ar1.NO_COD_POS as no_cod_pos, ar1.LOC as loc, sum(c1.mt_cre) as sum1 from AFF a1 "
                + "inner join ADR ar1 on a1.NO_AFF = ar1.NO_AFF and a1.COD_ADR_SOC = ar1.COD_ADR and a1.COD_LAN = ar1.COD_LAN "
                + "inner join REL r1 on a1.NO_AFF = r1.NO_AFF "
                + "inner join CRE c1 on c1.NO_AFF = r1.NO_AFF and c1.NO_JOU = r1.NO_JOU and c1.EXE_REL = r1.EXE_REL and c1.NO_REL = r1.NO_REL "
                + "where a1.NO_AFF between ?1 and ?2 "
                + "and r1.DAT_EXP between ?3 and  ?4 "
                + "and r1.NO_JOU in ?5 "
                + "and c1.NO_BAS_TVA not in ?6 "
                + "and c1.NO_ORG in ?7 "
                + "and (c1.TYP_CRE < ?8 OR c1.TYP_CRE > ?9 OR c1.TYP_CRE = ?10 ) "
                + "and c1.TYP_CRE not in ?11 "
                + "group by a1.NO_AFF, a1.COD_LAN, a1.PRE_TVA, a1.NUM_TVA, a1.STA_TVA, a1.NO_TVA_EUR, a1.NO_AGR_BUR, ar1.DEN, ar1.ADR, ar1.NO_COD_POS, ar1.LOC "
                + "order by a1.NO_AFF";
 
        String sql2 = "select a2.no_aff as aff2, sum(c2.mt_cre) as sum2 from AFF a2 "
                + "inner join ADR ar2 on a2.NO_AFF = ar2.NO_AFF and a2.COD_ADR_SOC = ar2.COD_ADR and a2.COD_LAN = ar2.COD_LAN "
                + "inner join REL r2 on a2.NO_AFF = r2.NO_AFF "
                + "inner join CRE c2 on c2.NO_AFF = r2.NO_AFF and c2.no_jou = r2.no_jou and c2.exe_rel = r2.exe_rel and c2.no_rel = r2.no_rel "
                + "where a2.NO_AFF between ?12 and ?13 "
                + "and r2.DAT_EXP between ?14 and ?15 "
                + "and r2.no_jou in ?16 "
                + "and c2.no_bas_tva not in ?17 "
                + "and c2.no_org = ?18 "
                + "and c2.typ_cre in ?19 "
                + "group by a2.NO_AFF "
                + "order by a2.NO_AFF";
        String sql = "select aff1, aff2, sum1, sum2, lan, pre_tva, num_tva, sta_tva, no_tva_eur, no_agr_bur, den, adr, no_cod_pos, loc from ( " + sql1 + " ) full outer join ( " + sql2 + " ) on aff1 = aff2";
 
        em = JpaUtil.getEntityManagerFactory().createEntityManager();
        em.getTransaction().begin();
 
        Query query = em.createNativeQuery(sql, "AffiliateAddressStatements")
                .setParameter(1, aMin)
                .setParameter(2, aMax)
                .setParameter(3, Date.valueOf(yearBegin), TemporalType.DATE)
                .setParameter(4, Date.valueOf(yearEnd),TemporalType.DATE)
                .setParameter(5, journalIds1)
                .setParameter(6, vatBaseIds1)
                .setParameter(7, organizations)
                .setParameter(8, firstType)
                .setParameter(9, secondType)
                .setParameter(10, thirdType)
                .setParameter(11, types1)
                .setParameter(12, aMin)
                .setParameter(13, aMax)
                .setParameter(14, Date.valueOf(yearBegin), TemporalType.DATE)
                .setParameter(15, Date.valueOf(yearEnd), TemporalType.DATE)
                .setParameter(16, journalIds2)
                .setParameter(17, vatBaseIds2)
                .setParameter(18, org)
                .setParameter(19, types2);
 
        result = query.getResultList();
 
        em.getTransaction().commit();
 
        long dbEnd = System.nanoTime();
        logger.info("We get all the data (" + result.size() + ") in " + (dbEnd - dbBegin) / 1000000 + " milliseconds.");
        logger.info("------------------------------------------");
 
        return result;

Le souci principal est qu'en fait je dois séparer les montants des créances en deux (car il y a deux "type") et donc en le faisant en SQL on récupère 27 000 entrées alors qu'en le faisant en Java on doit prendre les 1M entrées.

Avec ceci j'ai pensé a ne pas tout récupérer d'un coup et à passer dans le

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

where a2.NO_AFF between ?12 and ?13

les valeurs petit à petit. Par exemple :
je dois prendre les affiliés avec le numéro d'affilié entre 0 et 999 999. Donc je vais récupérer les affiliés de 0 à 1000 et les écrire dans le fichier, puis ceux de 1001 à 2000 écrire dans le fichier etc.

Ce déjà bien mieux non ?

[NoClassDefFound]

Que dit l'explain plan pour les différentes requêtes ?