[AZURE] Faut-il simplifier la requête ?

Bonjour,

Je me pose une question plus ou moins générale quant au développement dans l'environnement AZURE de Microsoft.

Le coût des Sql Database sur Azure dépend des DTUs. Or, je ne sais pas trop comment est calculé un DTU (et d'après ce qu'on m'a dit on ne trouve pas la formule sur le net car Microsoft ajuste encore régulièrement le tir).

Du coup, vaut-il mieux faire une requête ensembliste complexe en db ou bien vaut-il mieux rapatrier le jeu de données en mémoire du service qui attaque la db et faire le traitement de manière itérative dans le service (service qui est aussi sur Azure et on paie au CPU utilisé là je crois :aie:)?

Quand je parle requête complexe, je parle de requête dans ce genre-ci (explications en-dessous):

Code:

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
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
DECLARE @CMP_ID UNIQUEIDENTIFIER = '1E5BF419-6AFD-4448-AC79-A12F9B24CCF2' ;DECLARE @TOOL_ID UNIQUEIDENTIFIER = 'EDB28E73-C26D-42BF-2775-08D757DAA44F';
DECLARE @DATE_FROM DATE = '20191201';
DECLARE @DATE_TO DATE = '20200131';
 
 
WITH
SITE_IN AS ( --Dates d'entrée entre les bornes
    SELECT    DISTINCT 
            TS.ToolId, 
            TS.SiteId, 
            TS.ValidationDate AS 'DATE_IN'
    FROM    Tools_Sites TS
                INNER JOIN TOOLS T
                    ON    TS.ToolId = T.Id
                    AND T.CompanyId = @CMP_ID
    WHERE    TS.IsLeaving = 0
        AND 1 = CASE
                    WHEN @TOOL_ID IS NULL THEN 1
                    WHEN TS.ToolId = @TOOL_ID THEN 1
                    ELSE 0
                END
        AND TS.ValidationDate BETWEEN @DATE_FROM AND DATEADD(DAY, 1, @DATE_TO)
    UNION ALL --Plus une date d'entrée à la date de la borne inférieure dans le cas où la sortie est entre les bornes mais pas l'entrée
    SELECT    TSA.TOOLID,
            TSA.SITEID,
            @DATE_FROM
    FROM    Tools_Sites TSA
                INNER JOIN TOOLS T
                    ON    TSA.ToolId = T.Id
                    AND T.CompanyId = @CMP_ID
    WHERE    TSA.IsLeaving = 1
        AND NOT EXISTS(    SELECT    1 
                        FROM    Tools_Sites TSB
                                    INNER JOIN TOOLS T
                                        ON    TSB.TOOLID = T.Id
                                        AND T.CompanyId = @CMP_ID
                        WHERE    TSB.IsLeaving = 0
                            AND TSB.ValidationDate <= TSA.ValidationDate
                            AND TSB.ToolId = TSA.ToolId
                            AND TSB.SiteId = TSA.SiteId
                            AND TSB.ValidationDate BETWEEN @DATE_FROM AND DATEADD(DAY, 1, @DATE_TO))
    AND 1 = CASE
                WHEN @TOOL_ID IS NULL THEN 1
                WHEN TSA.ToolId = @TOOL_ID THEN 1
                ELSE 0
            END
    AND TSA.ValidationDate BETWEEN @DATE_FROM AND DATEADD(DAY, 1, @DATE_TO)
),
SITE_OUT AS (--Dates de sorties entres les bornes
    SELECT    DISTINCT
            TS.TOOLID,
            TS.SITEID,
            TS.VALIDATIONDATE AS 'DATE_OUT'
    FROM    Tools_Sites TS
                INNER JOIN TOOLS T
                    ON    TS.ToolId = T.ID
                    AND T.CompanyId = @CMP_ID
    WHERE    IsLeaving = 1
    AND 1 = CASE
                WHEN @TOOL_ID IS NULL THEN 1
                WHEN TS.ToolID = @TOOL_ID THEN 1
                ELSE 0
            END
    AND TS.ValidationDate BETWEEN @DATE_FROM AND DATEADD(DAY, 1, @DATE_TO)
),
SITE_IN_OUT AS (
    SELECT    SI.TOOLID,
            SI.SITEID,
            SI.DATE_IN,
            (    
                SELECT    COALESCE(MIN(DATE_OUT), CAST(@DATE_TO AS DATE)) --Si pas de date de sortie, on prend la date de la borne supérieure
                FROM    SITE_OUT SO
                WHERE    SO.TOOLID = SI.TOOLID
                    AND SO.SITEID = SI.SITEID
                    AND SO.DATE_OUT >= SI.DATE_IN
            ) AS 'DATE_OUT'
    FROM    SITE_IN SI
),
SITE_DAYS_ALL AS (
    SELECT    TOOLID,
            SITEID,
            CAST(DATE_IN AS DATE) AS 'DATE_IN',
            CAST(DATE_OUT AS DATE) AS 'DATE_OUT',
            --DATEDIFF(DAY, CAST(DATE_IN AS DATE), CAST(DATE_OUT AS DATE)) + 1 AS 'DAYS_ON_SITE',
            ROW_NUMBER() OVER (ORDER BY TOOLID, SITEID, DATE_IN) AS 'RN'
    FROM    SITE_IN_OUT
),
SITE_DAYS_GROUPING AS (
    SELECT    TOOLID,
            SITEID,
            DATE_IN,
            DATE_OUT,
            1 AS 'GROUPING_COLUMN',
            RN
    FROM    SITE_DAYS_ALL A
    WHERE    RN = 1
    UNION ALL
    SELECT    B.ToolId,
            B.SITEID,
            B.DATE_IN,
            B.DATE_OUT,
            CASE
                WHEN B.DATE_IN = SDG.DATE_OUT THEN SDG.GROUPING_COLUMN
                ELSE SDG.GROUPING_COLUMN + 1
            END AS 'GROUPING_COLUMN',
            B.RN
    FROM    SITE_DAYS_ALL B
                INNER JOIN SITE_DAYS_GROUPING SDG
                    ON    B.RN = SDG.RN + 1
),
SITE_DAYS_GROUPED AS (
    SELECT    ToolId,
            SITEID,
            MIN(DATE_IN) AS 'DATE_IN',
            MAX(DATE_OUT) AS 'DATE_OUT',
            DATEDIFF(DAY, MIN(DATE_IN), MAX(DATE_OUT)) + 1 AS 'DAYS_ON_SITE'
    FROM    SITE_DAYS_GROUPING
    GROUP BY
            TOOLID,
            SITEID,
            GROUPING_COLUMN
)
SELECT    * 
FROM    SITE_DAYS_GROUPED
ORDER BY 
        SiteId, 
        DATE_IN

Sur le principe la requête n'est pas dure à comprendre. Il s'agit d'une requête sur une table de jointure entre une table d'outils et une table de sites dont voici la structure se trouve plus bas de manière à avoir le nombre de jour qu'un outil a passé sur un site.

Code:

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
CREATE TABLE [dbo].[Tools_Sites](    [Id] [uniqueidentifier] NOT NULL,
    [ToolId] [uniqueidentifier] NOT NULL,
    [SiteId] [uniqueidentifier] NOT NULL,
    [SiteLocationType] [int] NOT NULL,
    [IsCurrentAssignedSite] [bit] NOT NULL,
    [IsLeaving] [bit] NOT NULL,
    [ValidationDate] [datetimeoffset](7) NOT NULL,
    [Created] [datetimeoffset](7) NOT NULL,
    [CreatedByUserId] [uniqueidentifier] NOT NULL,
    [CreatedByUserName] [nvarchar](256) NOT NULL,
    [Modified] [datetimeoffset](7) NOT NULL,
    [ModifiedByUserId] [uniqueidentifier] NOT NULL,
    [ModifiedByUserName] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_Tools_Sites] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
 
ALTER TABLE [dbo].[Tools_Sites] ADD  CONSTRAINT [DF_Tools_Sites_Id]  DEFAULT (newsequentialid()) FOR [Id]
GO
 
 
ALTER TABLE [dbo].[Tools_Sites]  WITH NOCHECK ADD  CONSTRAINT [FK_Tools_Sites_SiteLocationTypes] FOREIGN KEY([SiteLocationType])
REFERENCES [dbo].[SiteLocationTypes] ([Id])
GO
 
 
ALTER TABLE [dbo].[Tools_Sites] CHECK CONSTRAINT [FK_Tools_Sites_SiteLocationTypes]
GO
 
 
ALTER TABLE [dbo].[Tools_Sites]  WITH NOCHECK ADD  CONSTRAINT [FK_Tools_Sites_Sites] FOREIGN KEY([SiteId])
REFERENCES [dbo].[Sites] ([Id])
GO
 
 
ALTER TABLE [dbo].[Tools_Sites] CHECK CONSTRAINT [FK_Tools_Sites_Sites]
GO
 
 
ALTER TABLE [dbo].[Tools_Sites]  WITH NOCHECK ADD  CONSTRAINT [FK_Tools_Sites_Tools] FOREIGN KEY([ToolId])
REFERENCES [dbo].[Tools] ([Id])
GO
 
 
ALTER TABLE [dbo].[Tools_Sites] CHECK CONSTRAINT [FK_Tools_Sites_Tools]
GO

Qu'en pensez-vous ?
Question annexe : Y a-t-il moyen de simplifier cette requête ? Il est possible que je sois passé à côté de quelque chose et qu'il y avait moyen de faire plus simple.

EDIT : Concernant la structure de la table de jointure, il s'agit d'un projet que je reprends et je n'ai pas la possibilité de la modifier pour le moment. La colonne de ValidationDate indique la date à partir de laquelle la ligne est active et la colonne IsLeaving indique si la ligne est une entrée ou une sortie du site. J'aurais préféré avec les colonnes DateIn et DateOut mais bon... J'dois faire avec ça.

Code:

1 2 3 4 5 CASE WHEN @TOOL_ID IS NULL THEN 1 WHEN TS.ToolId = @TOOL_ID THEN 1 ELSE 0 END
=>

Code:

CASE WHEN TS.ToolId = COALESCE(@TOOL_ID, TS.ToolId) THEN 1 ELSE 0 END

Déjà…

Si j'ai bien compris il y a un agrégat d'intervalle de date….

Si oui, ItZik Ben Gan a publié une jolie requête très performante évitant la récursivité pour ce faire….
https://blogs.solidq.com/en/sqlserve...ing-intervals/

A +

Hello.

Merci pour la réponse :).

