ماهو - شرح sql




SQL ، جدول الأرقام المساعدة (5)

باستخدام SQL Server 2016+ لإنشاء جدول أرقام ، يمكنك استخدام OPENJSON :

-- range from 0 to @max - 1
DECLARE @max INT = 40000;

SELECT rn = CAST([key] AS INT) 
FROM OPENJSON(CONCAT('[1', REPLICATE(CAST(',1' AS VARCHAR(MAX)),@max-1),']'));

LiveDemo

فكرة مأخوذة من كيف يمكننا استخدام OPENJSON لإنشاء سلسلة من الأرقام؟

بالنسبة لأنواع معينة من استعلامات SQL ، يمكن أن تكون جدول الأرقام المساعد مفيدًا جدًا. قد يتم إنشاؤه كجدول مع عدد الصفوف التي تحتاجها لمهمة معينة أو كدالة معرفة من قبل المستخدم تقوم بإرجاع عدد الصفوف المطلوبة في كل استعلام.

ما هي الطريقة المثلى لإنشاء مثل هذه الوظيفة؟


تحرير: انظر تعليق كونراد أدناه.

جواب جيف مودن رائع ... ولكن أجد على بوستجرس أن يفشل الأسلوب ايتسيك إلا إذا قمت بإزالة الصف E32.

أسرع قليلا على postgres (40ms مقابل 100ms) هي طريقة أخرى وجدت في here تتكيف مع 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

أثناء انتقالي من SQL Server إلى Postgres world ، قد فاتني طريقة أفضل للقيام الجداول التقديرية على ذلك النظام الأساسي ... INTEGER ()؟ تسلسل()؟


هذا العرض سريع جدًا ويحتوي على جميع قيم int موجبة.

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

هيه ... آسف أنا متأخر جدا الرد على وظيفة قديمة. و ، نعم ، كان عليّ الاستجابة لأن الإجابة الأكثر شيوعًا (في ذلك الوقت ، إجابة CTE العودية مع وصلة 14 طريقة مختلفة) على هذا الموضوع هي ، ummm ... الأداء في أفضل حالاته.

أولاً ، المقالة مع 14 حلول مختلفة على ما يرام لرؤية الطرق المختلفة لإنشاء جدول الأرقام / تالي على الطاير ولكن كما هو موضح في المقالة وفي الخيط المذكور ، هناك اقتباس مهم جدا ...

"الاقتراحات المتعلقة بالكفاءة والأداء غالبًا ما تكون ذاتية. وبغض النظر عن كيفية استخدام الاستعلام ، فإن التنفيذ الفعلي يحدد كفاءة الاستعلام. لذلك ، بدلاً من الاعتماد على إرشادات متحيزة ، من الضروري اختبار الاستعلام وتحديد أيها يعمل بشكل أفضل. "

ومن المفارقات ، أن المقالة نفسها تحتوي على العديد من التصريحات الذاتية و "المبادئ التوجيهية المتحيزة" مثل "يمكن أن تقوم CTE العودية بتوليد رقم يتم سرده بكفاءة عالية " و "هذه طريقة فعالة لاستخدام حلقة WHILE من نشر مجموعة أخبار بواسطة Itzik Ben-Gen" ( التي أنا متأكد من أنه نشر فقط لأغراض المقارنة). همم الناس ... مجرد ذكر اسم ايتسيك الجيد قد يؤدي إلى بعض الساذج الفقراء في الواقع باستخدام هذه الطريقة الرهيبة. يجب على المؤلف أن يمارس ما (أو أشياء) يعظ بها ويجب عليه القيام باختبار بسيط للأداء قبل أن يدلي بمثل هذه التصريحات غير الصحيحة بشكل خاطئ خاصة في مواجهة أي قابلية.

من خلال التفكير في إجراء بعض الاختبارات فعليًا قبل إجراء أي ادعاءات شخصية حول ما تفعله أي شفرة أو ما يعجبه شخص ما ، إليك بعض التعليمات البرمجية التي يمكنك إجراؤها باستخدام الاختبار الخاص بك. برنامج الإعداد منشئ التعريف لـ SPID الذي تقوم بتشغيل الاختبار منه والتحقق من ذلك بنفسك ... فقط قم بإجراء "Search'n'Replace" رقم 1000000 لرقم "المفضلة" الخاص بك ثم راجع ...

