SQL, Table auxiliaire des nombres

Question

Pour certains types de requêtes sql, une table auxiliaire de chiffres peut être très utile. Elle peut être créée sous la forme d'une table comportant autant de lignes que nécessaire pour une tâche particulière ou sous la forme d'une fonction définie par l'utilisateur qui renvoie le nombre de lignes requises dans chaque requête.

Quelle est la manière optimale de créer une telle fonction ?

Answer 1

Heh... désolé d'avoir répondu si tard à un vieux message. Et, oui, je devais répondre parce que la réponse la plus populaire (à l'époque, la réponse CTE récursif avec le lien vers 14 méthodes différentes) sur ce fil de discussion est, ummm... au mieux, un problème de performance.

Tout d'abord, l'article avec les 14 solutions différentes est très bien pour voir les différentes méthodes de création d'un tableau de nombres/tableaux de pointage à la volée, mais comme indiqué dans l'article et dans le fil de discussion cité, il y a un très citation importante...

"Les suggestions concernant l'efficacité et la performances sont souvent subjectives. Indépendamment de la façon dont une requête est utilisée, l'implémentation physique détermine l'efficacité d'une requête. Par conséquent, plutôt que de s'appuyer sur directives biaisées, il est impératif de que vous testiez la requête et déterminiez laquelle est la plus performante."

Ironiquement, l'article lui-même contient de nombreuses déclarations subjectives et des "directives biaisées" telles que "un CTE récursif peut générer un listing de nombres de manière assez efficace " et "C'est une méthode efficace de l'utilisation de la boucle WHILE à partir d'un message d'Itzik Ben-Gen sur un groupe de discussion". (que je suis sûr qu'il a posté juste à titre de comparaison). Allez les gars... Le simple fait de mentionner le nom d'Itzik peut inciter un pauvre bougre à utiliser cette horrible méthode. L'auteur devrait pratiquer ce qu'il prêche et devrait faire un peu de tests de performance avant de faire des déclarations aussi ridiculement incorrectes, surtout face à la scalabilité.

Dans l'idée de faire des tests avant de faire des déclarations subjectives sur ce que fait un code ou ce que quelqu'un "aime", voici un code avec lequel vous pouvez faire vos propres tests. Configurez le profileur pour le SPID à partir duquel vous exécutez le test et vérifiez par vous-même... faites simplement un "Search'n'Replace" du nombre 1000000 pour votre nombre "préféré" et voyez...

--===== Test for 1000000 rows ==================================
GO
--===== Traditional RECURSIVE CTE method
   WITH Tally (N) AS 
        ( 
         SELECT 1 UNION ALL 
         SELECT 1 + N FROM Tally WHERE N < 1000000 
        ) 
 SELECT N 
   INTO #Tally1 
   FROM Tally 
 OPTION (MAXRECURSION 0);
GO
--===== Traditional WHILE LOOP method
 CREATE TABLE #Tally2 (N INT);
    SET NOCOUNT ON;
DECLARE @Index INT;
    SET @Index = 1;
  WHILE @Index <= 1000000 
  BEGIN 
         INSERT #Tally2 (N) 
         VALUES (@Index);
            SET @Index = @Index + 1;
    END;