Je note pour la simplification du CASE. C'est vrai que ça fait la même chose mais je trouve ça moins clair à la lecture... P-e une question d'habitude. C'est moins demandant en terme d'exécution ?

N.B. : Pour l'anecdote, tu noteras que la forme de CASE que j'utilise vient d'un de tes billets de blog de l'époque où je me formais en SQL :aie:.

Pour l'article, je vais aller lire ça :mrgreen:.

Citation:

Si oui, ItZik Ben Gan a publié une jolie requête très performante évitant la récursivité pour ce faire….
https://blogs.solidq.com/en/sqlserve...ing-intervals/

FYI,
A partir d’ORACLE 12c, la solution la plus élégante pour cet environnement, est MATCH_RECOGNIZE :
5 lignes suffisent pour le définir (partition by, order by, measures, pattern à seulement 2 variables, et 1 seul define nécessaire) ce qui rend le code trivial à lire et à adapter (tolérance d’intervalle, priorités comme évoqués dans les commentaires du blog cité, ...) et en plus (surtout) : un et un seul table scan.
Et toute la famille des problèmes liés aux intervalles se résout de manière similaire (gaps, split, ...), même si pour certains problèmes le pattern de la regexp n’est pas trivial...

(il existe aussi des solutions « one table scan » avec la clause MODEL mais sans doute moins lisibles... ou c’est une question d’habitude ou de goût... en tous cas en termes de nombre de lignes de code, elles sont plus longues)

Pour information, la norme SQL avait prévu des opérateurs spécifique pour cela comme PACK/COLLAPSE et à l'inverse EXPAND/UNPACK...

Mais à ce jour jamais implémenté ni finalement définis !

A +