3
votes

L'application croisée à l'aide d'une fonction c # accélère la requête

J'ai deux tableaux

Tableau A (environ 2 millions de lignes)

public static SqlByte function(SqlString equip, SqlString comp)
{
    SqlByte result = 1;

    if (comp.IsNull)
    {
        result = 1;
    }
    else
    {
        var eq = new List<string>(equip.ToString().Split('&'));
        var compString = new List<string>(comp.ToString().Split('!'));

        foreach (string com in compString)
        {
            foreach (string c in com.Split('&'))
            {
                if (c.StartsWith("~"))
                {
                    if (eq.Contains(c.Substring(1)))
                    {
                        result = 0;
                        break;
                    }
                }
                else if (!eq.Contains(c))
                {
                    result = 0;
                    break;
                }
                else
                {
                    result = 1;
                    continue;
                }
            }
            if (result == 1)
            {
                break;
            }
        }

    }
    return result;
}

Tableau B ( environ 50000 lignes)

SELECT A.ID, B.ID
FROM TableA AS A
CROSS APPLY TableB AS B
WHERE function(A.Str,B.Str) = 1

La requête que j'utilise est celle-ci

ID    Str
1     1=A&1323=D!1=B&1323=D!1=A&1323=E!1=F&1323=D!1=B&1323=E!1=F&1323=E
2     1=B&142=D&1323=D&1470=B
3     1=A&1323=E&1470=C

Le code de la fonction est écrit en C # :

ID    Str
123   1=A&3=B&8=B&11=A&12=R&17=BX&19=Z&20=B&21=AK&22=AH&24=A&27=A&28=A&29=C&31=A&32=S&33=N&34=H&35=Z&37=A&38=Z&39=A&41=D&42=G&49=A&52=Z&53=C&54=D&55=Z&56=Z&57=Z&58=A&61=B&63=B&65=A&66=A&67=A&68=A&69=A&71=G&73=Q&74=AB&76=B&77=Z&79=F&80=B&82=D&83=B&89=BA&91=Z&92=Z&93=Z&95=R&97=B&98=Z&99=G&100=G&101=B&106=C&109=Z&110=A&111=Z&112=C&116=B&118=Z&120=B&121=A&122=A&124=E&125=Z&128=A&131=Z&137=F&141=A&142=B&143=Z&144=B&146=C&148=Z&153=A&157=A&164=A&167=Z&168=Z&170=C&188=E&200=A&203=B&227=Z&235=C&237=B&238=G&258=Z&259=Z&268=Z&272=B&274=D&284=B&285=C&288=A&289=Z&290=A&299=M&300=D&313=Z&315=Z&322=A&324=Z&327=Z&358=E&360=B&365=A&366=A&375=B&376=B&379=Z&381=Z&383=M&387=Z&390=Z&391=Z&392=B&393=Z&398=MA&400=Z&403=B&404=A&407=B&408=BT&410=B&412=A&413=F&414=B&415=F&416=B&419=Z&421=Z&430=C&433=Z&435=C&440=Z&441=PG&448=A&453=A&458=B&459=Z&460=Z&463=A&466=Z&468=Z&472=Z&474=A&475=Z&492=Z&496=Z&498=A&504=A&506=B&507=Z&514=D&518=B&519=Z&523=C&536=A&554=Z&557=Z&560=A&582=Z&584=A&586=Z&587=Z&588=B&591=C&599=A&603=Z&610=Z&614=B&625=A&664=A&666=C&668=C&670=Z&672=L&674=L&698=B&699=A&720=BB&745=Z&754=A&756=Z&759=Z&762=Z&766=AA&767=AA&769=A&772=HB&785=B&787=Z&788=Z&789=Z&790=Z&791=Z&792=Z&794=Z&795=A&796=Z&803=A&808=Z&809=DC&812=G&813=G&815=G&821=K&822=M&824=ES&828=Z&829=GA&831=SN&837=BC&841=K&845=K&851=S&852=Z&853=B&854=WU&855=B&856=WU&857=A&859=A&861=B&863=C&865=A&867=B&869=Z&873=ZA&882=CM&889=CD&921=Z&922=Z&924=Z&925=Z&932=B&933=Z&941=Z&952=RJ&954=Z&967=P&984=A&985=H&1002=D&1007=F&1041=F&1052=BR&1053=BR&1056=PD&1057=NA&1095=H&1104=B&1121=A&1122=M&1123=D&1162=G&1163=A&1164=B&1165=A&1166=E&1174=A&1182=A&1183=A&1185=C&1188=Z&1202=KG&1211=A&1223=B&1229=Z&1231=A&1233=Z&1237=L&1256=TW&1258=C&1259=BH&1260=D&1261=A&1272=C&1296=Z&1298=Z&1299=A&1302=A&1303=A&1308=C&1309=J&1313=A&1316=B&1317=Z&1318=A&1319=Z&1323=D&1327=A&1328=A&1329=Z&1330=Z&1331=A&1332=Z&1333=Z&1335=DS&1339=Z&1345=AE&1346=AG&1352=Z&1356=Z&1366=Z&1367=B&1368=B&1369=A&1370=W&1371=A&1373=Z&1379=GQ&1381=A&1387=CX&1388=Z&1389=EU&1391=E&1392=J&1397=Z&1399=Z&1401=Z&1405=A&1406=CN&1412=Z&1416=Z&1417=B&1419=Z&1421=Z&1424=Z&1428=Z&1431=B&1432=B&1436=A&1437=Z&1441=CB&1442=A&1443=Z&1449=A&1452=Z&1453=Z&1470=A&1473=Z&1474=A&1475=Z&1477=D&1478=B&1479=B&1481=A&1488=A&1490=D&1493=E&1494=Z&1502=Y&1503=BH&1504=C&1505=B&1506=Z&1511=Z&1512=A&1513=KU&1518=A&1524=Z&1541=A&1549=A&1554=A&1623=A&1629=Z&1637=B&1640=Z&1642=Z&1643=Z&1644=B&1661=A&1673=K&1681=A&1771=A&1772=Z&1787=Z&1788=Z&1790=Z&1793=Z&1837=A&1838=Z&1839=Z&1854=Z&1855=Z&1856=Z&1858=Z&1867=B&1872=B&1873=B&1879=Z&1881=B&1902=Z&1910=Z&1916=B&1917=A&1918=B&1920=A&1927=A&1928=Z&1963=Z&1964=Z&1968=A&1974=A&1975=Z&1976=Z&1993=Z&2007=A&2010=Z&2011=A&2012=Z&2015=Z&2022=Z&2023=Z&2024=Z&2032=Z&2037=Z&MPC-9002=AA&MPC-9006=AG&
165   1=A&2=R&4=B&33=G&34=Q&46=Y&49=A&75=AH&589=C&590=A&803=IG&812=RB&813=RB&814=RB&815=RB&841=V&843=V&844=V&845=V&851=S&853=B&854=KJ&855=B&856=KJ&857=B&858=KJ&859=B&860=KJ&861=B&862=RB&863=B&864=RB&865=B&866=RB&867=B&881=AC&883=GE&889=M&897=IS&922=C&965=CP&1323=C&

