Discussion :

[Alex35]

Bonjour,

Voilà dans le cadre d'une newsletter qui présente les annonces en rapport avec les choix laissés par les internautes, je cherche à construire un web mail.

Donc nous avons les tables suivantes :
internautes (email, prix, departement, transaction, type de bien)
annonce (annonce_id, prix, #insee_id, promo, transaction, type de bien)
insee(insee_id, ville, dep, region)
webmail(#annonce_id,#email,prix,codepos,ville,transaction,type de bien_descriptif, Promo, region)

Le truc est de mettre dans une table pour mon webmail les annonce correspondant au critères retenu par l'internaute avec un prix a +ou- 20%

Pour cela j'ai cette requête qui fonctionne :

Code :

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

1
2
3
4
5
6
7
INSERT INTO webmail(annonce_id,email,prix,codepos,ville,transaction,type de bien, Promo, region) 
SELECT A.annonce_id,I.email,A.prix,Ii.codepos,Ii.Ville,A.transaction,A.type de bien, CASE WHEN Promo = 1 THEN 2 ELSE 0 END,  Ii.region
FROM annonces A INNER JOIN insee Ii WITH(NOLOCK) ON Ii.insee_id=A.insee_id
INNER internautes I WITH(NOLOCK) ON I.transaction=A.transaction 
AND I.type de bien=A.type de bien 
AND SUBSTRING(A.insee_id,1,2)=I.departement 
WHERE A.prix BETWEEN (I.prix*0.8) AND (I.prix*1.2)

Cette requête fonctionne avec un temps de traitement tout a fait acceptable.

Par contre là ou cela se complique c'est que si pour mes emails dans ma table webmail je n'ai pas 5 promos, je dois élargir avec les promos à la région...

Et là j'ai essayé avec un curseur de la sorte :

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
DECLARE @NB_TG int
DECLARE @email varchar(200)
DECLARE @region varchar(200)
 
DECLARE Cu CURSOR FOR
	select count(nullif(tete_gondole, 0))as nb_TG, email, region FROM webmail GROUP BY email, region HAVING COUNT(*) < 5
OPEN Cu
 
FETCH NEXT FROM Cu INTO @NB_TG, @email, @region
 
WHILE @@Fetch_Status = 0
BEGIN
INSERT INTO webmail_tmp(annonce_id,email,prix,codepos,ville,transaction,type de bien, Promo, region) 
SELECT TOP(5 - @NB_TG) A.annonce_id,I.email,A.prix,Ii.codepos,Ii.Ville,A.transaction,A.type de bien, Promo,  Ii.region
FROM annonces A WITH(NOLOCK)  	
INNER JOIN insee Ii WITH(NOLOCK) ON Ii.insee_id=A.insee_id
AND Ii.region = @region
INNER JOIN webmail wm WITH(NOLOCK) ON email = @email
AND A.transaction_id = wm.tr_id
AND A.typebien_id = wm.tb_id	
WHERE A.prix BETWEEN (wm.prix_internaute*0.8) AND (wm.prix_internaute*1.2) 
AND A.Promo= 1
AND A.Annonce_id NOT IN (SELECT annonce_id FROM imex.dbo.webmail_tmp)
FETCH NEXT FROM Cu INTO @NB_TG, @email, @region
END
CLOSE Cu
DEALLOCATE Cu

Qui fonctionne (enfin je crois ) mais par contre qui est très très lente...

C'est pourquoi je fais appel à vos lumières pour tenter de m'aider à optimiser celà.

PS : le fait de placer les Promo du département avec une promo à 2 et celle de la région à 1 me permet ensuite de pouvoir afficher d'abord celle du département puis celle de la région avec un ORDER BY Promo DESC.

D'avance merci pour votre aide !

[SQLpro]

Une seule requête suffit :

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
SELECT TOP (5) *
FROM
(            
SELECT A.annonce_id, I.email, A.prix, Ii.codepos, Ii.Ville, 
       A.transaction, A.type de bien, I.Prix AS PrixDemande, 
       CASE WHEN Promo = 1 THEN 2 ELSE 0 END,  Ii.region,
       1 AS CAS 
FROM   annonces A 
       INNER JOIN insee Ii 
             ON Ii.insee_id=A.insee_id
       INNER internautes I 
             ON I.transaction=A.transaction 
                AND I.[type de bien]=A.[type de bien] 
                AND SUBSTRING(A.insee_id,1,2) = I.departement 
WHERE  A.prix BETWEEN (I.prix*0.8) AND (I.prix*1.2)
UNION ALL
SELECT A.annonce_id, I.email, A.prix, Ii.codepos, Ii.Ville, 
       A.transaction, A.[type de bien],  I.Prix AS PrixDemande, 
       Promo,  Ii.region,
       2 AS CAS 
FROM  annonces A 
      INNER JOIN insee Ii 
            ON Ii.insee_id=A.insee_id
               AND Ii.region = @region
      INNER JOIN webmail wm 
            ON email = @email
               AND A.transaction_id = wm.tr_id
               AND A.typebien_id = wm.tb_id	
WHERE A.prix BETWEEN (wm.prix_internaute*0.8) AND (wm.prix_internaute*1.2) 
AND A.Promo= 1
) AS T
ORDER BY CAS, ABS(Prix - PrixDemande)

Je note cependant de nombreuses erreurs et horreurs dans votre code :
1) l'emploi d'identifiants illicites comme nom de colonne. Exemple : Transaction (mot clef de SQL) ou "type de bien" (comporte des espaces)
2) l'emploi abusif et systématique du NOLOCK qui de toute façon est ignoré car vous faites un INSERT
extrait de l'aide en ligne :
"
Pour les instructions UPDATE ou DELETE : Cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.
"
3) le fait que vos clef étrangères ne portent pas le même nom que les clef primaire de référence. Exemple
typebien_id dans Annonce et tb_id dans Webmail

A +