Eviter les doublons dans une base access

**gilles_906** · 28/08/2011, 17h10

Bonjour

Une table access contient des enregistrements.
Je rajoute des enregistrements provenant d'une datatable dans la table access.

Le Hic, est que dans la datatable il y a des enregistrements qui sont déja dans la table access et d'autre non.

Je voudrais ne remplir la base access qu'avec les enregistrements manquant.

Voici comment je fais mais je pense que ce n'est pas la meilleure facon de faire.
Un champ qui est unique dans chaque enregistrement est défini en clé primaire sur la table access

connexion a la base access
lecture de la premiere ligne de la datatable
insert into la table access
gestion de l'erreur sur doublon clé primaire si l'enregistrement existe déja j'ignore l'erreur et passe a la ligne suivante de la datatable.

Cela fonctionne mais j'ai lu que la gestion des erreurs était a proscrire car lente.

Sachant que la base access va contenir plusieurs milliers d'enregistrements et que la datatable va en contenir a chaque fois une centaine.

Qu'est ce qui a votre avis est le plus long?
la gestions de 50 erreurs (on va dire qu'un enregistrement sur deux est déjà présent)

ou

Le chargement dans une deuxième datatable de la table access contenant plusieurs milliers d'enregistrement
la requete entre les deux datatables excluant les doublons
puis le transfert de la requete dans la table access?

peut être avez vous une autre solution a me proposer.

Merci de m'avoir lu.
Gilles

**tomlev** · 28/08/2011, 18h48

Envoyé par gilles_906

Le Hic, est que dans la datatable il y a des enregistrements qui sont déja dans la table access et d'autre non.

Bah c'est ce problème là qu'il faut régler... ça ne devrait pas arriver. Tu dois modéliser dans ta DataTable les même contraintes de clé primaire ou d'unicité que dans la table en BDD, comme ça tu n'auras pas de doublons dans la DataTable, et le problème ne se posera plus

**gilles_906** · 28/08/2011, 18h54

oui mais cela m'oblige a charger la table access, ce qui va prendre du temps car elle contient beaucoup d'enregistrements.

**tomlev** · 28/08/2011, 19h34

Dans ce cas, ne charge que les lignes sur lesquelles tu vas travailler... Avant de travailler sur une ligne qui n'est pas dans la DataTable, tu refais un Fill avec une requête qui récupère juste la ligne voulue, et ensuite tu travailles en mémoire

**gilles_906** · 28/08/2011, 20h00

j'ai pas tout compris

je ne charges que les lignes sur lesquels je travaille puisqu'elles proviennent d'un fichier xml, elles sont donc affichées dans un datagrid qui est lié a une datatable.

Tu me dis (si j'ai bien compris) de faire une requete pour trouver dans ma BDD l'enregistrement correspondant a la ligne de la datatable?
je trouve => doublon
je trouve pas => nouvel enregistrement

Si c'est ca, cela va me faire exécuter une centaine de fois la requete, et la requete va s'executer sur plusieurs milliers d'enregistrements.

Pour que tu comprenne mieux ce que je veux faire:
J'ai une table access qui contient toutes les operations bancaires.

Je telecharge sur le site de la banque un fichier qui contient les opérations faites sur la période demandé (il peu il y avoir de nouvelles opérations)

j'affiche les opérations du fichier dans un datagrid.

sur appuie d'un bouton aprés vérif manuelle j'envoie les opérations dans la BDD pour stockage (uniquement celle qui n'y sont pas)

**tomlev** · 29/08/2011, 01h35

Envoyé par gilles_906

Si c'est ca, cela va me faire exécuter une centaine de fois la requete, et la requete va s'executer sur plusieurs milliers d'enregistrements.

Et alors ? Y a pas d'index dans ta DB ? Si tu fais une recherche sur une colonne indexée, c'est très rapide... D'ailleurs, quelques milliers de lignes c'est rien du tout ; à partir de quelques millions on peut commencer à dire que c'est une grosse table, mais des milliers... c'est peanuts

(même pour Access, qui est probablement le SGBD le plus pourri qui soit

)

Envoyé par gilles_906

Pour que tu comprenne mieux ce que je veux faire:
J'ai une table access qui contient toutes les operations bancaires.

Je telecharge sur le site de la banque un fichier qui contient les opérations faites sur la période demandé (il peu il y avoir de nouvelles opérations)

j'affiche les opérations du fichier dans un datagrid.

sur appuie d'un bouton aprés vérif manuelle j'envoie les opérations dans la BDD pour stockage (uniquement celle qui n'y sont pas)

OK, j'avais pas bien compris ton problème alors...

Tu as un identifiant qui te permet de retrouver une ligne, ou tu compares tous les champs ? Si tu as un identifiant, j'ai une suggestion pas super propre mais qui devrait marcher : tu construis une requête avec "WHERE ID_OPERATION IN (x, y, z ...)", où x/y/z/etc sont les IDs des opérations dans le fichier. Ca te permet de repérer facilement les lignes qui sont déjà dans la DB

**gilles_906** · 29/08/2011, 01h56

je trouve que c'est déja pas super rapide d'ajouter a la BDD une centaine de lignes alors faire une requete select sur chaque ligne de la BDD puis ajouter les manquantes ca va me prendre trois plombes.

J'ai un champ qui est unique et qui me sert de clé primaire, il est construit comme ca:

yyyymmdd12345

12345 s'incremente a chaque operation et la date est celle de l'operation.

Est il possible de créer une autre datatable dans laquelle je chargerai ce champ puis sur une requete select qui aurai une liaison sur ce champ avec la datatable.
La requete ne contiendrait alors plus que les enregistrements qui sont nouveau.
En la mettant dans un datareader je pourrais completer ma BDD.

Tu en penses quoi?

**tomlev** · 29/08/2011, 02h36

Envoyé par gilles_906

Est il possible de créer une autre datatable dans laquelle je chargerai ce champ puis sur une requete select qui aurai une liaison sur ce champ avec la datatable.
La requete ne contiendrait alors plus que les enregistrements qui sont nouveau.
En la mettant dans un datareader je pourrais completer ma BDD.

Tu en penses quoi?

Euh... j'ai pas trop compris ta suggestion en fait

**narutobaka** · 29/08/2011, 09h19

Bonjour,

Si tu as un identifiant unique pour chacun de tes n-uplet, pourquoi ne fais tu pas une requête qui compte le nombre de ligne correspondant à la clef, un count.

Si le count renvoit une valeur > 0 la ligne existe déjà tu n'ajoutes pas la ligne sinon tu l'ajoutes.

++

**gilles_906** · 29/08/2011, 11h58

Merci de ta reponse narutobaka mais cela revient a faire ce que me proposait Tomlev, a savoir.

Hypothese:
La table de la BDD a 10 000 enregistrements
La datatable de mon appli a récupérée une centaine d'enregistrement
Dans la centaine d'enregistrement j'en ai 50 qui sont déja dans la BDD

Je vais donc faire 100 * 10 000 la requete selection pour savoir si chaque enregistrement est present dans la table
+ 50 fois la requete Ajout.

Ca va bien prendre du temps tout ca.

Tomlev:

Soit:
DatatableOperation => la datatable qui contient les 100 enregistrements qui proviennent du fichier

TOperations => La table de la BDD access

ChampID => un champ unique a chaque enregistrement qui sert de clé primaire

Est il possible de faire?

Une nouvelle datatable (je n'ai pas fait de dataset)=> DatatableBDD
Faire une liaison relationelle entre DatatableBDD et DatatableOperation sur le champ ChampID (relation de un a plusieurs sens 1 pour DatatableOperation, plusieurs pour DatatableBDD)

Une fois la relation mise en place:
Il me suffit de faire une requete sur le champ ChampID d'une des tables.
Elle devrait me renvoyer tous les enregistrement présent dans les deux tables, donc les doublons.
Il me suffirait de les enlever le la datatable DatatableOperation, puis d'ajouter les enregistrements de DatatableOperation a DatatableBDD.

Cela ne serait pas beaucoup plus rapide?

la requete ente les deux datatable serait du style

Code :

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

SELECT DatatableOperation.ChampID  , DatatableBDD.ChampID  FROM DatatableBDD INNER JOIN TOperationBanques ON DatatableBDD.ChampID = DatatableOperation.ChampID;

C'est plus clair comme ca?

**narutobaka** · 29/08/2011, 13h17

Code :

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

1
2
Je vais donc faire 100 * 10 000 la requete selection pour savoir si chaque enregistrement est present dans la table
+ 50 fois la requete Ajout.

Bonjour,

Si tu fais ta recherche sur ta clef primaire, tu ne passes pas les 10000 champs en revu. Je pense pas dire de bétise en disant que c le principe des index.

Ce que je t'indique sers juste à savoir si la ligne avec l'id primaire que tu testes existe ou non.

Quand à diminuer la compléxité des opérations je vois pas bien comment vérifier pour 100 lignes si c 100 lignes existe ou non déjà en BDD, à moins qu'à la génération de ton fichier XML tu ais un booleen qui te permette de le savoir.

Quand à l'insertion bah tu auras autant d'insertion que de ligne que tu veux inserer.

**gilles_906** · 29/08/2011, 13h42

Merci de ta reponse

Oui le champ de clé primaire est indexé.

Je vais essayer de le faire comme ca, on verra bien le temps que cela prend

si cela est acceptable ou pas

**tomlev** · 29/08/2011, 13h42

Envoyé par gilles_906

Une nouvelle datatable (je n'ai pas fait de dataset)=> DatatableBDD
Faire une liaison relationelle entre DatatableBDD et DatatableOperation sur le champ ChampID (relation de un a plusieurs sens 1 pour DatatableOperation, plusieurs pour DatatableBDD)

Il faut forcément un DataSet pour établir une relation entre 2 tables

Envoyé par gilles_906

la requete ente les deux datatable serait du style

Code :

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

SELECT DatatableOperation.ChampID  , DatatableBDD.ChampID  FROM DatatableBDD INNER JOIN TOperationBanques ON DatatableBDD.ChampID = DatatableOperation.ChampID;

On peut pas faire des requêtes SQL entre 2 DataTables... c'est pas un SGBD, juste une sorte de "cache" en mémoire des données de la base.

Mais de toutes façons, comment tu remplis DataTableBDD ? Tu veux pas charger toute la DB en mémoire, et tu sais pas à l'avance quelles lignes sont déjà présentes...

**gilles_906** · 29/08/2011, 14h11

Ben si j'aurai chargé la datatableBDD en mémoire mais je ne faisais qu'une seule requete pour extraire les lignes identiques.

Mais si on ne peut pas lié les deux datatables qui sont en mémoire c'est mort

**tomlev** · 29/08/2011, 14h50

On peut les lier; ce qu'on peut pas faire, c'est des requêtes SQL entre elles

Tu peux faire un truc comme ça :

- entre les tables BDD et Operation, tu fais une relation BDD_Operation, avec BDD comme parent, et Operation comme enfant (sans activer la contrainte sur la relation, sinon ça va péter quand il n'y aura pas de parent)
- dans la table Operation, tu mets une colonne calculée qui récupère l'ID de la ligne parent (Expression = "Parent(BDD_Operation).ID")

En code ça donne quelque chose dans ce goût là :

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
Dim ds As New DataSet()
Dim bdd As New DataTable("BDD")
bdd.Columns.Add("ID", GetType(String))
bdd.PrimaryKey = New () {bdd.Columns("ID")}
 
Dim operation As New DataTable("Operation")
operation.Columns.Add("ID", GetType(String))
operation.PrimaryKey = New () {operation.Columns("ID")}
 
ds.Tables.Add(bdd)
ds.Tables.Add(operation)
 
ds.Relations.Add("BDD_Operation", New () {bdd.Columns("ID")}, New () {operation.Columns("ID")}, False)
 
operation.Columns.Add("BDD_ID", GetType(String), "Parent(BDD_Operation).ID")
 
bdd.Rows.Add("123")
bdd.Rows.Add("456")
bdd.Rows.Add("789")
 
operation.Rows.Add("456")
' BDD_ID = 456
operation.Rows.Add("987")
' BDD_ID = NULL

(exemple très simplifié bien sûr, il faut adapter à ton code...)

Ensuite, tu peux "requêter" sur Operation avec la méthode Select :

Code :

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

1
2
3
For Each row In operation.Select("BDD_ID IS NULL")
    ...
Next

**gilles_906** · 29/08/2011, 14h53

Bon je l'ai fais comme ca:

Une requete ajout RAjout=> ajoute un enregistrement dans la BDD
Une requete selection RTrouveDoublon=> requete selection entre ma datatableOperation (en mémoire) et la BDD sur uniquement le champ clé

Une boucle sur la datatable

If le resultat de la requete selection obtient quelque chose

passe au suivant sur la boucle de la datatable

else
execute la requete ajout

passe au suivant de la datatable

ben ca prends du temps

J'ai 250 enregistrements dans la BDD
j'ai 252 enregistrements dans la datatable

je n'ai donc que 2 lignes a rajouter, cela me prend 4 000 tick d'horloge, ca fait dans les 3 à 4 secondes.

Sur 10 000 enregistrement dans la BDD ca va me prendre 1 a 2 minutes

Si je ne trouve rien d'autre il va me falloir faire une barprogress

Voila le code:

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
        Dim cmdText As String = "INSERT INTO TOperationBanques (NumeroCompte, TRNTYPE, DTPOSTED, TRNAMT, FITID, NomOperation, StrMEMO) VALUES (?,?,?,?,?,?,?)"
        Dim ChercheDoublon As String = "SELECT TOperationBanques.FITID FROM TOperationBanques WHERE TOperationBanques.FITID = ?"
        Dim Cmd As OleDbCommand = New OleDbCommand(cmdText, MyConnexion)
        Dim CmdVerifDoublons As OleDbCommand = New OleDbCommand(ChercheDoublon, MyConnexion)
        Dim BonneDate As Date
        Dim reader As OleDb.OleDbDataReader
 
        Try
            MyConnexion.Open()
 
            Cmd.CommandType = CommandType.Text ' The default is CommandType.Text
            CmdVerifDoublons.CommandType = CommandType.Text
 
            CmdVerifDoublons.Parameters.Add("@p1", OleDbType.VarChar).Value = ""
 
            With Cmd.Parameters
                .Add("@p1", OleDbType.Double).Value = 0
                .Add("@p2", OleDbType.VarChar).Value = ""
                .Add("@p3", OleDbType.Date).Value = 1 / 1 / 1900
                .Add("@p4", OleDbType.Double).Value = 0
                .Add("@p5", OleDbType.VarChar).Value = ""
                .Add("@p6", OleDbType.VarChar).Value = ""
                .Add("@p7", OleDbType.VarChar).Value = ""
            End With
 
            'boucle qui passe en revue la datatable et ajoute la ligne a la base de données .mdb
            For i = 0 To DataTableOperation.Rows.Count - 1
 
                CmdVerifDoublons.Parameters("@p1").Value = DataTableOperation.Rows(i).Item(5)
 
 
                reader = CmdVerifDoublons.ExecuteReader()
 
                If reader.HasRows Then
 
                    'Console.WriteLine(vbTab & reader.GetName(0) & vbTab)
 
                    'Do While reader.Read()
                    '    Console.WriteLine(vbTab & reader.GetValue(0) _
                    '      & vbTab & "déja present" & vbCrLf)
                    'Loop
 
                Else
 
                    Console.WriteLine(vbTab & DataTableOperation.Rows(i).Item(5) & vbTab & "rajouté" & vbCrLf)
 
 
                    'je transforme en date format anglais 
                    BonneDate = Date.ParseExact(DataTableOperation.Rows(i).Item(3), "yyyyMMdd", Nothing)
 
                    Cmd.Parameters("@p1").Value = DataTableOperation.Rows(i).Item(1)
                    Cmd.Parameters("@p2").Value = DataTableOperation.Rows(i).Item(2)
                    Cmd.Parameters("@p3").Value = BonneDate
                    Cmd.Parameters("@p4").Value = Replace(DataTableOperation.Rows(i).Item(4), ".", ",")
                    Cmd.Parameters("@p5").Value = DataTableOperation.Rows(i).Item(5)
                    Cmd.Parameters("@p6").Value = DataTableOperation.Rows(i).Item(6)
                    Cmd.Parameters("@p7").Value = DataTableOperation.Rows(i).Item(7)
 
                    Cmd.ExecuteNonQuery()
                End If
                reader.Close()
 
            Next
 
            'fermeture de la connection
            MyConnexion.Close()
            MyConnexion = Nothing

Tomlev
tu as vu j'ai utilisé une autre requete paramétrée cette fois, tu as raison c'est quand même beaucoup mieux que ce que je faisais

Pour vous c'est la bonne méthode? il n'y a pas plus rapide?

En tout cas merci a tous les deux d'avoir pris le temps de m'aider.

**Pol63** · 29/08/2011, 15h03

mettre les nouvelles lignes dans une table temporaire
puis exécuter la requete suivante

Code :

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

1
2
3
4
5
INSERT INTO vraietable (colonne1, colonne2 etc...) 
    SELECT tabletemp.colonne1, tabletemp.colonne2 etc...
        FROM tabletemp 
            LEFT JOIN vraietable ON vraietable.colonne_pas_doublon_voulu = tabletemp.colonne_pas_doublon_voulu
        WHERE vraietable.colonne_pas_doublon_voulu IS NULL

il faut un index sur colonne_pas_doublon_voulu dans vraietable

**tomlev** · 29/08/2011, 15h55

le coup de la table temporaire c'est un peu bourrin, mais effectivement ça devrait marcher... et ça devrait être nettement plus rapide que la solution actuelle

**Pol63** · 29/08/2011, 15h59

bourrin je sais pas

et je ne connais pas trop access, mais sur sql server il y a en effet de fortes chances que ca soit la méthode la plus rapide (enfin avec une table @ pas #)

**gilles_906** · 29/08/2011, 16h15

Ta table temporaire serait sur acces? c'est bien ca?

Se serait la meme que la table qui contient toutes les opérations? mais avec toutes les nouvelles (doublons y compris)

Ensuite tu fais executer une requete qui se trouverai sur acces pour virer les doublons puis remplir la table de stockage?

J'ai bien compris?

Eviter les doublons dans une base access

VB.NET

Discussions similaires

Partager

Partager