Sybase ASE 15.0.2 et les fonctions UDF (User Defined Functions) : performances et meilleures pratiques

Introduction

Les fonctions utilisateurs UDF (User-Defined functions) sont une nouveauté de Sybase ASE 15.0.2. Elles sont parfois utiles, mais il est essentiel de comprendre leur fonctionnement avant de les utiliser systématiquement. Les fonctions provoquent un traitement ligne à ligne, colonne par colonne de l’information.

Par exemple, si une fonction retourne son résultat en 4 E/S (I/O), alors ces 4 E/S seront reproduits autant de fois qu’il y a de lignes et autant de fois qu’il y a de colonnes appelant la fonction. Toute la puissance du traitement ensembliste est perdu.

Les fonctions UDF sont séduisantes mais il faut utiliser ces dernières avec précautions et basculer vers un mode ensembliste performant lorsque la volumétrie traitée devient de plus en plus importante. Cet article présente un cas pratique en comparant les performances entre fonctions et traitement ensembliste via une procédure stockée et les dérives sont très significatives lorsque la volumétrie augmente.

Cas pratique

Fonction UDF

La procédure sp_test_fonction appelle la fonction UDF f_get_agent_code_ctp pour 3 colonnes :

create procedure sp_test_fonction
as 
begin
 select top 100 AGENT_ID into #tmp from smartco.dbo.AGENT_REGISTERED_ROLES

 select
   a.AGENT_ID, 
   dbo.f_get_agent_code_ctp (a.AGENT_ID, 'BBG_CPNY')   AS BLOOMBERG_ID,
   dbo.f_get_agent_code_ctp (a.AGENT_ID, 'DEC_ISS_ID') AS DECALOG_ID,    
   dbo.f_get_agent_code_ctp (a.AGENT_ID, 'RED_CODE')   AS RED_CODE
 from    #tmp a

end
go

La fonction f_get_agent_code_ctp est très classique

create function f_get_agent_code_ctp( @agent_id int,
                                      @internal_code_item varchar(50))
returns varchar (20) 
as
begin
  declare @agent_code  varchar (20)
                   
  select  @agent_code = c.AGENT_CODE  
  from    smartco.dbo.AGENT_CODE c , 
            smartco.dbo.SMARTLIST_ITEM i
  where   c.AGENT_ID  = @agent_id 
  and     c.AGENT_CODE_TYPE=i.SMARTLIST_ITEM_ID
  and     i.INTERNAL_CODE=@internal_code_item 
         
       
  return @agent_code
end

Avec la commande set statistics io, l’appel de la fonction f_get_agent_code_ctp coûte 4 E/S, soit 12 E/S par ligne au sein de la procédure stockée sp_test_fonction.

Équivalent en mode ensembliste

Dans la plupart des cas, une solution en mode ensembliste est techniquement possible pour remplacer l’appel des fonctions. Voici l’équivalent de la procédure sp_test_fonction en mode ensembliste ;

create procedure sp_test_ensembliste
as 
begin
  select top 100 AGENT_ID into #tmp from smartco.dbo.AGENT_REGISTERED_ROLES
 
  select  ac.AGENT_ID,
  
          max(case it.INTERNAL_CODE
               when 'BBG_CPNY'   then ac.AGENT_CODE
               else null
              end ) 'BLOOMBERG_ID',
               
          max(case it.INTERNAL_CODE
               when 'DEC_ISS_ID' then ac.AGENT_CODE
               else null
              end ) 'DECALOG_ID',
               
          max(case it.INTERNAL_CODE
               when 'RED_CODE'   then ac.AGENT_CODE
               else null
              end ) 'RED_CODE'
               
  into   #agent_code
  from   #tmp  a
  inner join  smartco.dbo.AGENT_CODE  ac on ac.AGENT_ID=a.AGENT_ID
  inner join  smartco.dbo.SMARTLIST_ITEM  it
     on it.SMARTLIST_ITEM_ID = ac.AGENT_CODE_TYPE
  group by    ac.AGENT_ID    
 
  select  a.AGENT_ID, 
          ac.BLOOMBERG_ID, 
          ac.DECALOG_ID, 
          ac.RED_CODE
  from  #tmp a
  left join   #agent_code ac on ac.AGENT_ID=a.AGENT_ID

end
go

Évolution des performances avec le volume traité

Dans les 2 procédures stockées sp_test_fonction et sp_test_ensembliste, le périmètre est déterminé en prenant les n premières lignes (top 100) de la table de référence AGENT_REGISTERED_ROLES.

En faisant varier le périmètre, de 10 lignes à 10000 lignes, les coûts en E/S et les temps d’exécution sont mesurés (set statistics io, set statistics time on) :

Graphiquement, les résultats sont plus explicites :

Dans tous les tests, la procédure stockée en mode ensembliste a un temps d’exécution de moins d’une seconde, alors que celle utilisant les fonctions dérape jusqu’à atteindre 15 secondes pour 10 000 lignes traitées.

Les E/S sont dépendants du nombre de pages à lire en mode classique et dépendants du nombre de lignes en mode fonction. Dans ce dernier mode, 250 000 E/S sont réalisés pour à peine 10 000 lignes, mieux vaut ne pas avoir trop de traitements équivalents simultanés !