Bonjour,

J'ai un petit problème avec une requête :
Je dois récupérer le nombre d'utilisateur ainsi que la moyenne du montant par groupe.
Les groupes sont calculés à partir du Montant totale par semaines et par sport du montant jouer par les utilisateurs.
Cette requête me renvoi exactement ce que je recherche mais est bien trop lente

Pourriez vous m'aider s'il vous plait à l'optimiser?
Je suis sur SQL Serveur 2008 R2.

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
SELECT t3.ApplicationId
      ,t3.ChannelId 
      ,t3.YearDate
      ,t3.WeekDate
      ,t3.Sport
      ,t3.StepNum
      ,t3.StepMin
      ,t3.StepMax 
      ,t3.nbusers
      ,(t3.AverageTurnover/t3.nbusers) AS AverageTurnover
FROM(
    SELECT 
    ApplicationId
          ,ChannelId 
          ,YearDate
          , WeekDate
          , Sport
          , StepNum
          , StepMin
          , StepMax 
          , (SELECT count(DISTINCT t.user_id) FROM 
                (
                    SELECT s2.user_id , SUM(s2.montant_eur) AS montant FROM stats_paris s2 
                    LEFT JOIN Channels c ON c.Id = s2.ChannelId
                    WHERE 
                    s2.datfin >= {d '2010-07-05'} 
                    AND s2.datfin < {d '2010-07-12'} 
                    AND s2.sport_trad = Sport
                    AND c.ApplicationId = 2
                    GROUP BY  s2.user_id
                ) AS t
                WHERE t.montant <= t2.StepMax 
                AND t.montant >= t2.StepMin
 
            ) AS nbusers,
 
            (SELECT sum(t.montant) FROM 
                (
                    SELECT SUM(s2.montant_eur) AS montant FROM stats_paris s2 
                    LEFT JOIN Channels c ON c.Id = s2.ChannelId
                    WHERE 
                    s2.datfin >= {d '2010-07-05'} 
                    AND s2.datfin < {d '2010-07-12'} 
                    AND s2.sport_trad = Sport
                    AND c.ApplicationId = 2
                    GROUP BY  c.ApplicationId,DATEPART(WEEK, s2.datfin),s2.sport_trad, s2.user_id
 
                ) AS t
            WHERE t.montant <= t2.StepMax 
            AND t.montant >= t2.StepMin
            ) AS AverageTurnover
    FROM (
        SELECT
              ApplicationId
              ,ChannelId 
              ,YearDate
              , WeekDate
              , Sport
              , StepTO
              , StepNum
              , StepMin = ROUND(CASE StepNum WHEN 1 THEN 0 WHEN 2 THEN StepTO WHEN 3 THEN StepTO * 2 WHEN 4 THEN StepTO * 3  WHEN 5 THEN StepTO * 4 WHEN 6 THEN StepTO * 5 WHEN 7 THEN StepTO * 6 WHEN 8 THEN StepTO * 7  WHEN 9 THEN StepTO * 8 WHEN 10 THEN StepTO * 9  END,2)
              , StepMax = ROUND(CASE StepNum WHEN 1 THEN StepTO WHEN 2 THEN StepTO * 2 WHEN 3 THEN StepTO * 3 WHEN 4 THEN StepTO * 4 WHEN 5 THEN StepTO * 5 WHEN 6 THEN StepTO * 6 WHEN 7 THEN StepTO * 7 WHEN 8 THEN StepTO * 8 WHEN 9 THEN StepTO * 9 WHEN 10 THEN StepTO * 10 END,2) FROM
              (
              SELECT
                    c.ApplicationId
                    ,s.ChannelId
                    ,DATEPART(YEAR, s.datfin)  AS YearDate
                    ,DATEPART(WEEK, s.datfin) AS WeekDate
                    ,s.sport_trad AS Sport
                    ,SUM(s.montant_eur)/10 AS [StepTO]
                    ,t.ID StepNum
              FROM 
                    stats_paris AS s WITH (NOLOCK)
                    LEFT JOIN Channels c ON c.Id = s.ChannelId
                    CROSS JOIN (SELECT  TOP 10 ROW_NUMBER() OVER (ORDER BY id) AS ID FROM ADMIN WITH (NOLOCK)) t
              WHERE 
                    s.datfin >= {d '2010-07-05'} AND s.datfin < {d '2010-07-12'} 
              AND c.ApplicationId = 2 
              AND s.sport_trad IS NOT NULL
              GROUP BY 
                    c.ApplicationId,s.ChannelId,DATEPART(YEAR, s.datfin),DATEPART(WEEK, s.datfin) ,s.sport_trad,t.ID 
              ) TOSportStep     
        ) AS t2
    ) AS t3
ORDER BY ApplicationId, ChannelId, YearDate, WeekDate, Sport,StepNum, nbusers
Merci d'avance