Avec la quantité de données donnée, il me faut environ 6 jours pour exécuter la requête. Y a-t-il une astuce que je peux faire pour fixer les choses?

Merci beaucoup!


8 commentaires

Vous pouvez utiliser Tasks ou Parallel.ForEach , exemple


Je ne sais pas quel est son objectif, mais vous créez et vérifiez des chaînes 100000000000 (cent milliards) - pourriez-vous diviser les chaînes en lignes dans une table relationnelle, puis utiliser un autre type de SQL pour trouver les correspondances que vous chercher?


Avez-vous envisagé d'utiliser CTE côté serveur au lieu de la fonction C #?


@MaciejLos a essayé cela mais cela a pris encore plus de temps à accomplir


@Cato a également essayé cela, mais vous avez encore plus de combinaisons de lignes, donc cela a pris plus de temps


La meilleure façon d’améliorer les performances ici est de cesser de stocker des valeurs délimitées. Il viole 1NF et cause toutes sortes de problèmes.


@SeanLange c'est une table qui n'est pas faite par moi ... mais je dois l'utiliser: |


Je trouverais un moyen d'analyser ce désordre qui vous a été remis dans une table persistante afin que vous puissiez l'interroger. Je ressens votre douleur de ne pas pouvoir réparer cette table, mais peu importe la façon dont vous la coupez, cela va nuire à la performance dans son état actuel. Il a 2 niveaux de délimiteurs qui sont essentiellement en train de pousser une table entière dans un seul tuple. L'idée d'une table persistante est de permettre au fractionnement de se produire lorsque les données atteignent votre table afin que vous n'ayez pas à le faire tout le temps. Ce n'est pas le meilleur mais ce serait mieux pour les performances.


3 Réponses :


2
votes

Dans MS Sql Server 2016+, vous pouvez utiliser le STRING_SPLIT pour séparer les chaînes de tableB.

Même double-les diviser.

Ensuite, faites correspondre ces parties de chaîne avec les chaînes de TableA.

Exemple d'extrait de code utilisant des variables de table pour la démonstration:

ID1 ID2
1   1
1   2
1   3
2   1
2   3

declare @TableA table (ID int primary key identity(1,1), [Str] varchar(3000));
declare @TableB table (ID int primary key identity(1,1), [Str] varchar(100));