GO
--===== Traditional CROSS JOIN table method
 SELECT TOP (1000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS N
   INTO #Tally3
   FROM Master.sys.All_Columns ac1
  CROSS JOIN Master.sys.ALL_Columns ac2;
GO
--===== Itzik's CROSS JOINED CTE method
   WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
 SELECT N
   INTO #Tally4
   FROM cteTally
  WHERE N <= 1000000;
GO
--===== Housekeeping
   DROP TABLE #Tally1, #Tally2, #Tally3, #Tally4;
GO

Pendant que nous y sommes, voici les chiffres que j'obtiens de SQL Profiler pour les valeurs 100, 1000, 10000, 100000 et 1000000...

SPID TextData                                 Dur(ms) CPU   Reads   Writes
---- ---------------------------------------- ------- ----- ------- ------
  51 --===== Test for 100 rows ==============       8     0       0      0
  51 --===== Traditional RECURSIVE CTE method      16     0     868      0
  51 --===== Traditional WHILE LOOP method CR      73    16     175      2
  51 --===== Traditional CROSS JOIN table met      11     0      80      0
  51 --===== Itzik's CROSS JOINED CTE method        6     0      63      0
  51 --===== Housekeeping   DROP TABLE #Tally      35    31     401      0

  51 --===== Test for 1000 rows =============       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method      47    47    8074      0
  51 --===== Traditional WHILE LOOP method CR      80    78    1085      0
  51 --===== Traditional CROSS JOIN table met       5     0      98      0
  51 --===== Itzik's CROSS JOINED CTE method        2     0      83      0
  51 --===== Housekeeping   DROP TABLE #Tally       6    15     426      0

  51 --===== Test for 10000 rows ============       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method     434   344   80230     10
  51 --===== Traditional WHILE LOOP method CR     671   563   10240      9
  51 --===== Traditional CROSS JOIN table met      25    31     302     15
  51 --===== Itzik's CROSS JOINED CTE method       24     0     192     15
  51 --===== Housekeeping   DROP TABLE #Tally       7    15     531      0

  51 --===== Test for 100000 rows ===========       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method    4143  3813  800260    154
  51 --===== Traditional WHILE LOOP method CR    5820  5547  101380    161
  51 --===== Traditional CROSS JOIN table met     160   140     479    211
  51 --===== Itzik's CROSS JOINED CTE method      153   141     276    204
  51 --===== Housekeeping   DROP TABLE #Tally      10    15     761      0

  51 --===== Test for 1000000 rows ==========       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method   41349 37437 8001048   1601
  51 --===== Traditional WHILE LOOP method CR   59138 56141 1012785   1682
  51 --===== Traditional CROSS JOIN table met    1224  1219    2429   2101
  51 --===== Itzik's CROSS JOINED CTE method     1448  1328    1217   2095
  51 --===== Housekeeping   DROP TABLE #Tally       8     0     415      0

Comme vous pouvez le voir, la méthode CTE récursive est la deuxième plus mauvaise après la boucle While en termes de durée et de CPU et exerce une pression sur la mémoire sous forme de lectures logiques 8 fois plus importante que la boucle While. . C'est du RBAR sous stéroïdes et il faut l'éviter, à tout prix, pour tout calcul sur une seule ligne, tout comme il faut éviter une boucle While. Il y a des endroits où la récursion est très utile, mais ce n'est pas le cas ici. .

En passant, M. Denny a tout à fait raison... une table permanente de nombres ou de pointage de taille correcte est la meilleure solution pour la plupart des choses. Qu'entendez-vous par "taille correcte" ? Eh bien, la plupart des gens utilisent une table Tally pour générer des dates ou pour effectuer des fractionnements sur VARCHAR(8000). Si vous créez une table Tally de 11 000 lignes avec l'index clusterisé correct sur "N", vous aurez suffisamment de lignes pour créer des dates sur plus de 30 ans (je travaille souvent avec des hypothèques, donc 30 ans est un chiffre clé pour moi) et certainement assez pour gérer une division VARCHAR(8000). Pourquoi le " bon dimensionnement " est-il si important ? Si la table Tally est beaucoup utilisée, elle peut facilement être mise en cache, ce qui la rend extrêmement rapide, sans aucune pression sur la mémoire.

Enfin, chacun sait que si vous créez une table Tally permanente, la méthode utilisée pour la construire n'a pas beaucoup d'importance car 1) elle ne sera créée qu'une seule fois et 2) s'il s'agit d'une table de 11 000 lignes, toutes les méthodes fonctionneront "suffisamment bien". Alors pourquoi toute cette indignation de ma part quant à la méthode à utiliser ???

