Optimisation Script PHP

Version imprimable

Bonjour,

Je sollicite votre aide dans le cadre d'une réflexion sur un algorithme (script PHP).

J'ai une TABLE (Microsoft SQL Server 2008) "IDENTITE" constituée des colonnes suivantes :
Code:

1 2 3 4 5 6 7 8 9 CREATE TABLE [IDENTITE]( [IDI_ID] [int] IDENTITY(1,1) NOT NULL, [PK] [IDI_F1] [varchar] NULL, [IDI_F2] [varchar](25) NULL, [IDI_M1] [varchar](25) NULL, [IDI_M2] [varchar](25) NULL, [IDI_FORMULA] [varchar](25) NULL, [IDI_NAME] [varchar](25) NULL )
La colonne "IDI_ID" est l'identifiant unique de la table (identity).

Les colonnes "IDI_F1", "IDI_F2", "IDI_M1", "IDI_M1" sont les parents potentiels de mon identifiant.

La colonne "IDI_FORMULA" est la combinaison des colonnes parents : IDI_F1.IDI_F2/IDI_M1.IDI_M2 ou IDI_F2.IDI_F1/IDI_M2.IDI_M1

La colonne "IDI_NAME" est un nom commun que l'on attribut à mon identifiant.

Objectif :

L'objectif du script est de rechercher si les parents passés en paramètres de mon script existe dans la table "IDENTITE", sachant qu'il faut tester toutes les combinaisons possibles, soit 8 avec au minimum deux parents et au maximum 4 parents.

Exemple :

Dans la table, j'ai la ligne suivante :
IDI_ID : 		198587
IDI_F1 :		TITI
IDI_F2 :		TUTU
IDI_M1 :		TOTO
IDI_M2 :		TKTK
IDI_FORMULA :	TITI.TUTU/TOTO.TKTK
IDI_NAME	:	JML
Je veux donc rechercher cette ligne mais les paramètres passés sont :
IDI_F1 :		TUTU
IDI_F2 :		TITI
IDI_M1 :		TKTK
IDI_M2 :		TOTO
IDI_FORMULA :	TUTU.TITI/TKTK.TOTO
La formule est inversée donc je ne trouverai jamais ma ligne et pourtant elle existe bien.

Je pensais donc pour un paramètre, tester 8 requêtes avec des wheres représentants les 8 combinaisons possibles ou sinon faire un where avec les 8 combinaisons imbriquées par un OR.

Sachant que la table fait environ 800 000 lignes et que la recherche peut contenir 1000 formules à identifier ... Je souhaiterai un peu de votre aide :) !

Si vous voulez plus de précisions, je me tiens à votre disposition.

Cordialement

06/02/2014, 23h37
ryan

Hola,

Si j'ai bien compris ton problème (mais rien n'est moins sur) , quelque chose du genre devrait aider:

Code:

SELECT * FROM IDENTITE WHERE IDI_FORMULA LIKE '%TITI%' AND IDI_FORMULA LIKE '%TUTU%' AND IDI_FORMULA LIKE '%TKTK%' AND IDI_FORMULA LIKE '%TOTO%'

Ryan

Bonjour,

En fait, l'utilisateur va passer en paramètre une liste de parents soit F1, F2, M1, M2 correspondant à une formule F1.F2/M1.M2. Les parents peuvent être saisi dans un ordre différent que celui référencé dans la table IDENTITE.

Par exemple dans la table, je peux avoir dans la table F2.F1/M2.M1 et l'utilisateur tente de rechercher F1.F2/M1.M2.

Idée 1 :

Je pense donc parcourir l'ensemble des formules à rechercher et composer un ensemble de requêtes comme cela (pour chaque formule à rechercher), si un numéro existe je le retourne :
Code:

1 2 3 4 5 6 7 8 SELECT * FROM IDENTITE WHERE IDI_F1 = 'TUTU' AND IDI_F2 = 'TITI' AND IDI_M1 = 'TOTO' AND IDI_M2 = 'TKTK' SELECT * FROM IDENTITE WHERE IDI_F1 = 'TUTU' AND IDI_F2 = 'TITI' AND IDI_M1 = 'TKTK' AND IDI_M2 = 'TOTO' SELECT * FROM IDENTITE WHERE IDI_F1 = 'TITI' AND IDI_F2 = 'TUTU' AND IDI_M1 = 'TKTK' AND IDI_M2 = 'TOTO' SELECT * FROM IDENTITE WHERE IDI_F1 = 'TITI' AND IDI_F2 = 'TUTU' AND IDI_M1 = 'TOTO' AND IDI_M2 = 'TKTK' SELECT * FROM IDENTITE WHERE IDI_F1 = 'TOTO' AND IDI_F2 = 'TKTK' AND IDI_M1 = 'TUTU' AND IDI_M2 = 'TITI' SELECT * FROM IDENTITE WHERE IDI_F1 = 'TOTO' AND IDI_F2 = 'TKTK' AND IDI_M1 = 'TITI' AND IDI_M2 = 'TUTU' SELECT * FROM IDENTITE WHERE IDI_F1 = 'TKTK' AND IDI_F2 = 'TOTO' AND IDI_M1 = 'TUTU' AND IDI_M2 = 'TITI' SELECT * FROM IDENTITE WHERE IDI_F1 = 'TKTK' AND IDI_F2 = 'TOTO' AND IDI_M1 = 'TITI' AND IDI_M2 = 'TUTU'
Idée 2 :