insert into @TableA ([Str]) values
 ('1=A&3=B&8=B&11=A&12=R&17=BX&19=Z&20=B&21=AK&22=AH&24=A&27=A&28=A&29=C&31=A&32=S&33=N&34=H&35=Z&37=A&38=Z&39=A&41=D&42=G&49=A&52=Z&53=C&54=D&55=Z&56=Z&57=Z&58=A&61=B&63=B&65=A&66=A&67=A&68=A&69=A&71=G&73=Q&74=AB&76=B&77=Z&79=F&80=B&82=D&83=B&89=BA&91=Z&92=Z&93=Z&95=R&97=B&98=Z&99=G&100=G&101=B&106=C&109=Z&110=A&111=Z&112=C&116=B&118=Z&120=B&121=A&122=A&124=E&125=Z&128=A&131=Z&137=F&141=A&142=B&143=Z&144=B&146=C&148=Z&153=A&157=A&164=A&167=Z&168=Z&170=C&188=E&200=A&203=B&227=Z&235=C&237=B&238=G&258=Z&259=Z&268=Z&272=B&274=D&284=B&285=C&288=A&289=Z&290=A&299=M&300=D&313=Z&315=Z&322=A&324=Z&327=Z&358=E&360=B&365=A&366=A&375=B&376=B&379=Z&381=Z&383=M&387=Z&390=Z&391=Z&392=B&393=Z&398=MA&400=Z&403=B&404=A&407=B&408=BT&410=B&412=A&413=F&414=B&415=F&416=B&419=Z&421=Z&430=C&433=Z&435=C&440=Z&441=PG&448=A&453=A&458=B&459=Z&460=Z&463=A&466=Z&468=Z&472=Z&474=A&475=Z&492=Z&496=Z&498=A&504=A&506=B&507=Z&514=D&518=B&519=Z&523=C&536=A&554=Z&557=Z&560=A&582=Z&584=A&586=Z&587=Z&588=B&591=C&599=A&603=Z&610=Z&614=B&625=A&664=A&666=C&668=C&670=Z&672=L&674=L&698=B&699=A&720=BB&745=Z&754=A&756=Z&759=Z&762=Z&766=AA&767=AA&769=A&772=HB&785=B&787=Z&788=Z&789=Z&790=Z&791=Z&792=Z&794=Z&795=A&796=Z&803=A&808=Z&809=DC&812=G&813=G&815=G&821=K&822=M&824=ES&828=Z&829=GA&831=SN&837=BC&841=K&845=K&851=S&852=Z&853=B&854=WU&855=B&856=WU&857=A&859=A&861=B&863=C&865=A&867=B&869=Z&873=ZA&882=CM&889=CD&921=Z&922=Z&924=Z&925=Z&932=B&933=Z&941=Z&952=RJ&954=Z&967=P&984=A&985=H&1002=D&1007=F&1041=F&1052=BR&1053=BR&1056=PD&1057=NA&1095=H&1104=B&1121=A&1122=M&1123=D&1162=G&1163=A&1164=B&1165=A&1166=E&1174=A&1182=A&1183=A&1185=C&1188=Z&1202=KG&1211=A&1223=B&1229=Z&1231=A&1233=Z&1237=L&1256=TW&1258=C&1259=BH&1260=D&1261=A&1272=C&1296=Z&1298=Z&1299=A&1302=A&1303=A&1308=C&1309=J&1313=A&1316=B&1317=Z&1318=A&1319=Z&1323=D&1327=A&1328=A&1329=Z&1330=Z&1331=A&1332=Z&1333=Z&1335=DS&1339=Z&1345=AE&1346=AG&1352=Z&1356=Z&1366=Z&1367=B&1368=B&1369=A&1370=W&1371=A&1373=Z&1379=GQ&1381=A&1387=CX&1388=Z&1389=EU&1391=E&1392=J&1397=Z&1399=Z&1401=Z&1405=A&1406=CN&1412=Z&1416=Z&1417=B&1419=Z&1421=Z&1424=Z&1428=Z&1431=B&1432=B&1436=A&1437=Z&1441=CB&1442=A&1443=Z&1449=A&1452=Z&1453=Z&1470=A&1473=Z&1474=A&1475=Z&1477=D&1478=B&1479=B&1481=A&1488=A&1490=D&1493=E&1494=Z&1502=Y&1503=BH&1504=C&1505=B&1506=Z&1511=Z&1512=A&1513=KU&1518=A&1524=Z&1541=A&1549=A&1554=A&1623=A&1629=Z&1637=B&1640=Z&1642=Z&1643=Z&1644=B&1661=A&1673=K&1681=A&1771=A&1772=Z&1787=Z&1788=Z&1790=Z&1793=Z&1837=A&1838=Z&1839=Z&1854=Z&1855=Z&1856=Z&1858=Z&1867=B&1872=B&1873=B&1879=Z&1881=B&1902=Z&1910=Z&1916=B&1917=A&1918=B&1920=A&1927=A&1928=Z&1963=Z&1964=Z&1968=A&1974=A&1975=Z&1976=Z&1993=Z&2007=A&2010=Z&2011=A&2012=Z&2015=Z&2022=Z&2023=Z&2024=Z&2032=Z&2037=Z&MPC-9002=AA&MPC-9006=AG&')
,('1=A&2=R&4=B&33=G&34=Q&46=Y&49=A&75=AH&589=C&590=A&803=IG&812=RB&813=RB&814=RB&815=RB&841=V&843=V&844=V&845=V&851=S&853=B&854=KJ&855=B&856=KJ&857=B&858=KJ&859=B&860=KJ&861=B&862=RB&863=B&864=RB&865=B&866=RB&867=B&881=AC&883=GE&889=M&897=IS&922=C&965=CP&1323=C&')
;

