Aide à L'optimisation d'une requete

**psyrio** · 11/10/2010, 17h33

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

**Waldar** · 11/10/2010, 18h25

Pas étonnant vu le nombre d'imbrication et de requête scalaire.

Là pour vous aider par contre il faut publier un jeu de test (une dizaine de lignes par table, sans oublier les scripts de création / insertion) avec ce que vous voulez obtenir : les règles de calculs + résultat attendu.

Mais d'emblée je suis sûr qu'on peut diviser par dix.

**SQLpro** · 11/10/2010, 21h27

Commencez par respecter la charte de postage : http://www.developpez.net/forums/d96...vement-poster/

En postant notamment le DDL de vos tables ainsi que des index.

A +

**psyrio** · 12/10/2010, 10h25

Bonjour,

Merci pour votre retour voici les informations que j'ai pu recuperer:

stats:

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
 
CREATE TABLE [dbo].[stats_paris](
	[mise_id] [int] NOT NULL,
	[typmise] [char](3) NOT NULL,
	[datfin] [datetime] NULL,
	[user_id] [int] NOT NULL,
	[sport_trad] [varchar](30) NULL,
	[montant_eur] [decimal](10, 2) NOT NULL,
	[ChannelId] [smallint] NULL,
 CONSTRAINT [PK_stats_paris] PRIMARY KEY CLUSTERED 
(
	[typmise] ASC,
	[mise_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

et Channels :

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 TABLE [dbo].[Channels](
	[Id] [smallint] IDENTITY(1,1) NOT NULL,
	[ApplicationId] [bigint] NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[IsActive] [bit] NOT NULL,
	[TokenKey] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Channels] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
 
ALTER TABLE [dbo].[Channels]  WITH CHECK ADD  CONSTRAINT [FK_Channels_Applications] FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[Applications] ([Id])
GO
 
ALTER TABLE [dbo].[Channels] CHECK CONSTRAINT [FK_Channels_Applications]

Applications:

Code :

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

1
2
3
4
5
6
7
8
9
10
 
CREATE TABLE [dbo].[Applications](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_Applications] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Voici les tables comma j'ai pu les trouvés.
Concernant les données je vous les transferts dans le fichier Excel suivant (chaque onglet représente une table)

Je n'ai pas accès aux index.

Concernant les règles de calculs:
Je dois créer pour chaque semaine, pour chaque sport :
- 10 groupes numéroté de 1 à 10 : Chacun de ces groupes à une valeur min et une valeur max qui correspond à ses bornes de valeurs.
On les calculs en faisant la somme totale pour chaque semaine et chaque sport des montant_eur de tous les utilisateurs et en splittant ce total en 10 part égales.
- Ensuite pour chaque groupe de chaque sport de chaque semaine on calcul le nombre d'utilisateur ayant fait un total résultat (la somme des résultats de ce joueur pour la semaine et le sport données) étant dans la tranche du groupe.
- Puis on calcul la moyenne du résultat en fonction du nombre d'utilisateurs dans le groupe.

J'espère avoir été assez explicite.

Concernant les rendus vous trouverez le fichier excel qui vous montrera le type de résultat que je souhaiterais.

J'espère que vous pourrez m'aider

Merci d'avance.

**Waldar** · 12/10/2010, 11h38

Oui mais sans les données de départ ça va être compliqué.

Je veux bien aider, mais le travail de mise en place c'est à vous de le faire.

Si vous n'avez pas accès aux scripts, vous êtes toujours en mesure de les écrire à la main !

Regarde l'étape #5 des règles du forum :
http://www.developpez.net/forums/a69...gage-sql-lire/

Si ce n'est pas exactement vos tables ce n'est pas grave, l'important c'est que la logique de vos données soit respectée, et qu'on puisse comparer avec votre requête actuelle.

**Waldar** · 12/10/2010, 15h59

Désolé pour l'ordre des messages, j'ai fusionné deux discussions en une.

psyrio, ok pour les tables, mais il manque encore les scripts permettant d'insérer les données.

**psyrio** · 12/10/2010, 16h33

Désoler pour le multipost mais je pensais que j'avais mal posté dans le forum SGBD.

Concernant ma demande j'ai vu un peu la définition de la spec et en fait on me demande un groupement par décile ce qui est differrent de la définitions que je vous ai donnée ultérieurement:

Pour regroupé les utilisateurs par decile on le fait grâce à un trie par valeur ascendante du turnover total par utilisateur et par sport, puis par la somme cumulé de ce turnover afin de classé les utilisateurs ds le bon décile.
Voici les script d'alimentation que j'ai créer j'espère qu'ils seront suffisant ?:

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
INSERT INTO [stats_paris]      ([mise_id]  , [datmise]  ,[typmise]    ,[datfin]      ,[user_id]      ,[sport_trad]      ,[montant_eur]      ,[ChannelId])
  VALUES     (1,    2010-06-05 09:07:00.667,    COM,    2010-06-05 13:37:04.437,    1,    Sport1,    1.00,    3)
INSERT INTO [stats_paris]      ([mise_id]   , [datmise] ,[typmise]    ,[datfin]      ,[user_id]      ,[sport_trad]      ,[montant_eur]      ,[ChannelId])
  VALUES     (2,    2010-06-05 11:09:03.647,    COM,    2010-06-11 16:58:38.160,    2,    Sport1,    20.00,    3)
INSERT INTO [stats_paris]      ([mise_id] , [datmise]   ,[typmise]    ,[datfin]      ,[user_id]      ,[sport_trad]      ,[montant_eur]      ,[ChannelId])
  VALUES     (3,    2010-06-05 12:26:19.310,    COM,    2010-06-05 15:53:14.373,    3,    Sport1,    1.00,    3)
INSERT INTO [stats_paris]      ([mise_id]  , [datmise]  ,[typmise]    ,[datfin]      ,[user_id]      ,[sport_trad]      ,[montant_eur]      ,[ChannelId])
  VALUES     (4,    2010-06-07 11:24:58.900,    COM,    2010-06-07 15:08:57.440,    4,    Sport1,    0.50,    3)
INSERT INTO [stats_paris]      ([mise_id]   , [datmise] ,[typmise]    ,[datfin]      ,[user_id]      ,[sport_trad]      ,[montant_eur]      ,[ChannelId])
  VALUES     (5,    2010-06-07 17:33:33.690,    COM,    2010-06-08 21:13:15.940,    5,    Sport1,    0.50,    3)
INSERT INTO [stats_paris]      ([mise_id] , [datmise]  ,[typmise]    ,[datfin]      ,[user_id]      ,[sport_trad]      ,[montant_eur]      ,[ChannelId])
  VALUES     (6,    2010-06-07 17:33:53.723,    COM,    2010-06-08 15:32:37.383,    6,    Sport2,    0.50,    3)
 
INSERT INTO [Applications]          ([Name]       ,[IsActive])   VALUES       (Application1       ,1) 
INSERT INTO [Applications]          ([Name]        ,[IsActive])   VALUES      (Application2      ,1) 
INSERT INTO [Channels] ([ApplicationId] ,[Name],[IsActive])  VALUES  (1, Channel1 ,1)
INSERT INTO [Channels] ([ApplicationId] ,[Name],[IsActive])  VALUES  (2, Channel2 ,1)
INSERT INTO [Channels] ([ApplicationId] ,[Name],[IsActive])  VALUES  (2, Channel3 ,1)

Merci pour votre aide

**Sergejack** · 13/10/2010, 11h33

Je pensais que quelqu'un aurait déjà fait la remarque mais non :

Vous avez des jointures externes (LEFT JOIN) vers certaines tables et vous faites références à ces tables ainsi-jointe dans vos clauses WHERE, c'est un non-sens.

Utilisez soit une jointure interne et ne changer rien d'autres, soit une jointure externe et mettez toutes vos conditions portant sur la table jointe dans la clause "ON".

**psyrio** · 13/10/2010, 12h00

Je vous remercie de votre réponse.

On m'a aussi conseillé d'utiliser une CTE: Voici ce que j'ai pu réaliser pour l'instant pour avoir un classement de mes utilisateurs par Turnover:

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
WITH 
    SportBets (Sport)
    as
    (select DISTINCT s2.sport_trad as Sport from stats_paris s2
    INNER JOIN Channels c on c.Id = s2.ChannelId
    WHERE  s2.datfin >= {d '2010-09-04'} 
        AND s2.datfin < {d '2010-09-11'} 
        AND c.ApplicationId = 2),
    TurnoverUser (Sport,User_id, Turnover)
    AS
    (select s2.sport_trad as Sport ,s2.user_id as User_id, SUM(s2.montant_eur) as Turnover
    FROM stats_paris s2 
    INNER JOIN Channels c on c.Id = s2.ChannelId
    WHERE s2.datfin >= {d '2010-09-04'} 
        AND s2.datfin < {d '2010-09-11'} 
        AND c.ApplicationId = 2
        GROUP BY  s2.sport_trad,s2.user_id),
    tree (Sport, User_id, Turnover)
    as
    (
    select Sport, User_id, Turnover
    FROM TurnoverUser T
    WHERE T.Sport = (select top 1 * from SportBets)
    UNION ALL
    select T.Sport, T.User_id, T.Turnover
    FROM TurnoverUser T
    INNER JOIN tree t1 on t1.Sport = T.Sport)
 
Select * from tree

Mais j'ai une erreur du type :

Code :

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

1
2
Msg 530, Level 16, State 1, Line 10
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Je ne maitrise pas du tout le CTE pourriez vous m'aidez s'il vous plait?

**Sergejack** · 19/10/2010, 14h13

Qu'est-ce que le turnover ?

Aide à L'optimisation d'une requete

Développement SQL Server

Discussions similaires

Partager

Partager