J'essaie de rendre ma requête lisible en utilisant UDF dans SQL SERVER mais le temps d'exécution augmente considérablement lors de l'utilisation de la fonction.
Voici la fonction que j'utilise:
SELECT
CASE WHEN replace(replace(replace([FULL_NAME_1],' ',''),')',''),'(','')=replace(replace(replace([FULL_NAME_2],' ',''),')',''),'(','') THEN 1 ELSE 0 END AS [name_match],
CASE WHEN replace(replace(replace([ADDRESS_1],' ',''),')',''),'(','')=replace(replace(replace([ADDRESS_2],' ',''),')',''),'(','') THEN 1 ELSE 0 END AS [adrs_match]
.
.
.
FROM
TABLE_1
4 Réponses :
Je ne peux pas imaginer un énorme coup de pouce, mais que diriez-vous d'une approche alternative
SELECT
DL.DoesItMatch([FULL_NAME_1],[FULL_NAME_2]) AS [name_match],
...
FROM
TABLE_1
Appelez ensuite la fonction comme suit:
create function DL.DoesItMatch(@s1 varchar(500),@s2 varchar(500))
returns bit
as begin
return CASE WHEN replace(replace(replace(@s1,' ',''),')',''),'(','')=replace(replace(replace(@s2,' ',''),')',''),'(','') THEN 1 ELSE 0 END
end
Cela devrait améliorer les performances ne serait-ce que pour le fait qu'il exécute l'UDF deux fois moins que la requête d'origine ...
@ZoharPeled Mes pensées aussi.
Désolé pour votre idée, j'espère que le vote positif pourra au moins compenser :-)
En poussant plus loin l'idée de John, en convertissant la fonction scalaire en une fonction de table en ligne et en utilisant cross apply pour l'activer pour chaque paire de colonnes - vous pourriez obtenir des performances encore meilleures, pour le prix d'une requête plus lourde:
pre> XXX
et la requête:
SELECT NameMatch.IsMatch AS [name_match],
AddressMatch.IsMatch AS adrs_match
.
.
.
FROM TABLE_1
CROSS APPLY DL.DoesItMatch(FULL_NAME_1, FULL_NAME_2) As NameMatch
CROSS APPLY DL.DoesItMatch(ADDRESS_1, ADDRESS_2) As AddressMatch
Je peux tester cela plus tard. Comme je l'ai mentionné dans les commentaires ci-dessus, je ne suis pas sûr que 20 applications croisées seraient plus performantes. De toute façon, +1 de moi aussi
Vous pouvez utiliser l'inlining Scalar UDF dans SQL Server 2019. Avec cela, vous pourrez conserver le même UDF que vous avez écrit et obtenir automatiquement les performances identiques à la requête sans l'UDF.
L'UDF que vous avez donné correspond aux critères d'inlinéabilité, vous êtes donc en bonne forme. La documentation sur la fonctionnalité d'inlining UDF se trouve ici: https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=azuresqldb-current
Conseil de pro: je vous suggère d'apporter une modification mineure à votre UDF avant d'utiliser l'inlining UDF scalaire. Faites-en une seule instruction scalaire UDF en évitant la variable locale. Avec cela, vous devriez être mieux loti que d'utiliser un TVF en ligne avec application croisée.
L'intégration est toujours la voie à suivre. Période. Même sans tenir compte des aspects inhibant le parallélisme des UDF scalaires T-SQL, les ITVF sont plus rapides, nécessitent moins de ressources (CPU, mémoire et E / S), plus faciles à entretenir et plus faciles à dépanner / analyser / profiler / tracer. Pour le plaisir, j'ai mis en place un test de performance comparant l'ITVF de Zohar à l'UDF scalaire de John. J'ai créé 250K lignes, testé une sélection de base contre les deux, puis un autre test avec un ORDER BY sur le tas pour forcer un tri.
Exemple de données:
scalar, no sort ------------------------------------------------------------ Beginning execution loop 844 843 840 Batch execution completed 3 times. ITVF, no sort ------------------------------------------------------------ Beginning execution loop 270 270 270 Batch execution completed 3 times. scalar, sorted set ------------------------------------------------------------ Beginning execution loop 937 930 936 Batch execution completed 3 times. ITVF, sorted set ------------------------------------------------------------ Beginning execution loop 196 190 190 Batch execution completed 3 times.
Test de performances:
PRINT 'scalar, no sort'+CHAR(10)+REPLICATE('-',60);
GO
DECLARE @st DATETIME = GETDATE(), @isMatch BIT;
SELECT @isMatch = DL.DoesItMatch(t.col1,t.col2)
FROM #tmp AS t;
PRINT DATEDIFF(MS,@st,GETDATE())
GO 3
PRINT CHAR(10)+'ITVF, no sort'+CHAR(10)+REPLICATE('-',60);
GO
DECLARE @st DATETIME = GETDATE(), @isMatch BIT;
SELECT @isMatch = f.isMatch
FROM #tmp AS t
CROSS APPLY DL.DoesItMatch_ITVF(t.col1,t.col2) AS f;
PRINT DATEDIFF(MS,@st,GETDATE())
GO 3
PRINT CHAR(10)+'scalar, sorted set'+CHAR(10)+REPLICATE('-',60);
GO
DECLARE @st DATETIME = GETDATE(), @isMatch BIT;
SELECT @isMatch = DL.DoesItMatch(t.col1,t.col2)
FROM #tmp AS t
ORDER BY DL.DoesItMatch(t.col1,t.col2);
PRINT DATEDIFF(MS,@st,GETDATE())
GO 3
PRINT CHAR(10)+'ITVF, sorted set'+CHAR(10)+REPLICATE('-',60);
GO
DECLARE @st DATETIME = GETDATE(), @isMatch BIT;
SELECT @isMatch = f.isMatch
FROM #tmp AS t
CROSS APPLY DL.DoesItMatch_ITVF(t.col1,t.col2) AS f
ORDER BY f.isMatch;
PRINT DATEDIFF(MS,@st,GETDATE())
GO 3
Test Résultats:
-- Sample Data
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#tmp','U') IS NOT NULL DROP TABLE #tmp;
SELECT TOP (250000) col1 = '('+LEFT(NEWID(),10)+')', col2 = '('+LEFT(NEWID(),10)+')'
INTO #tmp
FROM sys.all_columns a, sys.all_columns;
UPDATE #tmp SET col1 = col2 WHERE LEFT(col1,2) = LEFT(col2,2)
END
Ainsi, quand aucun plan parallèle n'est nécessaire, l'ITVF est 3 fois plus rapide, lorsqu'un plan parallèle est requis, il est 5 fois plus rapide. Voici quelques autres liens sur lesquels j'ai testé ITVF vs (UDF scalaires et multi-instructions).
Fonction définie par l'utilisateur du serveur SQL pour calculer la tranche d'âge
La fonction est lente mais la requête s'exécute rapidement
Pourquoi SQL Server dit-il que cette fonction n'est pas déterministe?
Regroupement basé sur le pourcentage de correspondance
Fonction définie par l'utilisateur SQL Server 2008 pour ajouter des espaces entre chaque chiffre Les valeurs séparées par des virgules du tableau SQL contiennent vérification de l'une des valeurs de variable
Manipulation de la chaîne SQL, trouver toutes les permutations
Les fonctions définies par l'utilisateur sont des tueurs de performances.
@GordonLinoff J'apprécierai un peu plus de détails et s'il y a un moyen de résoudre le problème? sinon, quel est l'intérêt de l'UDF?
Le but est de servir de pièges insidieux et d'expériences d'apprentissage précieuses pour des développeurs innocents ... mais sérieusement, les UDF scalaires sont parfaitement utilisables - tant que vous ne les utilisez jamais dans le traitement de requêtes de plus d'une poignée de lignes. Recherchez "scalar udf performance" dans n'importe quel moteur de recherche de votre choix pour trouver de nombreuses références sur ce sujet (ainsi que les améliorations apportées à SQL Server 2019, qui ne résolvent toujours pas tous les problèmes). La solution de contournement, si vous voulez absolument une fonction, consiste à utiliser des fonctions table en ligne ou des fonctions CLR, qui n'ont pas ces problèmes.
@JeroenMostert Vous avez raison, mais je soupçonne des rendements décroissants en utilisant un TVF sur 20 colonnes.
@JohnCappelletti: par rapport à une expression nue, certes, mais même le simple fait que les TVF en ligne n'empêchent pas le parallélisme peut faire la différence entre "inutilisablement lent" et "plus lent, mais au moins quelque chose avec lequel je peux vivre".
Une autre chose qui pourrait améliorer les performances est d'arrêter d'utiliser
varchar (max)et d'utiliser une longueur raisonnable à la place (c'est-à-dire, à moins que vous ne soupçonniez des chaînes contenant plus de 8000 caractères)