insert into @TableB ([Str]) values
 ('1=A&1323=D!1=B&1323=D!1=A&1323=E!1=F&1323=D!1=B&1323=E!1=F&1323=E')
,('1=B&142=D&1323=D&1470=B')
,('1=A&1323=E&1470=C')
;

select A.ID as ID1, ssB.ID as ID2
-- , COUNT(DISTINCT ssB.StrPart) as TotalStringPartsMatching
-- , STRING_AGG(ssB.StrPart, '&') WITHIN GROUP (ORDER BY ssB.StrPart) as CommonStrParts
from @TableA A
left join 
(
   select distinct B.ID, ss2.value as StrPart
   from @TableB B
   cross apply string_split([Str],'!') ss1
   cross apply string_split(ss1.value,'&') ss2
) ssB on concat('&',A.[Str],'&') like concat('%&',ssb.StrPart,'&%')
group by A.ID, ssB.ID;


4 commentaires

Génial! Que pensez-vous de CTE? Vaut-il la peine de publier une solution avec des requêtes récursives?


@MaciejLos Je pense qu'il est possible d'écrire une solution qui utilise un CTE récursif. Mais j'ai quelques doutes sur le fait que ce serait plus performant qu'en utilisant JOIN. Puisque c'est beaucoup de données à faire correspondre. En fait, pour accélérer cela, je suppose qu'il pourrait être utile d'utiliser une table temporaire pour ce que la sous-requête ssB calcule.


STRING_SPLIT () ne renvoie malheureusement pas la position du fragment. Lors de votre appel à STRING_AGG () , vous triez par ssB.StrPart , mais cela peut ne pas renvoyer l'ordre d'origine (bien que les données données ressemblent à ceci). vous pourriez jeter un oeil à ma réponse, comment OPENJSON peut surmonter cette faille ...


@Shnugo Je suis d'accord avec STRING_SPLIT. Ne renvoyer que des valeurs sans leur position semble être une occasion manquée de fournir une fonction encore plus utile. Btw, utiliser OPENJSON comme alternative est plutôt cool.



1
votes

En raison de la solution très intéressante fournie par LukStorms et de notre discussion en commentaires sur Sa solution, je ne suis pas sûr c'est la meilleure alternative, mais ... CTE devrait faire l'affaire.

Remarque: je n'ai pas MS SQL Server 2017, j'ai donc utilisé CHARINDEX fonction pour diviser la chaîne en parties et de les comparer.

Le voici:

EID EStep   EPart   AID APart       Result
123 1       1=A     1   1=A&1323=D  1
123 1       1=A     1   1=A&1323=E  1
165 1       1=A     1   1=A&1323=D  1
165 1       1=A     1   1=A&1323=E  1
123 297     1323=D  1   1=A&1323=D  5
123 297     1323=D  1   1=B&1323=D  5
123 297     1323=D  1   1=F&1323=D  5

Résultat:

