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!
3 Réponses :
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;
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.
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!
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
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.
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 ...
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 ...
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 ...
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.
Une autre solution intéressante!
@shnugo merci beaucoup! À quoi ressemblerait une simple jointure entre les deux résultats?
Vous pouvez utiliser
Tasks
ouParallel.ForEach
, exempleJe 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.