La réponse est qu'un pauvre gars/une pauvre fille qui ne connaît pas mieux et qui a juste besoin de faire son travail pourrait voir quelque chose comme la méthode CTE récursive et décider de l'utiliser pour quelque chose de beaucoup plus grand et beaucoup plus fréquemment utilisé que la construction d'une table Tally permanente et j'essaie de protéger ces personnes, les serveurs sur lesquels leur code est exécuté et l'entreprise qui possède les données sur ces serveurs. . Ouais... c'est un gros problème. Ça devrait l'être pour tous les autres aussi. Enseignez la bonne façon de faire les choses au lieu du "suffisant". Faites des essais avant de publier ou d'utiliser quelque chose tiré d'un article ou d'un livre... la vie que vous sauvez peut, en fait, être la vôtre, surtout si vous pensez qu'un CTE récursif est la solution pour quelque chose comme ça ;-)

Merci d'avoir écouté...

Answer 2

La fonction la plus optimale serait d'utiliser un tableau au lieu d'une fonction. L'utilisation d'une fonction entraîne une charge supplémentaire pour le processeur, qui doit créer les valeurs pour les données renvoyées, en particulier si les valeurs renvoyées couvrent une très large plage.

Answer 3

Cet article donne 14 solutions possibles différentes avec une discussion sur chacune d'elles. Le point important est le suivant :

Les suggestions concernant l'efficacité et la performances sont souvent subjectives. Indépendamment de la façon dont une requête est utilisée, l'implémentation physique détermine l'efficacité d'une requête. Par conséquent, plutôt que de s'appuyer sur directives biaisées, il est impératif de que vous testiez la requête et déterminiez laquelle est la plus performante.

J'ai personnellement aimé :

WITH Nbrs ( n ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )

Answer 4

Cette vue est super rapide et contient tous les positifs int valeurs.

CREATE VIEW dbo.Numbers
WITH SCHEMABINDING
AS
    WITH Int1(z) AS (SELECT 0 UNION ALL SELECT 0)
    , Int2(z) AS (SELECT 0 FROM Int1 a CROSS JOIN Int1 b)
    , Int4(z) AS (SELECT 0 FROM Int2 a CROSS JOIN Int2 b)
    , Int8(z) AS (SELECT 0 FROM Int4 a CROSS JOIN Int4 b)
    , Int16(z) AS (SELECT 0 FROM Int8 a CROSS JOIN Int8 b)
    , Int32(z) AS (SELECT TOP 2147483647 0 FROM Int16 a CROSS JOIN Int16 b)
    SELECT ROW_NUMBER() OVER (ORDER BY z) AS n
    FROM Int32
GO

Answer 5

edit : voir le commentaire de Conrad ci-dessous.

La réponse de Jeff Moden est excellente... mais je trouve que sur Postgres, la méthode d'Itzik échoue à moins de supprimer la ligne E32.

Légèrement plus rapide sur postgres (40ms contre 100ms) est une autre méthode que j'ai trouvé sur ici adapté pour postgres :

WITH 
    E00 (N) AS ( 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),
    E01 (N) AS (SELECT a.N FROM E00 a CROSS JOIN E00 b),
    E02 (N) AS (SELECT a.N FROM E01 a CROSS JOIN E01 b ),
    E03 (N) AS (SELECT a.N FROM E02 a CROSS JOIN E02 b 
        LIMIT 11000  -- end record  11,000 good for 30 yrs dates
    ), -- max is 100,000,000, starts slowing e.g. 1 million 1.5 secs, 2 mil 2.5 secs, 3 mill 4 secs
    Tally (N) as (SELECT row_number() OVER (ORDER BY a.N) FROM E03 a)

SELECT N
FROM Tally

Comme je passe de SQL Server au monde de Postgres, j'ai peut-être manqué une meilleure façon de faire des tableaux de décompte sur cette plateforme ... INTEGER() ? SEQUENCE() ?