Dev Faq
3
votes

Réglage des performances sur la requête de correspondance de modèle

J'ai deux tableaux:

Tableau 1: 100 lignes

Tableau 2:10 millions de lignes

Exemple:

Tableau 1: tb100 

;with splitdata as
(
    select f.item as data
    from tb100 t
    cross apply dbo.udf_split(t.name,' ') f
)
select t2.name 
from tb10mil t2
inner join splitdata c on charindex(c.data,t2.name)>0
group by t2.name

Tableau 2: tb10mil

CREATE FUNCTION [dbo].[udf_Split]
(
@InputString VARCHAR(8000), 
@Delimiter VARCHAR(50)
)
RETURNS @Items TABLE (ID INTEGER IDENTITY(1,1), Item VARCHAR(8000))

AS
BEGIN
      IF @Delimiter = ' '
      BEGIN
            SET @Delimiter = ','
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
      END
      IF (@Delimiter IS NULL OR @Delimiter = '')
            SET @Delimiter = ','

      DECLARE @Item VARCHAR(8000)
      DECLARE @ItemList VARCHAR(8000)
      DECLARE @DelimIndex INT

      SET @ItemList = @InputString
      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      WHILE (@DelimIndex != 0)
      BEGIN
            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
            INSERT INTO @Items VALUES (@Item)

            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      END 

      IF @Item IS NOT NULL 
      BEGIN
            SET @Item = @ItemList
            INSERT INTO @Items VALUES (@Item)
      END

      ELSE INSERT INTO @Items VALUES (@InputString)

      RETURN

END

Remarque: I voulez faire correspondre le nom entre deux tables, le cas échéant WORD (John, Smith, Will) présent dans une autre table. Par exemple John présent dans John A Mark .

Mon essai:

J'ai d'abord créé une fonction définie par l'utilisateur pour diviser le nom de tb100 en lignes.

Fonction: udf_Split

create table tb10mil
(
    name varchar(50)
);

insert into tb10mil values('John A Mak'),('K Smith Will'),('James Henry')......10 millions rows.    

create nonclustered index nci_tb10mil_name  on tb10mil(name);

Et puis j'ai écrit la requête suivante: 

create table tb100
(
    name varchar(50)
);

insert into tb100 values('Mak John'),('Will Smith'),('Luke W')......100 rows.

La requête ci-dessus prend plus de 20 minutes à s'exécuter.

5 commentaires

Pourquoi utilisez-vous une boucle WHILE pour votre séparateur? Il existe de nombreuses solutions de jeux de données qui seront beaucoup plus rapides. Je suggérerais d'utiliser un séparateur XML ou delimitedsplit8k (si ce n'est pas un nvarchar (MAX) ). Ce séparateur est très probablement votre (premier) problème.

Tout d'abord, SQL n'est pas un langage de traitement de texte, vous ne pouvez donc pas vous attendre à de bonnes performances de fractionnement de texte. Deuxièmement, sans index, vous ne pouvez pas améliorer les performances et cette fonction ne peut pas utiliser d'index. Enfin, SQL Server dispose déjà de capacités de recherche de texte intégral et de correspondance et de recherches floues dans SSIS. Si vous souhaitez faire correspondre les noms de manière floue lors du chargement de 10 millions de lignes, utilisez un opérateur de recherche floue dans SSIS

Si vous souhaitez faire correspondre par exemple une table de ventes avec un ensemble d'enregistrements clients par nom, faites-le lors du chargement des données et stockez uniquement les ID réels dans la table cible. N'essayez pas de répéter la recherche floue chaque fois que vous souhaitez rejoindre les tables. Quelle que soit la technique que vous utilisez, une correspondance floue sera toujours plus lente qu'une jointure sur deux colonnes indexées

Si cette requête est fréquemment utilisée, il est préférable de conserver une table de mappage à partir d'un déclencheur AFTER sur les tables principales et de conserver les données fractionnées en tant que valeurs de colonne de table. Les jointures peuvent alors être construites sur cette nouvelle table de mappage

Pour diviser une chaîne à l'aide de la méthode SQL FOR XML PATH, la fonction UDF suivante peut être une alternative kodyaz.com/articles/...

3 Réponses :

2
votes

Vous pouvez essayer comme suit. 

  ;WITH splitdata 
     AS (SELECT splitname 
         FROM   (SELECT *, 
                        Cast('<X>' + Replace(F.Name, ' ', '</X><X>') + '</X>' AS XML) 
                        AS  xmlfilter 
                 FROM   tb100 F)F1 
                CROSS apply (SELECT fdata.d.value('.', 'varchar(50)') AS splitName 
                             FROM   f1.xmlfilter.nodes('X') AS fdata(d)) O) 
SELECT DISTINCT t2.NAME 
FROM   tb10mil t2 
       INNER JOIN splitdata S 
               ON T2.NAME LIKE '%' + S.splitname + '%'

2 commentaires

Je doute vraiment qu'un JOIN par LIKE sur un motif avec un % en tête soit rapide avec des millions de lignes ... Et juste pour mentionner ceci: Si les noms contiennent des caractères interdits (comme <,> ou & et bien d'autres), cela peut casser sur CAST () . Et un autre indice sur les performances: GROUP BY est beaucoup plus rapide que DISTINCT .

LIKE n'a rien à voir avec les performances dans cette réponse. C'est la méthode différente de fractionnement de chaînes qui a amélioré les performances. Ce qui sera toujours horrible par rapport à l'utilisation de recherches appropriées lors du chargement des données

