Discussion :

[sonik1st]

Bonjour,

Je dispose de fichiers plutôt volumineux que je dois charger dans différentes tables client réparties sur différentes opérations. Pour être plus clair, voici le processus :
1/ chargement d'un fichier brut dans une table fichierbrut
2/ chargement d'une partie de la table fichierbrut dans une table de production

Pour ne pas importer dans ma table de production des fiches déjà présentes, je vérifie que les fiches sélectionnées dans ma table fichierbrut ne sont pas déjà présentes dans ma table de production. Le critère de dédoublonnage est le n° de téléphone.

Sur une table de production relativement petite (<100.000 enregistrements), ça passe assez rapidement. Mais sur les tables + conséquentes (>700.000 enregistrements), ça rame et me provoque un timeout (plafonné à 20min) dans l'applicatif PHP qui lance la requête.

Voici l'environnement :
1/ SGBD : SQL-Server
2/ Environnement applicatif : Apache + PHP connecté au serveur SQL par pilote ODBC (je sais : "bof avec le PHP")
3/ J'ai mis un index sur le champ "telephone" de chacune des tables

Voici ma requête de pré-sélection :

Code :

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

1
2
3
4
5
6
7
UPDATE FichierBrut
SET Utilise=2
FROM FichierBrut f1,
 (SELECT TOP 10000 f.IdFicheBrut FROM FichierBrut f 
 LEFT JOIN Client c ON f.Telephone=c.Telephone 
 WHERE f.Utilise=0 AND c.Telephone IS NULL ORDER BY NEWID()) f2
WHERE f1.IdFicheBrut=f2.IdFicheBrut

Cette requête présélectionne 10000 fiches de la table FichierBrut (triées de manière aléatoire) en s'assurant que la valeur du champ téléphone n'est pas déjà présente dans la table Client

C'est cette requête qui met beaucoup de temps à s'exécuter. Quelqu'un aurait-il une autre méthode plus rapide pour effectuer la même chose ?

Pour info :
Ensuite, j'insère ces fiches dans la table Client, je recherche un indice de fichier non encore utilisé, pour que je distingue ce segment de fichier des autres segments. Puis j'exporte ces fiches dans un fichier csv à destination d'un partenaire. Cette dernière partie est longue aussi, mais c'est juste du au fait qu'il doive écrire ligne par ligne dans le fichier. Ce n'est donc pas mon problème du jour

[SQLpro]

je sais : "bof avec le PHP"

Contrairement à ce que vous pensez PHP est un excellent langage et un outil de développement remarquable. Même chez MS officieusement on ne sait pas faire mieux que PHP !

Est-te vous sur que le téléphone est écrit de manière strictement identique des deux côtés ?
Pour ma part, je commencerait par rectifier la qualité des données de chacune des tables en ajoutant une colonne PERISTED indexée avec nettoyage des caractères parasites. Utilisez la fonction que j'ai donné ici :
http://blog.developpez.com/sqlpro/p7...ction-de-corr/

Code :

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

