Discussion :

[punkoff]

bah prenenz l'exemple que je vous ai donné ci-dessus.

La requête ne retourne rien alors qu'elle devrait retourner les 4 articles.

Et dans mon exemple les articles 1 à 4 font partie de l'unique table sur laquelle il itère (dupliquez là au pire le résultat ne changera pas)

[tri_yann]

SergeJack, La requête de 10h ne donne pas le bon résultat. Je n'ai que 9 enr.
Par contre le "Partition" est intéressant. En fait j'ai dans l'idée qu'on peut utiliser le RANK OVER pour affecter un Id de regroupement à chaque article. Ce qui me permetterait d'obtenir une table de cross du nombre distinct d'articles, donc beaucoup plus léger qu'une table de 3500000 enr. Le hic (de taille) c'est que les composants doivent être en colonnes dont le nombre est bien sûre variable.
Ce qui donnerait :

Code :

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

1
2
3
4
 
SELECT DISTINCT A.Id_Art, 
RANK() OVER (ORDER BY Id_Comp1, Id_Comp2, ...) as Id_CompGrp
FROM TB

[tri_yann]

Autre possibilité qui ne plait pas trop, c'est de concaténer à l'aide d'un CLR aggrégat tous les composants par article. Il suffit de faire une comparaison de chaîne. Je suis limité à 4000 car mais c'est peu probable que je dépasse.

[Waldar]

Que prend en entrée votre procédure ?

[tri_yann]

J'ai opté pour une solution qui ne me plait pas trop mais tant pis. J'ai créé un clr aggrégat qui concatène tous les Id_Comp par Id_Art. Il faut que je m'assure que l'ordre d'écriture des Id_Comp est toujours respecté.

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
 
SELECT Id_Art, Id_Comp
INTO #Tmp
FROM Tb
GROUP BY Id_Art, Id_Comp
ORDER BY Id_Art, Id_Comp
 
CREATE UNIQUE CLUSTERED INDEX IX_Tmp ON #Tmp (Id_Art, Id_Comp)
--Je m'assure par la création de la table #tmp que l'ordre est toujours croissant
 
SELECT Id_Art, RANK() OVER (ORDER  BY dbo.Clr_Concat(Id_Comp)) as Id_Rgp
INTO dbo.CROSS_ART
FROM #Tmp
GROUP BY Id_Art
 
CREATE UNIQUE INDEX IX_CROSS_ART ON dbo.CROSS_ART (Id_Art)
CREATE INDEX IX_CROSS_ART_Id_Rgp ON dbo.CROSS_ART (Id_Rgp)

L'exécution de cette procédure prend 5 sec. L'avantage, c'est que l'exploitation de la table CROSS_ART est instantannée car elle ne contient que 30000 enr. au lieu de 3500000.

Pour obtenir l'équivalent des autres requête de comparaison de comptage :

Select A.Id_Art, B.Id_Art
FROM dbo.CROSS_ART A inner join dbo.CROSS_ART B
ON A.Id_Rgp = B.Id_Rgp
-- Là, j'ai 3 500 000 enr.

Si quelqu'un a une idée pour éviter de créer la table #tmp tout en assurant l'ordre dans l'aggrégat. Je suis preneur.
Merci

[tri_yann]

Waldar, Je ne saisis pas la question.
Je nai pas de procédure, c'est justement ce que je cherche à créer.

[azur668]

Autre possibilité qui ne plait pas trop, c'est de concaténer à l'aide d'un CLR aggrégat tous les composants par article.

Si quelqu'un a une idée pour éviter de créer la table #tmp tout en assurant l'ordre dans l'aggrégat. Je suis preneur.

Une alternative au CLR agrégat : une sous-requête for XML PATH ('')
Du coup on peut gérer le ORDER BY Id_Comp et pas besoin de table temporaire.
Par contre pour je perfs aucune idée, tiens-nous au courant

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
 
With 
ListeART as (
    Select distinct Id_Art 
    FROM TB
),
AGREG as (
    SELECT 
        Id_Art, 
        Chaine= (Select cast(Id_Comp as varchar) + '/' from Tb  where TB.Id_Art = ListeART.Id_Art ORDER BY Id_Comp FOR XML PATH(''))
    FROM ListeART
)
Select
    Id_Art, RANK() OVER (ORDER  BY Chaine )
INTO 
    dbo.CROSS_ART
FROM  AGREG

Variante : au lieu de stocker un ID RANK, tu peux conserver la chaine concaténée en entier.
Avantage : inutile de recalculer le rank de l'ensemble de la table à chaque modif, il suffit de mettre a jour le Concat a chaque insert/delete/update pour les lignes concernées uniquement
Inconvénient : La consultation est un peu plus lente car la comparaison se fait sur un Varchar. Mais avec un bon index ça ne devrait pas avoir un grand impact.

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
 