4
votes

Parlons de la performance

  • Le premier point est: essayez d'éviter scalaires et essayez d'éviter les multi-instructions-TVF si possible. Le seul l'approche rapide est une TVF en ligne (une déclaration en une ligne ).

  • Deuxième point: évitez les boucles si possible!

  • Le troisième point ( le tout premier en fait ): essayez de stocker vos données dans un format optimisé pour les requêtes rapides. Stockage de plus d'une valeur dans une cellule viole 1NF et un énorme tueur de vitesse.

Vous pouvez essayer ceci:

Simulez vos tables 

DROP TABLE #t100;
GO
DROP TABLE #t1M;
GO
DROP TABLE #t100Splitted;
GO
DROP TABLE #t1MSplitted;
GO

--Créez des tables pour stocker les fragments de nom de manière spear (c'est le format que vous devriez utiliser à la place) 

SELECT t1.ID_t1M
FROM #t1MSplitted t1
INNER JOIN #t100Splitted t2 ON t1.Fragment=t2.Fragment
GROUP BY t1.ID_t1M
GO

- Cette requête renverra toutes les lignes avec des fragments communs
- Vous pouvez rejoindre à nouveau vos tables source pour récupérer les valeurs
- Vous pouvez grouper par t2.ID_t100 afin d'obtenir les identifiants de la plus petite table (beaucoup plus rapide) 

CREATE TABLE #t100Splitted(ID INT IDENTITY PRIMARY KEY,ID_t100 INT,Fragment NVARCHAR(200));

--Use an inline XML-splitter
INSERT INTO #t100Splitted(ID_t100,Fragment)
SELECT ID
      ,B.frg.value('text()[1]','nvarchar(200)')
FROM #t100
CROSS APPLY(SELECT CAST('<x>' + REPLACE((SELECT SomeName AS [*] FOR XML PATH('')),' ','</x><x>') + '</x>' AS XML)) A(CastedToXml)
CROSS APPLY A.CastedToXml.nodes('/x[text()]') B(frg);

--add indexes
CREATE INDEX IX_t100_ID_t100 ON #t100Splitted(ID_t100);
CREATE INDEX IX_t100_Fragment ON #t100Splitted(Fragment);

--The same for the second table
CREATE TABLE #t1MSplitted(ID INT IDENTITY PRIMARY KEY,ID_t1M INT,Fragment NVARCHAR(200));

INSERT INTO #t1MSplitted(ID_t1M,Fragment)
SELECT ID
      ,B.frg.value('text()[1]','nvarchar(200)')
FROM #t1M
CROSS APPLY(SELECT CAST('<x>' + REPLACE((SELECT SomeName AS [*] FOR XML PATH('')),' ','</x><x>') + '</x>' AS XML)) A(CastedToXml)
CROSS APPLY A.CastedToXml.nodes('/x[text()]') B(frg);

CREATE INDEX IX_tM_ID_t100 ON #t1MSplitted(ID_t1M);
CREATE INDEX IX_tM_Fragment ON #t1MSplitted(Fragment);
GO

- Check the intermediate results
SELECT * FROM #t100Splitted;
SELECT * FROM #t1MSplitted;
GO

--Nettoyer 

CREATE TABLE #t100(ID INT IDENTITY,SomeName VARCHAR(200));
CREATE TABLE #t1M (ID INT IDENTITY,SomeName VARCHAR(200));

INSERT INTO #t100 VALUES('james smith'),('mak john'),('Luke W');
GO
INSERT INTO #t1M values('John A Mak'),('K Smith Will'),('James Henry'),('James John'),('Some other');
GO

Sur mon système, 1 million de lignes ont été traitées en 2 minutes environ.

MISE À JOUR -Tests de performances avec 100 lignes contre 10 millions de lignes

(avec de très nombreux appels)

  • Juste le fractionnement , pour transformer vos données en une meilleure forme: ~ 17 minutes

  • La sélection finale (juste la recherche):

  • La sélection finale mais recherche des ID de la petite table: quelques secondes
    (une fois que vous aurez transformé vos données, ce sera la vitesse normale )

  • Approche du PSK (avec fractionnement en ligne): ~ 30 minutes

0 commentaires
0
votes

J'ai essayé de SAUVEGARDER LA MÉMOIRE et ainsi gagner du temps pour gérer la mémoire en évitant les jointures. J'ai essayé de simuler votre problème sur des enregistrements ~ 420k avec des valeurs 4 dans un tableau plus petit.

L'approche consiste à éviter les jointures et à limiter les problèmes de mémoire espace de mxn à au moins plus grand de m & n. 

select DISTINCT t2.name
from tb10mil  t2
where (SELECT TOP(1) 1 FROM #splitdata where CHARINDEX(data,t2.Problem)>0)=1

RÉSULTAT: la moitié du temps nécessaire à l'approche utilisé en question. (réduit de ~ 28 s à ~ 14 s)

CON: L'approche n'est bénéfique que si l'un des tableaux est considérablement petit

0 commentaires

Articles qui pourrait vous intéresser :

Synchronisation des données entre les tables de SQL Server et Db2 - Comment supprimer des lignes
Est-il possible d'utiliser ensemble Entity Framework et un projet de base de données?
Vérification de la concurrence sur une entité sans mettre à jour la version de ligne
Liaison Docker SQL: une tentative a été faite pour accéder à une socket d'une manière interdite par ses autorisations d'accès