Bonjour à tous, j'ai un soucis avec une requête SQL, je n'arrive pas à la formuler. Voici à quoi elle ressemble actuellement:
UPDATE caracteristiquesmaj INNER JOIN communes ON (communes.codeCommune=caracteristiquesmaj.com
AND communes.codeDsr=caracteristiquesmaj.dep)
SET caracteristiquesmaj.codeAprCommune = communes.codeApr;
Chaque combinaison de codeDsr et codeCommune de la table communes correspond à une unique combinaison de com et dep de la table caracteristiquesmaj, mais la requête prend énormément de temps et n'aboutit pas.
J'ai l'impression que SQL effectue toutes les combinaisons possibles et essaie de les relier ensuite, mais sachant que chacune de ces tables est très grande cela ne peut pas fonctionner car le nombre de combinaisons est beaucoup trop grand
Une idée pour que SQL vérifie les deux conditions AVANT de faire correspondre les données? Merci d'avance!
As tu des index posés sur les colonnes communes.codeCommune, caracteristiquesmaj.com, communes.codeDsr et caracteristiquesmaj.dep ?
(note : si une contrainte UNIQUE est posé sur l'une des colonnes, un index est automatiquement créé).
Mais de manière générale, ta problématique ne devrait pas exister. Tout simplement car une base de données bien normalisée ne doit pas avoir de redondance ! Ainsi, copier des valeurs d'une table dans une autre reviens forcément à violer la deuxième forme normale. La jointure existante (formée par le couple [caracteristiquesmaj.com caracteristiquesmaj.dep] suffit à retrouver les valeurs. Null besoin de les dupliquer !
Oui j'ai des index sur ces deux colonnes, le problème étant que c'est justement l'identifiant de la table commune que je voudrais copier dans la table caracteristiques.
Et oui je sais aussi que je pourrais y accéder directement, mais j'ai déjà écrit toute ma programmation en faisant en sorte de ne pas utiliser la table commune (ce qui ne viole pas la règle donc je crois). Cette table n'est utile que pour retrouver l'identifiant de la commune à insérer dans la table caractéristique lors de la mise à jour des données.
Pour que cela soit plus clair je vais résumer: je dispose d'une table communes avec (parmis d'autres colonnes) un identifiant unique (codeApr), un numéro de département et un code de commune. Chaque combinaison code de commune-département est unique, mais ces colonnes ne le sont pas indépendamment. D'un autre côté, j'ai une table caractéristiques, dans laquelle je retrouve les codes de communes et les département.
Et j'ai une colonne actuellement vide dans cette table caractéristiques, que je voudrais remplir avec les codeApr de la table communes.
Je n'ai malheureusement pas le temps de restructurer ma base de données dans le temps qui m'est imparti pour terminer mon projet, et je n'arrive pas à faire ejointure sur deux colonnes car je pense que sql les fait l'une après l'autre, et ma bdd étant trop grosse cela lui prend trop de temps de créer toutes les combinaisons possibles. Ma problématique est donc: y a t il une commande afin que sql vérifie l'égalité de deux colonnes différentes AVANT de procéder à la jointure de ces deux colonnes? Merci d'avance!
Ma problématique est donc: y a t il une commande afin que sql vérifie l'égalité de deux colonnes différentes AVANT de procéder à la jointure de ces deux colonnes?
En un mot : non. Ceci n'a aucun sens d'ailleurs puisque la jointure EST le test d'égalité d'au moins deux colonnes différentes ! Tu cherches la mauvaise solution, car tu poses le mauvais problème. Les problèmes de performances sont toujours dues à la manière de modéliser tes données. Si tu règles ce problème de modélisation, tu règles le problème.
jeremyblad a écrit:
D'un autre côté, j'ai une table caractéristiques, dans laquelle je retrouve les codes de communes et les département.
C'est ça qui pose problème. Si le couple numero-commune/departement est unique par commune, et que chaque caractéristiques ne fait référence qu'à une seule commune, alors seul l'identifiant unique de la commune est nécessaire dans la table des caractéristiques.
De ce fait seules les trois tables suivantes devraient exister (ici sous PostgreSQL) :
CREATE TABLE DEPARTEMENT (
id SERIAL,
name CHARACTER VARYING(64),
PRIMARY KEY(id)
);
CREATE TABLE COMMUNE (
id SERIAL,
code_apr CHARACTER VARYING(64) NOT NULL UNIQUE,
code_commune CHARACTER VARYING(64) NOT NULL,
departement_id INTEGER NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(departement_id) REFERENCES DEPARTEMENT(id),
UNIQUE(departement_id, code_commune)
);
CREATE TABLE COMMUNE_CARACTERISTIQUE (
id SERIAL,
commune_id INTEGER NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(commune_id) REFERENCES COMMUNE(id)
);
Ensuite, pour éviter d'avoir à modifier l'application pour utiliser ces tables, il faut créer une vue avec dans le SELECT les noms des colonnes telles qu'utilisées dans l'application pour retrouver le code APR comme si l'information était dans la table.
CREATE VIEW V_COMMUNE_CARACTERISTIQUE AS
SELECT COMMUNE_CARACTERISTIQUE.*, COMMUNE.code_apr as codeApr, DEPARTEMENT.name as nomDepartement
FROM COMMUNE_CARACTERISTIQUE
INNER JOIN COMMUNE
ON COMMUNE_CARACTERISTIQUE.commune_id = COMMUNE.id;
INNER JOIN DEPARTEMENT
ON COMMUNE.departement_id = DEPARTEMENT.id;
L'application n'a plus alors qu'à faire sa recherche dans la vue. Exemple :
SELECT *
FROM V_COMMUNE_CARACTERISTIQUE
WHERE codeApr = ?
J'ai déjà essayé une solution de ce type mais le problème reste au niveau de la jointure: comme SQL effectue les jointures l'une après l'autre, il commence dans ce cas par associer toutes les communes portant le numéro x à toutes les caractéristiques dont le champ commune comporte le numéro x, en créant ainsi toutes les combinaisons possibles.
Votre solution fonctionne sûrement sur une petite table, mais sur une table de grande taille comme la mienne c'est bien trop long.
C'est pour cela que je voulais ajouter un identifiant de communes unique dans la table caractéristiques, au lieu d'avoir ce couple d'identifiants
comme SQL effectue les jointures l'une après l'autre, il commence dans ce cas par associer toutes les communes portant le numéro x à toutes les caractéristiques dont le champ commune comporte le numéro x, en créant ainsi toutes les combinaisons possibles.
Bien sur ! Comment pourrait-il procéder autrement ?!
jeremyblad a écrit:
Votre solution fonctionne sûrement sur une petite table, mais sur une table de grande taille comme la mienne c'est bien trop long.
C'est pour cela que je voulais ajouter un identifiant de communes unique dans la table caractéristiques, au lieu d'avoir ce couple d'identifiants.
Cela démontre surtout une méconnaissance totale des SGBDR !
Pour lire des informations un SGBDR n'a que trois solutions à sa disposition:
- un full scan de la table
- un full scan de l'index
- une recherche dans un index + lecture des pages associées dans la table
(- et dans une certaine mesure, une recherche dans un index seul, en cas d'index couvrant)
La solution que je propose permet, à l'aide d'une modélisation correcte, de profiter au maximum des jointures et des index associés à leur colonnes, afin que le moteur puisse choisir le meilleur algorithme de recherche possible. C'est le meilleure moyen d'avoir de TRES BONNES performances, même sur une table contenant des milliards de lignes ( pour info, la base de données que j'administre dans le cadre de mon travail, c'est 4 milliards de lignes).
Toutefois, il faut bien comprendre qu'un index ne peut être utiliser que pour une recherche. C'est évident dit comme ça... mais ça implique qu'une requête sans clause WHERE ne peut pas être optimisée, et que son exécution implique un full scan de la table avec un coût exponentiel pour chaque jointure.
Il ne faut donc JAMAIS faire de requêtes sans clause WHERE. Si on veut remonter de très large quantité de données, il faut utiliser la pagination.
Ainsi, faire un UPDATE de toutes les caractéristiques impose un full scan des deux tables. C'est pourquoi il n'y a pas de solution: il ne faut pas rajouter une valeur redondante.
philodick a écrit:
Question bête mais est-ce que le serveur est bien configuré pour accorder au SGBD la mémoire nécessaire ?
Effectivement, c'est bête.... Si la base de données pèse 250Go (data + index), il est impossible d'allouer suffisamment de RAM pour faire les opérations en mémoire. Comme l'auteur indique qu'il dispose de grosses tables, je suppose donc que la taille de la BDD dépasse la quantité de RAM disponible.
Il n'est pas question de tout charger en mémoire, mais pour autant la taille de la mémoire allouée au serveur de base de données peut être limitée dans la config (et l'est très souvent par défaut) , et c'est trop souvent ignoré.
C'est bête seulement si ça été fait correctement...
Pas tant que ça en fait. Car il faut bien comprendre que toute manipulation de données se fait en RAM, ce qui implique de devoir charger les données sur disque en RAM, de faire le traitement, puis de les décharger.
Alors certes, on peut augmenter la quantité totale alloué au SGBDR, pour justement éviter de trop charger/décharger les données. C'est surtout la quantité de RAM disponible par requête qui est important (voir innodb_buffer_pool_size pour MySQL et work_memory pour PostgreSQL ). Sur ces points, tu n'as pas tord, mais s'il faut lire 250Go de données, il faudra toute les charger / décharger. Régler la quantité de RAM ne changera que la taille des blocs lus (des gros moins nombreux, ou des petits plus nombreux). Mais peu importe, puisqu'il faudra tous les lire quoi qu'il arrive. Et à ce petit jeu, c'est toujours le disque dur le facteur limitant.
Par ailleurs, si tu alloues 8Go de RAM à une base de données de moins de 8Go, elle se retrouvera intégralement en RAM. Elle est donc chargée, et jamais déchargée ! Le SGBDR ne procédera plus qu'aux écritures dans le journal de transaction, les tables, et les index.
Seulement là, il veut faire un UPDATE de toute ses lignes ! Ce qui implique une transaction, et donc une écriture dans le journal (et donc le disque) pour CHAQUE ligne ! Ainsi, la quantité de RAM alloué n’aura que peu d'instance au final. Même avec un disque SSD, le coût est pharaonique...
Oui effectivement je n'ai pas beaucoup (voire très peu) de connaissances en bases de données, j'ai commencé à assimiler ces notions il y a 4 mois dans le cadre de mon stage.
Et je suppose que pour gérer des milliards de lignes, vous avez des machines très puissantes? L'association pour laquelle je travaille ne dispose de moyens que très limités et ne dispose pas de beaucoup de matériel.
Il y aurait peut être une autre solution: le code apr commune est composé du département, suivi d'un '#', et du numéro de la commune.
Désolé d'avance pour cette expression si elle viole toutes les lois de sql, mais y aurait il un moyen de dire à SQL "Pour chaque ligne, prendre le département, ajouter un # et le numéro de la commune"?
Sinon, la seule solution qu'il me reste est de demander à l'utilisateur de se débrouiller pour rentrer les codes de communes au préalable..
Et je suppose que pour gérer des milliards de lignes, vous avez des machines très puissantes
Pas plus qu'un bon PC actuel (4 Core i5, 16Go RAM, HDD 1 To )
jeremyblad a écrit:
mais y aurait il un moyen de dire à SQL "Pour chaque ligne
A partir du moment où tu demande pour toutes les lignes, il n'y a pas d'optimisation possible.
Ton problème, c'est que tu regardes par le mauvais coté de la lorgnette. Tu essayes de trouver une solution à un problème qui en fait n'existe pas. Plutôt que de vouloir enregistrer ces codes communes dès maintenant, demande toi plutôt quand tu en a besoin.
Si tu veux une solution, explique le vrai fond de ton problème: pourquoi souhaites-tu redonder l'information du code commune dans les caractéristiques ?
Problème INNER JOIN sur deux conditions SQL
× Après avoir cliqué sur "Répondre" vous serez invité à vous connecter pour que votre message soit publié.
× Attention, ce sujet est très ancien. Le déterrer n'est pas forcément approprié. Nous te conseillons de créer un nouveau sujet pour poser ta question.
N'oubliez pas d'activer les erreurs PDO.
N'oubliez pas d'activer les erreurs PDO.