CREATE TRIGGER Tb_AIUD_concat
   ON  Tb
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    With ListeART as (Select distinct Inserted.Id_Art union Select DISTINCT Deleted.ID_Art),
    AGREG as (
    SELECT Id_Art, Chaine= (Select cast(Id_Comp as varchar) + '/' from Tb where TB.Id_Art = ListeART.Id_Art ORDER BY Id_Comp FOR XML PATH('')) FROM ListeART )
 
    DST as (SELECT  CROSS_ART.* from CROSS_ART inner join ListeART on CROSS_ART.ID_Art = ListeART.Id_Art )
    Merge DST
    Using AGREG
        on (DST.Id_Art  = AGREG.Id_Art  )        
        WHEN MATCHED AND DST.Chaine <> AGREG.Chaine THEN
            UPDATE SET AGREG.Chaine =DST.Chaine
        WHEN NOT MATCHED BY TARGET THEN
            INSERT (AGREG.Chaine) 
            VALUES (DST.Chaine)                    
        WHEN NOT MATCHED BY SOURCE THEN
            DELETE            
        ;    
END
GO

[tri_yann]

Variante : au lieu de stocker un ID RANK, tu peux conserver la chaine concaténée en entier.
Avantage : inutile de recalculer le rank de l'ensemble de la table à chaque modif, il suffit de mettre a jour le Concat a chaque insert/delete/update pour les lignes concernées uniquement
Inconvénient : La consultation est un peu plus lente car la comparaison se fait sur un Varchar. Mais avec un bon index ça ne devrait pas avoir un grand impact.

Bonjour Azur668,
Je reste sur un Id car pour une génération de cette table, j'ai 1000 select. Donc je préfère mutualiser le temps sur la génération de la table et que les select soient instantannés.

je teste le XML PATH pour ses performances et merci pour l'info sur la possibilité du Order by. En règle général, je boude le XML PATH car il est difficile de gérer les caractères spéciaux.

[tri_yann]

Une alternative au CLR agrégat : une sous-requête for XML PATH ('')
Du coup on peut gérer le ORDER BY Id_Comp et pas besoin de table temporaire.
Par contre pour je perfs aucune idée, tiens-nous au courant

Rebonjour,

Je n'ai qu'un mot. C'est nickel. Je suis à la seconde au lieu de 5 pour générer ma table de Cross. Merci beaucoup.

Sais tu pourquoi ceci fonctionne dans ton code :

Code :

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

Chaine= (SELECT cast(Id_Comp AS varchar) + '/' FROM Tb  WHERE TB.Id_Art = ListeART.Id_Art ORDER BY Id_Comp FOR XML PATH(''))

et pas ceci :

Code :

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

Chaine= (SELECT dbo.clr_concat(cast(Id_Comp AS varchar) + '/')FROM Tb  WHERE TB.Id_Art = ListeART.Id_Art ORDER BY Id_Comp)

Order by interdit dans les sous requêtes

[tri_yann]

Merci à tous pour votre matière grise. Je considère le sujet résolu. Mais il me reste encore l'impression de passer à côté de quelquechose.
En résumé : 2 méthodes :

1. Comparaison de count
2. Concaténation (sorte de pivot en un varchar)

La méthode 1 est valable pour tous les cas mais très lourde car passe par un produit cartésien des 2 tables
La méthode 2 est rapide mais dangereuse dans certains contextes (trop de composants par ex)

[azur668]

et pas ceci :

Code :

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

Chaine= (SELECT dbo.clr_concat(cast(Id_Comp AS varchar) + '/')FROM Tb  WHERE TB.Id_Art = ListeART.Id_Art ORDER BY Id_Comp)

Order by interdit dans les sous requêtes

Aucune idée, mais une piste :
Les deux sous-requetes sont structurellement différentes :
Celle avec For XML PATH est entièrement assimilable à un littéral, puisque dans tous les cas elle ne peur renvoyer rien d'autre qu'un Varchar XML,
Alors que une requete classique, ne peut fonctionner que si elle ne renvoie qu'une valeur, ce qui n'est pas déterminable structurellement, même si le fait de définir un agrégat sans Group By nous assure en principe qu'il n'y aura qu'une ligne de renvoyée.

D'autre part, si on utilise une fonction d'agrégat, on ne peut pas avoir un Order by sur un champ qui n'est pas définit aussi dans Group By.
Le Order By ne va trier les lignes avant de les envoyer à la fonction d'agrégat, si c'est ce que tu voulais faire.

Pour cela il faudrait jouer avec

Code :

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

... From (Select top 99999999 From TB order by Id_Comp) As TbTri

(Le Top 100 percent ne fonctionne plus)
mais même comme cela, l'ordre de tri n'est pas garanti
(en cas de très grosses tables ou de tables partitionnées, ou selon l'état des données en cache, il peut arriver qu'il renvoie les lignes dans l'ordre de récupération, indépendamment du Order By qui restera utilisé pour le TOP)