DECLARE @Equip TABLE(ID INT, StrData VARCHAR(8000))
INSERT INTO @Equip (ID, StrData )
VALUES(123, '1=A&3=B&8=B&11=A&12=R&17=BX&19=Z&20=B&21=AK&22=AH&24=A&27=A&28=A&29=C&31=A&32=S&33=N&34=H&35=Z&37=A&38=Z&39=A&41=D&42=G&49=A&52=Z&53=C&54=D&55=Z&56=Z&57=Z&58=A&61=B&63=B&65=A&66=A&67=A&68=A&69=A&71=G&73=Q&74=AB&76=B&77=Z&79=F&80=B&82=D&83=B&89=BA&91=Z&92=Z&93=Z&95=R&97=B&98=Z&99=G&100=G&101=B&106=C&109=Z&110=A&111=Z&112=C&116=B&118=Z&120=B&121=A&122=A&124=E&125=Z&128=A&131=Z&137=F&141=A&142=B&143=Z&144=B&146=C&148=Z&153=A&157=A&164=A&167=Z&168=Z&170=C&188=E&200=A&203=B&227=Z&235=C&237=B&238=G&258=Z&259=Z&268=Z&272=B&274=D&284=B&285=C&288=A&289=Z&290=A&299=M&300=D&313=Z&315=Z&322=A&324=Z&327=Z&358=E&360=B&365=A&366=A&375=B&376=B&379=Z&381=Z&383=M&387=Z&390=Z&391=Z&392=B&393=Z&398=MA&400=Z&403=B&404=A&407=B&408=BT&410=B&412=A&413=F&414=B&415=F&416=B&419=Z&421=Z&430=C&433=Z&435=C&440=Z&441=PG&448=A&453=A&458=B&459=Z&460=Z&463=A&466=Z&468=Z&472=Z&474=A&475=Z&492=Z&496=Z&498=A&504=A&506=B&507=Z&514=D&518=B&519=Z&523=C&536=A&554=Z&557=Z&560=A&582=Z&584=A&586=Z&587=Z&588=B&591=C&599=A&603=Z&610=Z&614=B&625=A&664=A&666=C&668=C&670=Z&672=L&674=L&698=B&699=A&720=BB&745=Z&754=A&756=Z&759=Z&762=Z&766=AA&767=AA&769=A&772=HB&785=B&787=Z&788=Z&789=Z&790=Z&791=Z&792=Z&794=Z&795=A&796=Z&803=A&808=Z&809=DC&812=G&813=G&815=G&821=K&822=M&824=ES&828=Z&829=GA&831=SN&837=BC&841=K&845=K&851=S&852=Z&853=B&854=WU&855=B&856=WU&857=A&859=A&861=B&863=C&865=A&867=B&869=Z&873=ZA&882=CM&889=CD&921=Z&922=Z&924=Z&925=Z&932=B&933=Z&941=Z&952=RJ&954=Z&967=P&984=A&985=H&1002=D&1007=F&1041=F&1052=BR&1053=BR&1056=PD&1057=NA&1095=H&1104=B&1121=A&1122=M&1123=D&1162=G&1163=A&1164=B&1165=A&1166=E&1174=A&1182=A&1183=A&1185=C&1188=Z&1202=KG&1211=A&1223=B&1229=Z&1231=A&1233=Z&1237=L&1256=TW&1258=C&1259=BH&1260=D&1261=A&1272=C&1296=Z&1298=Z&1299=A&1302=A&1303=A&1308=C&1309=J&1313=A&1316=B&1317=Z&1318=A&1319=Z&1323=D&1327=A&1328=A&1329=Z&1330=Z&1331=A&1332=Z&1333=Z&1335=DS&1339=Z&1345=AE&1346=AG&1352=Z&1356=Z&1366=Z&1367=B&1368=B&1369=A&1370=W&1371=A&1373=Z&1379=GQ&1381=A&1387=CX&1388=Z&1389=EU&1391=E&1392=J&1397=Z&1399=Z&1401=Z&1405=A&1406=CN&1412=Z&1416=Z&1417=B&1419=Z&1421=Z&1424=Z&1428=Z&1431=B&1432=B&1436=A&1437=Z&1441=CB&1442=A&1443=Z&1449=A&1452=Z&1453=Z&1470=A&1473=Z&1474=A&1475=Z&1477=D&1478=B&1479=B&1481=A&1488=A&1490=D&1493=E&1494=Z&1502=Y&1503=BH&1504=C&1505=B&1506=Z&1511=Z&1512=A&1513=KU&1518=A&1524=Z&1541=A&1549=A&1554=A&1623=A&1629=Z&1637=B&1640=Z&1642=Z&1643=Z&1644=B&1661=A&1673=K&1681=A&1771=A&1772=Z&1787=Z&1788=Z&1790=Z&1793=Z&1837=A&1838=Z&1839=Z&1854=Z&1855=Z&1856=Z&1858=Z&1867=B&1872=B&1873=B&1879=Z&1881=B&1902=Z&1910=Z&1916=B&1917=A&1918=B&1920=A&1927=A&1928=Z&1963=Z&1964=Z&1968=A&1974=A&1975=Z&1976=Z&1993=Z&2007=A&2010=Z&2011=A&2012=Z&2015=Z&2022=Z&2023=Z&2024=Z&2032=Z&2037=Z&MPC-9002=AA&MPC-9006=AG&'),
(165, '1=A&2=R&4=B&33=G&34=Q&46=Y&49=A&75=AH&589=C&590=A&803=IG&812=RB&813=RB&814=RB&815=RB&841=V&843=V&844=V&845=V&851=S&853=B&854=KJ&855=B&856=KJ&857=B&858=KJ&859=B&860=KJ&861=B&862=RB&863=B&864=RB&865=B&866=RB&867=B&881=AC&883=GE&889=M&897=IS&922=C&965=CP&1323=C&')

DECLARE @Another TABLE(ID INT, StrData VARCHAR(8000))
INSERT INTO @Another (ID, StrData )
VALUES(1, '1=A&1323=D!1=B&1323=D!1=A&1323=E!1=F&1323=D!1=B&1323=E!1=F&1323=E'),
(2, '1=B&142=D&1323=D&1470=B'),
(3, '1=A&1323=E&1470=C')


;WITH E AS
(
    --inital part
    SELECT 1 AS Step, ID, LEFT(StrData, CHARINDEX('&', StrData) -1) AS Part, RIGHT(StrData, LEN(StrData) - CHARINDEX('&', StrData)) AS Remainder
    FROM @Equip 
    WHERE CHARINDEX('&', StrData)>0
    -- recursive part
    UNION ALL
    SELECT Step+1 AS Step, ID, LEFT(Remainder, CHARINDEX('&', Remainder) -1) AS Part, RIGHT(Remainder, LEN(Remainder) - CHARINDEX('&', Remainder)) AS Remainder
    FROM E 
    WHERE CHARINDEX('&', Remainder)>0
    UNION ALL
    SELECT Step+1 AS Step, ID, Remainder AS Part, NULL AS Remainder
    FROM E 
    WHERE CHARINDEX('&', Remainder)=0
), 
    A AS
    (
        --inital part
        SELECT 1 AS Step, ID, LEFT(StrData, CHARINDEX('!', StrData) -1) AS Part, RIGHT(StrData, LEN(StrData) - CHARINDEX('!', StrData)) AS Remainder
        FROM @Another 
        WHERE CHARINDEX('!', StrData)>0
        -- recursive part
        UNION ALL
        SELECT Step+1 AS Step, ID, LEFT(Remainder, CHARINDEX('!', Remainder) -1) AS Part, RIGHT(Remainder, LEN(Remainder) - CHARINDEX('!', Remainder)) AS Remainder
        FROM A 
        WHERE CHARINDEX('!', Remainder)>0
        UNION ALL
        SELECT Step+1 AS Step, ID, Remainder AS Part, NULL AS Remainder
        FROM A 
        WHERE CHARINDEX('!', Remainder)=0
    )