dbo.F_RESCRIT (MaColonneTel, '012345679', '0123456789, '')

Mettez ensuite des index sur ces deux colonnes calculées et faites la jointure dessus.

En outre n'oubliez pas les SCHEMA sQL comme dbo, pour préfixez vos requêtes.

Ensuite je ferais tout ceci dans une procédure stockée plutôt que par des requêtes adhoc. Ceci réduira drastiquement les E/S entre serveurs.

Enfin, vos tables ont-elles toutes une clef primaire avec un index cluster dessus ? Sinon, l'ajouter

A +

[sonik1st]

Contrairement à ce que vous pensez PHP est un excellent langage et un outil de développement remarquable. Même chez MS officieusement on ne sait pas faire mieux que PHP !

Je me suis mal exprimé, c'était pas dans ce sens qu'il fallait lire ma parenthèse. Je suis fan du PHP !! C'est juste que je n'ai pas eut le choix du SGBD et en fait ce dont je doute en terme de compatibilité, ce sont les drivers ODBC, qui sont plus que pauvres sous PHP pour ce qui est des accès aux bases. Il y a d'ailleurs pas mal de bug, notamment dans la fonction qui retourne le nombre de résultat d'une requête.

Est-te vous sur que le téléphone est écrit de manière strictement identique des deux côtés ?
Pour ma part, je commencerait par rectifier la qualité des données de chacune des tables en ajoutant une colonne PERISTED indexée avec nettoyage des caractères parasites. Utilisez la fonction que j'ai donné ici :
http://blog.developpez.com/sqlpro/p7...ction-de-corr/

Code :

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

dbo.F_RESCRIT (MaColonneTel, '012345679', '0123456789, '')

Mettez ensuite des index sur ces deux colonnes calculées et faites la jointure dessus.

Oui, mes numéros de téléphones sont normalisés avant l'import. VARCHAR(10) avec toujours le 0 devant.

En outre n'oubliez pas les SCHEMA sQL comme dbo, pour préfixez vos requêtes.

Hmmm... càd ? En fait pour vous avouer un autre soucis, mes tables client et fichierbrut ne sont pas dans la même base. Je ne l'ai pas dit plus, pensant que cela alourdirai la compréhension de mon problème. Résultat, mes requêtes de sélection ressemblent plus à "SELECT TOP 10 * FROM baseproduction..Client WHERE ....", et dans ce cas je ne sais pas trop comment utiliser le dbo. Je ne sais même pas à quoi il sert (je suis sous MySQL d'habitude, si ça peut me servir d'excuse )

Ensuite je ferais tout ceci dans une procédure stockée plutôt que par des requêtes adhoc. Ceci réduira drastiquement les E/S entre serveurs.

Bonne idée, pourquoi n'y ai-je pas pensé !!?? Je teste et je vous dis

Enfin, vos tables ont-elles toutes une clef primaire avec un index cluster dessus ? Sinon, l'ajouter

Oui elles ont des clefs primaires, je ne peux donc pas ajouter de cluster...

A +

[SQLpro]

Je me suis mal exprimé, c'était pas dans ce sens qu'il fallait lire ma parenthèse. Je suis fan du PHP !! C'est juste que je n'ai pas eut le choix du SGBD et en fait ce dont je doute en terme de compatibilité, ce sont les drivers ODBC, qui sont plus que pauvres sous PHP pour ce qui est des accès aux bases. Il y a d'ailleurs pas mal de bug, notamment dans la fonction qui retourne le nombre de résultat d'une requête.

N'utilisez pas les fonctions ODBC qui sont une belle M... Utilisez dans la connexion la variable @@ROWCOUNT.

Hmmm... càd ? En fait pour vous avouer un autre soucis, mes tables client et fichierbrut ne sont pas dans la même base. Je ne l'ai pas dit plus, pensant que cela alourdirai la compréhension de mon problème. Résultat, mes requêtes de sélection ressemblent plus à "SELECT TOP 10 * FROM baseproduction..Client WHERE ....", et dans ce cas je ne sais pas trop comment utiliser le dbo. Je ne sais même pas à quoi il sert (je suis sous MySQL d'habitude, si ça peut me servir d'excuse )

Que vos données soient dans deux base n'a aucune importance. En revanche l'utilisation des schema SQL est important. venant du monde MySQL qui est un ersatz de SQL je comprend que vous ne sachiez pas ce que c'est. Lisez mon bouquin ou mon site web. C'est très important car un objet doit toujours être précédé de son préfixe de schéma !
Lisez ceci pour commencer : http://blog.developpez.com/sqlpro/p5...es-schema-sql/

A +

[sonik1st]

Bon, je viens de tester la procédure stockée... Ca n'arrange pas la durée de traitement. J'ai testé ma procédure stockée directement via l'analyseur de requête et elle m'a bouffé toutes les ressources du serveur (bloquant ma production par la même occasion ), j'ai donc du l'arrêter avant la fin.

Vu que ma requête fait appel à une table d'une autre base dont le nom doit être envoyé dynamiquement à ma procédure stockée. Voici ma procédure stockée

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
CREATE PROCEDURE SelectionFiche
       @Campagne VARCHAR(8),
       @Quantite INTEGER,
       @IdfichierMin INTEGER,
       @Qualite INTEGER,
       @AutreCritere VARCHAR(250)
AS
BEGIN
     EXECUTE(N'
     UPDATE FichierBrut
     SET Utilise=2
     FROM FichierBrut f1, (SELECT TOP '+@Quantite+N' f.IdFicheBrut
                           FROM FichierBrut f
                           LEFT JOIN '+@Campagne+N'..Client c ON f.Telephone=c.Telephone AND c.IdFichier>'+@IdFichierMin+N'
					       WHERE f.Qualite='+@Qualite+N' AND f.Utilise=0 AND c.Telephone IS NULL '+@AutreCritere+N'
                           ORDER BY NEWID()) f2
     WHERE f1.IdFicheBrut=f2.IdFicheBrut')
END

Je me demandais si une "vue" de la partie "(SELECT TOP...ORDER BY NEWID())" ne pourrait pas accélérer tout ça. Mais est-ce que je peux passer un paramètre à une vue et faire un execute dedans ?

N'utilisez pas les fonctions ODBC qui sont une belle M... Utilisez dans la connexion la variable @@ROWCOUNT.

Je voulais passer par la librairies mssql, mais bon... J'ai déjà bien entamé le projet avec ODBC. Et il semblerait que mes problèmes de lenteurs ne viennent pas de là

Que vos données soient dans deux base n'a aucune importance. En revanche l'utilisation des schema SQL est important. venant du monde MySQL qui est un ersatz de SQL je comprend que vous ne sachiez pas ce que c'est. Lisez mon bouquin ou mon site web. C'est très important car un objet doit toujours être précédé de son préfixe de schéma !
Lisez ceci pour commencer : http://blog.developpez.com/sqlpro/p5...es-schema-sql/

Au risque d'abuser, comment ajouter le schema dbo quand je fais un Campagne..Client ? De plus, est-ce que cela va accélérer le traitement ?

[sonik1st]

bon et bien j'ai viré la clef primaire de ma table client et n'y ai mis qu'un index classique. Puis j'ai transformé l'index du champ telephone en index avec cluster... Fiuuuuuuuu ultra rapide !! Merci pour les pistes de réflexion ! Merci Merci Merci Merci Merci Merci Merci Merci Merci Merci Merci Merci Merci Merci Merci

PS : toujours pas bien compris l'intérêt du schéma, à part pour passer sur un serveur qui n'aurait pas la même config d'encodage des chaines de caractères...