--===== 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

بينما نحن في ذلك ، وهنا الأرقام التي أحصل عليها من SQL Profiler لقيم 100 ، 1000 ، 10000 ، 100000 ، و 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

كما ترى ، فإن طريقة CTE Recursive هي الطريقة الأسوأ الثانية فقط في فترة التكرار أثناء مدة الدورة ووحدة المعالجة المركزية (CPU) ولديها 8 أضعاف ضغط الذاكرة في شكل القراءات المنطقية مقارنةً بـ While Loop . انها RBAR على المنشطات وينبغي تجنبها ، بأي ثمن ، لأي حسابات صف واحد فقط كما ينبغي تجنبها في حين حلقة. هناك أماكن يكون فيها العودية ذات قيمة كبيرة ، لكن هذا ليس واحدًا منها .

وكشريط جانبي ، فإن السيد ديني على وجه التحديد ... رقم دائم للأرقام أو جدول تالي هو الطريق المناسب لمعظم الأشياء. ماذا يعني الحجم الصحيح؟ حسنا ، معظم الناس يستخدمون جدول تالي لتوليد التواريخ أو للقيام بتقسيم على VARCHAR (8000). إذا قمت بإنشاء جدول Tally مكون من 11000 صف مع الفهرس العنقودي الصحيح على "N" ، فسوف يكون لديك صفوف كافية لإنشاء أكثر من 30 عامًا من التواريخ (أعمل مع الرهون العقارية بشكل عادل حتى 30 عامًا هو رقم أساسي بالنسبة لي ) وبالتأكيد ما يكفي للتعامل مع تقسيم VARCHAR (8000). لماذا "التحجيم الصحيح" مهم جدا؟ إذا تم استخدام الجدول Tally كثيرًا ، فإنه يتسع بسهولة في ذاكرة التخزين المؤقت مما يجعله سريعًا بشكل صارخ دون ضغط كبير على الذاكرة على الإطلاق.

أخيرًا وليس آخرًا ، يعرف كل واحد أنه إذا قمت بإنشاء جدول تالٍ دائم ، فلا يهم كثيراً الطريقة التي تستخدمها في بنائه لأنه 1) سيتم إجراؤه مرة واحدة و 2) إذا كان هناك شيء مثل صف 11000 الجدول ، جميع الطرق سوف تعمل "جيدة بما فيه الكفاية". فلماذا كل التحسس من جانبي حول طريقة استخدام ؟؟؟

والجواب هو أن بعض الأشخاص المساكين / الغالبين الذين لا يعرفون أي شيء بشكل أفضل ويحتاجون فقط لإنجاز مهمته قد يرون شيئًا مثل طريقة تكرس CTE ويقررون استخدامه لشيء أكبر بكثير وأكثر استخدامًا كثيرًا من البناء جدول تالي دائم وأنا أحاول حماية هؤلاء الأشخاص ، الخوادم التي يعمل عليها الكود ، والشركة التي تمتلك البيانات على تلك الخوادم . نعم ... إنها صفقة كبيرة. يجب أن يكون للآخرين ، كذلك. تعليم الطريقة الصحيحة لفعل الأشياء بدلاً من "جيد بما فيه الكفاية". قم بإجراء بعض الاختبارات قبل نشر أو استخدام شيء ما من منشور أو كتاب ... قد تكون الحياة التي توفرها ، في الواقع ، خاصة بك إذا كنت تعتقد أن CTE العودية هي الطريقة التي يمكنك اتباعها للقيام بشيء كهذا. ؛-)

شكرا على الإنصات...


توفر هذه المقالة 14 حلول مختلفة ممكنة مع مناقشة كل منها. النقطة المهمة هي:

الاقتراحات المتعلقة بالكفاءة والأداء غالبًا ما تكون ذاتية. بغض النظر عن كيفية استخدام الاستعلام ، يحدد التنفيذ الفعلي كفاءة الاستعلام. لذلك ، بدلاً من الاعتماد على إرشادات متحيزة ، من الضروري اختبار الاستعلام وتحديد أيهما أفضل.

أنا شخصيا أحب:

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




sql-server