SELECT E.ID AS EID, E.Step AS EStep, E.Part AS EPart, A.ID AS AID, A.Part AS APart, CHARINDEX(E.Part, A.Part) AS Result
FROM E CROSS JOIN A 
WHERE CHARINDEX(E.Part, A.Part)>0
OPTION (MAXRECURSION 0)

Bonne chance!


2 commentaires

Je me demande comment il fonctionnera sur des millions de lignes, mais j'aime vos efforts pour l'essayer de manière récursive.


@LukStorms, merci. Comme je l'ai mentionné dans la réponse, je ne suis pas sûr que cela améliorera les performances, mais c'est une alternative. À votre santé! Maciej



3
votes

Comme vous êtes sur SQL-Server 2017, je recommanderais une approche via JSON. Un gros avantage est que OPENJSON retournera la position du fragment, ce qui permet de reconcaténer ceci - si nécessaire:

+----------+------+---------------+------+----------------+----------------+
| Fragment | ID_a | OrdPosition_a | ID_b | OrdPosition_b1 | OrdPosition_b2 |
+----------+------+---------------+------+----------------+----------------+
| 1=A      | 123  | 0             | 1    | 0              | 0              |
+----------+------+---------------+------+----------------+----------------+
| 1=A      | 165  | 0             | 1    | 0              | 0              |
+----------+------+---------------+------+----------------+----------------+
| 1=A      | 123  | 0             | 1    | 2              | 0              |
+----------+------+---------------+------+----------------+----------------+
| 1=A      | 165  | 0             | 1    | 2              | 0              |
+----------+------+---------------+------+----------------+----------------+
| 1=A      | 123  | 0             | 3    | 0              | 0              |
+----------+------+---------------+------+----------------+----------------+
| 1=A      | 165  | 0             | 3    | 0              | 0              |
+----------+------+---------------+------+----------------+----------------+
| 1323=D   | 123  | 296           | 1    | 3              | 1              |
+----------+------+---------------+------+----------------+----------------+
| 1323=D   | 123  | 296           | 1    | 1              | 1              |
+----------+------+---------------+------+----------------+----------------+
| 1323=D   | 123  | 296           | 1    | 0              | 1              |
+----------+------+---------------+------+----------------+----------------+
| 1323=D   | 123  | 296           | 2    | 0              | 2              |
+----------+------+---------------+------+----------------+----------------+

- Vous pouvez diviser ces données facilement en le transformant en un tableau JSON
- Une valeur du type 1 = A & 3 = B & 8 = B & 11 = A & 12 = R sera ["1 = A", "3 = B", "8 = B", "11 = A "," 12 = R "]

