SQL Fonction à valeur de Table dans l'Instruction Select

SQL n'est pas mon truc mais j'ai essayé d'optimiser cette procédure stockée. Il avait plusieurs fonctions scalaires que j'ai essayé de modifier les fonctions de table parce que j'ai lu à plusieurs endroits que c'est un moyen plus efficace de le faire. Et maintenant, je les ai fait, mais pas vraiment sûr de la façon de mettre en œuvre ou si j'ai peut-être juste de ne pas créer correctement.

C'est la fonction que je vais appeler.

Alter FUNCTION [IsNotSenateActivityTableValue]
(
    @ActivityCode int,
    @BillId int,
    @TextToDisplay varchar(max)
)
returns @T table(result varchar(max))
as
begin
DECLARE @result varchar(max);
    declare @countcodes int;


declare @ishousebill int;

select @ishousebill = count(billid)
from BillMaster
where BillID = @BillID and Chamber = 'H'

If (@ishousebill = 0)
begin


SELECT @countcodes = count([ActivityCode])
      FROM [HouseCoreData].[dbo].[ActivityCode]
      where ActivityDescription not like '%(H)%' and ActivityType = 'S'
      and [ActivityCode] = @ActivityCode

if (@countcodes = 0)
begin
    set @result = 'test'
   end
  else
     begin
        set @result = 'test2'
    end
end
else
begin
    set @result = @TextToDisplay
end
RETURN 

END

Et c'est ce que j'ai essayé de les appeler comme ça. Je préfère juste être capable de les mettre dans le top mais vraiment quelque chose qui fonctionne serait bon.

SELECT distinct       
      ActionDates.result as ActionDate
      ,ActivityDescriptions.result as ActivityDescription        
  FROM BillWebReporting.vwBillDetailWithSubjectIndex as vw
  left outer join [BillWebReporting].[HasHouseSummary] as HasSummary on vw.BillID = HasSummary.BillID
  outer APPLY dbo.IsNotSenateActivityDateTableValue(ActivityCode,vw.BillID,[ActionDate]) ActionDates    
  OUTER APPLY dbo.IsNotSenateActivityTableValue(ActivityCode,vw.BillID,[ActivityDescription]) as ActivityDescriptions

Tag dit mysql, mais cela ressemble T-SQL et SQL Server.
Est-ce que votre fonction fonctionne réellement? Où avez-vous jamais insert into @T? Est-il seulement jamais signifié pour retourner une seule ligne?
Aussi, le type le plus efficace de la fonction table est une table en ligne de la fonction (qui n'a qu'une RETURN (SELECT ...); et aucun de ces autres morceaux de code). Un multi-déclaration de la fonction table, comme celui que vous êtes en train d'écrire, est en fait sujettes à un grand nombre des mêmes problèmes de performances que vous essayez d'éviter.
Ok ya je n'ai pas l'avoir à l'insérer dans t est le problème de son travail maintenant, mais je vois certaines personnes, ne pas l'aimer. Est-il une meilleure façon d'aller à ce sujet?
Veuillez vous abstenir de mettre des mots dans la bouche des gens. Personne n'a dit qu'ils n'aimaient pas votre code. Nous essayons simplement de vous aider, vous vous souvenez?
Eh bien, il a déclaré que son sujette à beaucoup de problèmes que j'essayais d'éviter. Donc, je demandais juste si il avait une meilleure solution. Je vais prendre tout ce constructif, et en essayant d'obtenir la meilleure solution. Pas acusing gens de rien, désolé.
Je viens de dire qu'un multi-déclaration de la table de l'UDF, il est peu probable pour résoudre quoi que ce soit (ni une table en ligne UDF).

InformationsquelleAutor Ryan Schlueter | 2013-10-11

0

Table fonctions retournent un tableau, dans lequel, comme n'importe quel autre tableau, les lignes doivent être insérés.

Au lieu de faire set @result = ....., n':
```
INSERT INTO @T (result) VALUES ( ..... )
```
MODIFIER: Comme une note de côté, je ne comprends pas vraiment la raison de cette fonction à valeur de table. Vous essentiellement revenir un valeur.
- Oui c'était le problème retourna et regarda après, j'ai vu certains de il les commentaires. Quelle serait la meilleure manière de le faire. J'étais juste à la recherche de solutions de rechange à utiliser pour accélérer Scalaire de fonctions à valeur.
- Retournant une valeur unique implique une fonction scalaire. Faisant table serait une complication inutile, peut-être insignifiant, d'accord, mais encore inutile. Je ne sais pas vraiment lequel est le plus rapide, mais je suppose que la différence serait insignifiant. Les problèmes de Performance doit être adressée en vue de l'optimisation du reste des objets utilisés à l'intérieur de la fonction.
- Sauf que, dans le but d'en faire un inline UDF, il est à table, pas scalaire, et inline fonctions définies par l'utilisateur sont beaucoup plus performant dans les scénarios où ils sont en train d'être exécutée plusieurs fois.
- Je n'ai jamais eu l'occasion de comparer ces approches thotoughly, en terme de performance. Merci pour vos commentaires. Néanmoins, je pense encore affiner les objets que la fonction utilise doit être le premier objectif d'un processus d'optimisation, comme ce serait bénéfique pour tous les scénarios.
InformationsquelleAutor geomagas
3