Je pense exécuter une seule requête avec un WHERE contenant toutes les combinaisons :
Code:

1 2 3 4 5 SELECT * FROM IDENTITE WHERE (IDI_F1 = 'TUTU' AND IDI_F2 = 'TITI' AND IDI_M1 = 'TOTO' AND IDI_M2 = 'TKTK') OR (IDI_F1 = 'TUTU' AND IDI_F2 = 'TITI' AND IDI_M1 = 'TKTK' AND IDI_M2 = 'TOTO') OR (IDI_F1 = 'TITI' AND IDI_F2 = 'TUTU' AND IDI_M1 = 'TKTK' AND IDI_M2 = 'TOTO') ...
@ +

07/02/2014, 10h29
ryan

Hola,

Une chose qui n'est pas claire pour moi: est-ce que le champ IDI_FORMULA est complété pour chaque enregistrement de la table? Et si oui, est-ce que ce champ contient systématiquement les 4 parents?

Ryan
07/02/2014, 10h47
grunk

Quel rapport avec PHP ?
Ton message ne serait pas mieux coté SQL ? (je demande avant de déplacer)
07/02/2014, 11h35
JmL40

Bonjour,

Le champ formula n'est pas systématiquement renseigné, et un NUMERO n'a pas obligatoire 4 parents donc je peux avoir des formules du genre IDI_F1/IDI_M1 ...

C'est un problème PHP et SQL car je vais parcourir toutes les formules à rechercher en paramètre dans mon script PHP, et exécuter l'une des deux requêtes proposées dans une boucle. Je crains donc pour les performances.

Je voulais avoir vos avis à la fois sur le script PHP et la requête SOL.

Cordialement

Hola,

J'ai bien peur que tu ne coupes pas à tester toutes les combinaisons dans ton query. En effet, je ne pense pas que tout ramener (800000 lignes quand même) et faire les tests en PHP soit efficace.

A tester: une concaténation des champs IDI_F1, IDI_F2, IDI_M1 et IDI_M2 dans la clause WHERE et un test avec l'opérateur LIKE.
Code:

1 2 SELECT * FROM IDENTITE WHERE IDI_F1+IDI_F2+IDI_M1+IDI_M2 LIKE '%TOTO%' AND DI_F1+IDI_F2+IDI_M1+IDI_M2 LIKE '%TUTU%' AND DI_F1+IDI_F2+IDI_M1+IDI_M2 LIKE '%TITI%' AND DI_F1+IDI_F2+IDI_M1+IDI_M2 LIKE '%TKTK%'
Voir deux choses: si la concaténation est possible dans la clause WHERE et quel est l'opérateur de concaténation dans SQL Server.

Enfin, la dernière solution, si tu peux faire des modifs à la structure de la table IDENTITE, serait de regrouper le contenu des champs IDI_F1, IDI_F2, IDI_M1 et IDI_M2 dans un seul champ, avec un séparateur quelconque, et de faire autant de LIKE sur ce champ que tu n'as de parents connus dans ton query.

07/02/2014, 16h45
JmL40

Bonjour,

Je pourrais par exemple mettre en place une colonne calculée dans la table qui, pour chaque lignes concatène F1, F2, M1, M2.

Ensuite dans mon "query", je recherche la combinaison passée en paramètre dans la colonne calculée.

D'après vous, est-ce une bonne solution ?

Cordialement
07/02/2014, 18h39
ryan

Hola,

Je pense en effet que le champ qui rassemble les données des parents va faciliter et accélérer les choses.

Prévois un séparateur, qui peut être un espace, mais un caractère qui ne sera jamais présent dans les noms des parents offre plus de sécurité.

Ensuite un query sur le champ concaténé, avec un LIKE sur le nom de chaque parent à rechercher, précédé et suivi d'une wildcard, ça devrait le faire.

Autre solution: l'opérateur IN dans la clause WHERE:

Code:

SELECT * FROM IDENTITE WHERE IDI_F1 IN { 'toto', 'tutu', 'titi', 'tktk' } OR IDI_F2 IN { 'toto', 'tutu', 'titi', 'tktk' } OR IDI_M1 IN { 'toto', 'tutu', 'titi', 'tktk' } OR IDI_M2 IN { 'toto', 'tutu', 'titi', 'tktk' }

Faudrait faire un benchmark et voir ce qui est le plus rapide. Je crois me souvenir que l'opérateur LIKE n'est pas réputé pour sa rapidité...

Ryan
12/02/2014, 09h52
JmL40

Bonjour,

Merci pour vos réponses, je vais développer la solution du champ calculé avec le wildcard ...

Je ne clôture pas le sujet, je reviens vers vous dans peu de temps !

Cordialement
06/05/2014, 18h40
tse_jc

Bonjour,

Pour le faire simplement et d'une manière optimisé, il vous faut refaire d'abord votre modèle.
Vu, je suppose que F=Father et M=Mother, il faudrait d'abord commencer par référencer de manière unique chaque homme de votre base de donnée avec un identifiant, et de même pour chaque femme. Je vous laisse le soin de le faire, ce forum n'étant pas le bon pour vous faire une suggestion.
Ensuite faire une table de jonction pour les hommes et pour les femmes avec leur identifiants respectifs en relation avec votre table identité (attention aux mots réservés de plus) est le plus simple et vous évitera une volumétrie creuse.
Je rappelle ensuite que c'est le rôle de PHP de mettre en forme les paramètres de manière à ce qu'ils soient transmis de façon correcte au SGBDR pour le traitement approprié.

++