DECLARE @tblA TABLE(ID INT,YourString VARCHAR(MAX));
INSERT INTO @tblA VALUES
 (123,'1=A&3=B&8=B&11=A&12=R&17=BX&19=Z&20=B&21=AK&22=AH&24=A&27=A&28=A&29=C&31=A&32=S&33=N&34=H&35=Z&37=A&38=Z&39=A&41=D&42=G&49=A&52=Z&53=C&54=D&55=Z&56=Z&57=Z&58=A&61=B&63=B&65=A&66=A&67=A&68=A&69=A&71=G&73=Q&74=AB&76=B&77=Z&79=F&80=B&82=D&83=B&89=BA&91=Z&92=Z&93=Z&95=R&97=B&98=Z&99=G&100=G&101=B&106=C&109=Z&110=A&111=Z&112=C&116=B&118=Z&120=B&121=A&122=A&124=E&125=Z&128=A&131=Z&137=F&141=A&142=B&143=Z&144=B&146=C&148=Z&153=A&157=A&164=A&167=Z&168=Z&170=C&188=E&200=A&203=B&227=Z&235=C&237=B&238=G&258=Z&259=Z&268=Z&272=B&274=D&284=B&285=C&288=A&289=Z&290=A&299=M&300=D&313=Z&315=Z&322=A&324=Z&327=Z&358=E&360=B&365=A&366=A&375=B&376=B&379=Z&381=Z&383=M&387=Z&390=Z&391=Z&392=B&393=Z&398=MA&400=Z&403=B&404=A&407=B&408=BT&410=B&412=A&413=F&414=B&415=F&416=B&419=Z&421=Z&430=C&433=Z&435=C&440=Z&441=PG&448=A&453=A&458=B&459=Z&460=Z&463=A&466=Z&468=Z&472=Z&474=A&475=Z&492=Z&496=Z&498=A&504=A&506=B&507=Z&514=D&518=B&519=Z&523=C&536=A&554=Z&557=Z&560=A&582=Z&584=A&586=Z&587=Z&588=B&591=C&599=A&603=Z&610=Z&614=B&625=A&664=A&666=C&668=C&670=Z&672=L&674=L&698=B&699=A&720=BB&745=Z&754=A&756=Z&759=Z&762=Z&766=AA&767=AA&769=A&772=HB&785=B&787=Z&788=Z&789=Z&790=Z&791=Z&792=Z&794=Z&795=A&796=Z&803=A&808=Z&809=DC&812=G&813=G&815=G&821=K&822=M&824=ES&828=Z&829=GA&831=SN&837=BC&841=K&845=K&851=S&852=Z&853=B&854=WU&855=B&856=WU&857=A&859=A&861=B&863=C&865=A&867=B&869=Z&873=ZA&882=CM&889=CD&921=Z&922=Z&924=Z&925=Z&932=B&933=Z&941=Z&952=RJ&954=Z&967=P&984=A&985=H&1002=D&1007=F&1041=F&1052=BR&1053=BR&1056=PD&1057=NA&1095=H&1104=B&1121=A&1122=M&1123=D&1162=G&1163=A&1164=B&1165=A&1166=E&1174=A&1182=A&1183=A&1185=C&1188=Z&1202=KG&1211=A&1223=B&1229=Z&1231=A&1233=Z&1237=L&1256=TW&1258=C&1259=BH&1260=D&1261=A&1272=C&1296=Z&1298=Z&1299=A&1302=A&1303=A&1308=C&1309=J&1313=A&1316=B&1317=Z&1318=A&1319=Z&1323=D&1327=A&1328=A&1329=Z&1330=Z&1331=A&1332=Z&1333=Z&1335=DS&1339=Z&1345=AE&1346=AG&1352=Z&1356=Z&1366=Z&1367=B&1368=B&1369=A&1370=W&1371=A&1373=Z&1379=GQ&1381=A&1387=CX&1388=Z&1389=EU&1391=E&1392=J&1397=Z&1399=Z&1401=Z&1405=A&1406=CN&1412=Z&1416=Z&1417=B&1419=Z&1421=Z&1424=Z&1428=Z&1431=B&1432=B&1436=A&1437=Z&1441=CB&1442=A&1443=Z&1449=A&1452=Z&1453=Z&1470=A&1473=Z&1474=A&1475=Z&1477=D&1478=B&1479=B&1481=A&1488=A&1490=D&1493=E&1494=Z&1502=Y&1503=BH&1504=C&1505=B&1506=Z&1511=Z&1512=A&1513=KU&1518=A&1524=Z&1541=A&1549=A&1554=A&1623=A&1629=Z&1637=B&1640=Z&1642=Z&1643=Z&1644=B&1661=A&1673=K&1681=A&1771=A&1772=Z&1787=Z&1788=Z&1790=Z&1793=Z&1837=A&1838=Z&1839=Z&1854=Z&1855=Z&1856=Z&1858=Z&1867=B&1872=B&1873=B&1879=Z&1881=B&1902=Z&1910=Z&1916=B&1917=A&1918=B&1920=A&1927=A&1928=Z&1963=Z&1964=Z&1968=A&1974=A&1975=Z&1976=Z&1993=Z&2007=A&2010=Z&2011=A&2012=Z&2015=Z&2022=Z&2023=Z&2024=Z&2032=Z&2037=Z&MPC-9002=AA&MPC-9006=AG&')
,(165,'1=A&2=R&4=B&33=G&34=Q&46=Y&49=A&75=AH&589=C&590=A&803=IG&812=RB&813=RB&814=RB&815=RB&841=V&843=V&844=V&845=V&851=S&853=B&854=KJ&855=B&856=KJ&857=B&858=KJ&859=B&860=KJ&861=B&862=RB&863=B&864=RB&865=B&866=RB&867=B&881=AC&883=GE&889=M&897=IS&922=C&965=CP&1323=C&');

DECLARE @tblB TABLE(ID INT,YourString VARCHAR(MAX));
INSERT INTO @tblB VALUES
 (1,'1=A&1323=D!1=B&1323=D!1=A&1323=E!1=F&1323=D!1=B&1323=E!1=F&1323=E')
,(2,'1=B&142=D&1323=D&1470=B')
,(3,'1=A&1323=E&1470=C');