Obtenir un compte juste pour voir si au moins une ligne qui existe est très cher. Vous devez utiliser EXISTS au lieu de cela, ce qui peut potentiellement court-circuit sans matérialisant l'ensemble.

Ici est une façon plus efficace à l'aide d'une fonction table au lieu d'un multi-déclaration de la fonction table.
```
ALTER FUNCTION dbo.[IsNotSenateActivityTableValue] -- always use schema prefix!
(
    @ActivityCode int,
    @BillId int,
    @TextToDisplay varchar(max)
)
RETURNS TABLE
AS
  RETURN (SELECT result = CASE WHEN EXISTS 
    (SELECT 1 FROM dbo.BillMaster 
     WHERE BillID = @BillID AND Chamber = 'H'
  ) THEN @TextToDisplay ELSE CASE WHEN EXISTS 
    (SELECT 1 FROM [HouseCoreData].[dbo].[ActivityCode]
      where ActivityDescription not like '%(H)%' 
      and ActivityType = 'S'
      and [ActivityCode] = @ActivityCode
  ) THEN 'test2' ELSE 'test' END
  END);
GO
```
Bien sûr, il pourrait aussi être simplement un scalaire UDF...
```
ALTER FUNCTION dbo.[IsNotSenateActivityScalar] -- always use schema prefix!
(
    @ActivityCode int,
    @BillId int,
    @TextToDisplay varchar(max)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE @result VARCHAR(MAX);

  SELECT @result = CASE WHEN EXISTS 
    (SELECT 1 FROM dbo.BillMaster 
     WHERE BillID = @BillID AND Chamber = 'H'
  ) THEN @TextToDisplay ELSE CASE WHEN EXISTS 
    (SELECT 1 FROM [HouseCoreData].[dbo].[ActivityCode]
      where ActivityDescription not like '%(H)%' 
      and ActivityType = 'S'
      and [ActivityCode] = @ActivityCode
  ) THEN 'test2' ELSE 'test' END
  END;

  RETURN (@result);
END
GO
```
- Il était dans un scalaire de l'udf, mais je l'ai pris pour essayer d'accélérer les performances. Vous pensez que si j'ai essayé ceci dans la fonction qu'il permettrait d'améliorer le rendement suffisant pour être utilisable.
- Je doute que votre choix de scalaires de l'UDF ou de table UDF va faire une différence notable sur la performance - c'est probablement dû à beaucoup plus gros frappeurs des pauvres / non indexes, mauvaises stats, l'insuffisance de la mémoire, etc. Lorsque vous exécutez cette requête, de générer un plan d'exécution réel, et voir si quelque chose se démarque (comme très coûteux pour les scans, les index manquants recommandations, etc).
- Aaron, Dans Sql Server, à l'aide d'un inline UDF est beaucoup plus rapide qu'un multi-déclaration de l'UDF, et dans les cas où l'UDF est appelé plusieurs fois (disons une fois pour chaque ligne de la requête externe dans lequel il est intégré), il peut faire une énorme différence. N'est-ce pas le cas dans mySQL?
- pas sûr de ce que cela a à voir avec MySQL. Je suis d'accord avec vous qu'une ligne UDF est mieux (et c'est pourquoi j'ai proposé et écrit une ligne version UDF), je ne pense pas qu'il va automatiquement corriger toutes les OP les problèmes de performances avec cette requête. Il peut faire prendre 30 secondes au lieu de 34 secondes, mais ne pas prendre le reste de la voie. Je peux me tromper mais je doute que le problème de performances qui a conduit l'OP ici les résultats d'un mauvais type de fonction.
InformationsquelleAutor Aaron Bertrand

Première de toutes les fonctions définies par l'utilisateur sont généralement de non-performant. Je ne suis pas sûr à propos de MySQL, mais dans Sql Server UDF est recompilé à chaque fois (POUR CHAQUE LIGNE DE SORTIE), il est exécutée, à l'exception de ce que l'on appelle inline Udf, qui ne disposent que d'une seule instruction select, qui est plié dans le SQL de la requête externe, il est inclus dans... et il est donc seulement une fois compilé.

MySQL n'ont les fonctions table en ligne, l'utiliser à la place... dans SQL Server, la syntaxe sera:

CREATE FUNCTION IsNotSenateActivityTableValue
(
@ActivityCode int,
@BillId int,
@TextToDisplay varchar(max)
)
RETURNS TABLE 
AS
RETURN 
(
Select case 
   When y.bilCnt + z.actCnt = 0 Then 'test'
   when y.bilCnt = 0 then 'test2'
   else @TextToDisplay end result
From (Select Count(billId) bilCnt
      From BillMaster
      Where BillID = @BillID 
         And Chamber = 'H') y
    Full Join 
     (Select count([ActivityCode]) actCnt
      From [HouseCoreData].[dbo].[ActivityCode]
      Where ActivityDescription not like '%(H)%' 
         And ActivityType = 'S'
         And [ActivityCode] = @ActivityCode) z

)
GO

InformationsquelleAutor Charles Bretana

Vous devez vous connecter pour publier un commentaire.