WITH tblA AS
(
    SELECT ID
          ,A.[key] AS OrdPosition
          ,A.[value] AS Fragment
          ,x.value('/x[1]','varchar(10)') AS FragmentLeft
          ,x.value('/x[2]','varchar(10)') AS FragmentRight
    FROM @tblA 
    CROSS APPLY OPENJSON('["' + REPLACE(YourString,'&','","') + '"]') A
    CROSS APPLY(SELECT CAST('<x>' + REPLACE(A.[value],'=','</x><x>') + '</x>' AS XML)) B(x)
)
,tblB AS
(
    SELECT ID
          ,A.[key] AS OrdPositionA
          ,B.[key] AS OrdPositionB
          ,B.[value] AS Fragment
          ,x.value('/x[1]','varchar(10)') AS FragmentLeft
          ,x.value('/x[2]','varchar(10)') AS FragmentRight
    FROM @tblB
    CROSS APPLY OPENJSON('["' + REPLACE(YourString,'!','","') + '"]') A
    CROSS APPLY OPENJSON('["' + REPLACE(A.[value],'&','","') + '"]') B
    CROSS APPLY(SELECT CAST('<x>' + REPLACE(B.[value],'=','</x><x>') + '</x>' AS XML)) C(x)
)
SELECT a.Fragment
      ,a.ID AS ID_a, a.OrdPosition AS OrdPosition_a
      ,b.ID AS ID_b, b.OrdPositionA AS OrdPosition_b1,b.OrdPositionB AS OrdPosition_b2
FROM tblA a
INNER JOIN tblB b ON a.FragmentLeft=b.FragmentLeft AND a.FragmentRight=b.FragmentRight;

- Votre deuxième tableau est double . Nous pouvons également diviser cela en une seule requête

SELECT ID
      ,A.[key] AS OrdPositionA
      ,B.[key] AS OrdPositionB
      ,B.[value] AS Fragment
      ,x.value('/x[1]','varchar(10)') AS FragmentNumber
      ,x.value('/x[2]','varchar(10)') AS FragmentCharacter
FROM @tblB
CROSS APPLY OPENJSON('["' + REPLACE(YourString,'!','","') + '"]') A
CROSS APPLY OPENJSON('["' + REPLACE(A.[value],'&','","') + '"]') B
CROSS APPLY(SELECT CAST('<x>' + REPLACE(B.[value],'=','</x><x>') + '</x>' AS XML)) C(x)
ORDER BY ID,OrdPositionA,OrdPositionB

Il devrait être facile de combiner ces approches avec une simple jointure.

Performance

OPENJSON fonctionne plutôt bien. Dans cette réponse , j'ai comparé STRING_SPLIT , OPENJSON et le bien connu Fonction delimited8k () . Cela vaut la peine de le vérifier ...

Mais voici ce que vous devriez vraiment faire:

Utilisez une requête comme celle ci-dessus pour stocker vos données dans fragments. Chaque fois que vous en avez besoin, vous pouvez utiliser STRING_AGG () pour récupérer les longues chaînes, mais le gros travail est le fractionnement. Ce n'est pas très intelligent de le faire encore et encore et encore ...

MISE À JOUR: Encore mieux:

Utiliser ceci pour @tlbA p >

SELECT ID
      ,A.[key] AS OrdPosition
      ,A.[value] AS Fragment
      ,x.value('/x[1]','varchar(10)') AS FragmentNumber
      ,x.value('/x[2]','varchar(10)') AS FragmentCharacter
FROM @tblA 
CROSS APPLY OPENJSON('["' + REPLACE(YourString,'&','","') + '"]') A
CROSS APPLY(SELECT CAST('<x>' + REPLACE(A.[value],'=','</x><x>') + '</x>' AS XML)) B(x)
ORDER BY ID,OrdPosition

Et ceci pour @tblB

DECLARE @tblB TABLE(ID INT,YourString VARCHAR(MAX));
INSERT INTO @tblB VALUES
 (1,'1=A&1323=D!1=B&1323=D!1=A&1323=E!1=F&1323=D!1=B&1323=E!1=F&1323=E')
,(2,'1=B&142=D&1323=D&1470=B')
,(3,'1=A&1323=E&1470=C');

SELECT ID
      ,A.[key] AS OrdPositionA
      ,B.[key] AS OrdPositionB
      ,B.[value] AS Fragment
FROM @tblB
CROSS APPLY OPENJSON('["' + REPLACE(YourString,'!','","') + '"]') A
CROSS APPLY OPENJSON('["' + REPLACE(A.[value],'&','","') + '"]') B

Cela renverra chaque valeur soigneusement séparée. Remplissez-les dans des tables physiques, placez des index et profitez de la vitesse que vous obtenez.

Stockez les valeurs dans ces tables et récupérez vos énormes chaînes d'origine avec un simple STRING_AGG () query ...

UPDATE 2

En réponse à votre commentaire, voici une approche simple pour joindre ces résultats:

SELECT ID
      ,A.[key] AS OrdPosition
      ,A.[value] AS Fragment
FROM @tblA 
CROSS APPLY OPENJSON('["' + REPLACE(YourString,'&','","') + '"]') A
  • Le Fragment 1 = A est commun dans les a-lines 123 et 165 a avec les b-lines 1 ou 3 dans le premier ou le troisième bloc (index de base zéro) en première position

  • Le fragment 1323 = D est commun dans la ligne a 123 avec la ligne b 1 et 2. C'est le 297e fragment dans la ligne a et le ... ( et ainsi de suite ...)

Si cela ne vous aide pas: veuillez fournir le résultat attendu ainsi que les règles de votre jointure.


2 commentaires

Une autre solution intéressante!


@shnugo merci beaucoup! À quoi ressemblerait une simple jointure entre les deux résultats?