مشاركة عبر

تحسين العمليات في مجموعة SQL المخصصة في Azure Synapse Analytics

تعرف على كيفية تحسين أداء رمز العمليات الخاص بك في تجمع SQL مخصص مع تقليل المخاطر لعمليات التراجع الطويلة.

العمليات والتسجيل

العمليات هي مكون مهم من محرك تجمع SQL الارتباطي. يتم استخدام العمليات أثناء تعديل البيانات. يمكن أن تكون هذه العمليات صريحة أو ضمنية. تعد عبارات INSERT وUPDATE وDELETE المفردة كلها أمثلة على المعاملات الضمنية. تستخدم العمليات الصريحة BEGIN TRAN أو COMMIT TRAN أو ROLLBACK TRAN. عادة ما تستخدم العمليات الصريحة عندما يتطلب الأمر ربك عبارات تعديل متعددة معًا في وحدة ذرية واحدة.

يتم تعقب التغييرات على تجمع SQL باستخدام سجلات العمليات. كل توزيع له سجل العمليات الخاص به. تعد عمليات الكتابة في سجل المعاملات تلقائية. غير مطلوب التكوين. ومع ذلك، في حين أن هذه العملية تضمن الكتابة، فإنها تقدم مقدارًا زائدًا في النظام. يمكنك تقليل هذا التأثير عن طريق كتابة رمز فعال للعمليات. تقع التعليمة البرمجية الفعالة للمعاملات على نطاق واسع في فئتين.

  • استخدم بنيات تسجيل الحد الأدنى كلما أمكن ذلك
  • معالجة البيانات باستخدام دفعات محددة النطاق لتجنب المعاملات الفردية طويلة المدى
  • اعتماد نمط تبديل القسم لإجراء تعديلات كبيرة على قسم معين

تسجيل أدنى مقابل تسجيل كامل

تتعقب العمليات المُسجلة بحد أدنى تخصيصات المدى وتغييرات بيانات التعريف فقط، على عكس العمليات المُسجلة بالكامل، التي تستخدم سجل العمليات لتعقب جميع تغييرات الصف. وبالتالي، يتضمن الحد الأدنى من عمليات التسجيل تسجيل المعلومات المطلوبة فقط للتراجع عن المعاملة بعد الفشل، أو لطلب صريح (ROLLBACK TRAN). وبينما يتم تعقب قدر أقل بكثير من المعلومات في سجل العمليات، يكون أداء عملية مُسجلة بحد أدنى أفضل من أداء عملية مُسجلة بالكامل ذات حجم مماثل. علاوة على ذلك، نظرًا لوجود عدد أقل من عمليات الكتابة في سجل المعاملات، ينشئ قدر أقل بكثير من بيانات السجل وبالتالي يكون الإدخال/الإخراج أكثر كفاءة.

تنطبق حدود أمان المعاملة فقط على العمليات التي سجلت بالكامل.

ملاحظة

يمكن للعمليات التي سجلت بالحد الأدنى المشاركة في المعاملات الصريحة. نظرًا لتتبع كافة التغييرات في بنى التخصيص، فمن الممكن التراجع عن العمليات التي سجلت بأدنى حد.

الحد الأدنى من العمليات الخاصة بتسجيل الدخول

تكون العمليات التالية قادرة على التسجيل بحد أدنى:

  • أنشئ جدول كما هو محدد (CTAS)
  • إدراج..تحديد
  • إنشاء فهرس
  • تبديل إعادة إنشاء الفهرس
  • إسقاط فهرس
  • اقتطاع جدول
  • إسقاط جدول
  • تبديل قسم تبديل الجدول

ملاحظة

لا تتأثر عمليات النقل الخاصة بالبيانات الداخلية (مثل البث والخلط) بحد أمان المعاملة.

الحد الأدنى من التسجيل مع تحميل مجمع

يعتبر كل من إنشاء جدول كما محدد وإدراج...تحديد من عمليات التحميل المجمع. ومع ذلك، يتأثر كل منهما بتعريف الجدول الهدف ويعتمد على سيناريو التحميل. يوضح الجدول التالي عندما يتم تسجيل العمليات المجمعة بشكل كامل أو بحد أدنى:

الفهرس الأساسي سيناريو التحميل وضع التسجيل
كومة ذاكرة مؤقتة أي الحد الأدنى
فهرس متفاوت المسافات جدول الهدف الفارغ الحد الأدنى
فهرس متفاوت المسافات عدم تداخل الصفوف التي تم تحميلها مع الصفحات الموجودة في الهدف الحد الأدنى
فهرس متفاوت المسافات تداخل الصفوف التي تم تحميلها مع الصفحات الموجودة في الهدف كامل
فهرس تخزين الأعمدة متفاوت المسافات حجم الدُفعة >= 102,400 لكل توزيع يحاذي قسماً الحد الأدنى
فهرس تخزين الأعمدة متفاوت المسافات حجم الدُفعة < 102,400 لكل توزيع يحاذي قسماً كامل

تجدر الإشارة إلى أن أي عمليات كتابة لتحديث الفهارس الثانوية أو غير متفاوتة المسافات ستكون دائماً عمليات مُسجلة بالكامل.

هام

يحتوي تجمع SQL المخصص على 60 توزيعًا. ومن ثم، بافتراض توزيع جميع الصفوف بالتساوي والانتقال إلى قسم واحد، فإن الدفعة الخاصة بك سوف يتعين أن تحتوي على 6,144,000 صف أو أكثر لتسجيل الحد الأدنى منها عند الكتابة إلى فهرس تخزين أعمدة متفاوت المسافات. وفي حالة تقسيم الجدول وإدراج الصفوف لتشمل حدود الأقسام، حينها ستحتاج 6،144،000 صف لكل حد قسم على افتراض توزيع البيانات بالتساوي. يجب أن يتجاوز كل قسم في كل توزيع حد الصفوف البالغ 102,400 صف بشكل مستقل للإدراج ليتم تسجيل الحد الأدنى في التوزيع.

يُمكن في كثير من الأحيان أن يحتوي تحميل البيانات في جدول غير فارغ مع فهرس متفاوت المسافات على مزيج من الصفوف المسجلة بالكامل والمسجلة بحد أدنى. يعتبر الفهرس متفاوت المسافات شجرة متوازنة (b-tree) من الصفحات. إذا كانت الصفحة التي تتم الكتابة عليها تحتوي بالفعل على صفوف من معاملة أخرى، فمن ثم ستكون عمليات الكتابة مسجلة بالكامل. ومع ذلك، إذا كانت الصفحة فارغة، فمن ثم ستكون الكتابة في تلك الصفحة مسجلة بحد أدنى.

ترقية عمليات الحذف

الحذف هي عملية مُسجلة بالكامل. في حالة كنت بحاجة إلى حذف كمية كبيرة من البيانات في جدول أو قسم، فغالبًا ما يكون ذلك منطقيًا أكثر SELECTللبيانات التي ترغب في الاحتفاظ بها، والتي يمكن تشغيلها كعملية مسجلة بأدنى حد. لتحديد البيانات، قم بإنشاء جدول جديد باستخدام إنشاء جدول كما محدد. بمجرد الإنشاء، استخدم RENAME لاستبدال الجدول القديم بالجدول الذي تم إنشاؤه حديثًا.

-- Delete all sales transactions for Promotions except PromotionKey 2.

--Step 01. Create a new table select only the records we want to kep (PromotionKey 2)
CREATE TABLE [dbo].[FactInternetSales_d]
WITH
(    CLUSTERED COLUMNSTORE INDEX
,    DISTRIBUTION = HASH([ProductKey])
,     PARTITION     (    [OrderDateKey] RANGE RIGHT
                                    FOR VALUES    (    20000101, 20010101, 20020101, 20030101, 20040101, 20050101
                                                ,    20060101, 20070101, 20080101, 20090101, 20100101, 20110101
                                                ,    20120101, 20130101, 20140101, 20150101, 20160101, 20170101
                                                ,    20180101, 20190101, 20200101, 20210101, 20220101, 20230101
                                                ,    20240101, 20250101, 20260101, 20270101, 20280101, 20290101
                                                )
)
AS
SELECT     *
FROM     [dbo].[FactInternetSales]
WHERE    [PromotionKey] = 2
OPTION (LABEL = 'CTAS : Delete')
;

--Step 02. Rename the Tables to replace the
RENAME OBJECT [dbo].[FactInternetSales]   TO [FactInternetSales_old];
RENAME OBJECT [dbo].[FactInternetSales_d] TO [FactInternetSales];

تحسين عملية التحديثات

التحديث هي عملية مُسجلة بالكامل. في حالة كنت بحاجة إلى تحديث عدد كبير من الصفوف في جدول أو قسم، فغالبًا ما يكون من الأفضل استخدام عملية تسجيل الحد الأدنى مثل CTAS للقيام بذلك أكثر كفاءة بكثير.

في المثال الموضح أدناه، تم تحويل تحديث جدول كامل إلى CTAS حتى يكون الحد الأدنى من التسجيل ممكنًا.

في هذه الحالة، نضيف مبلغ خصم بأثر رجعي إلى المبيعات في الجدول:

--Step 01. Create a new table containing the "Update".
CREATE TABLE [dbo].[FactInternetSales_u]
WITH
(    CLUSTERED INDEX
,    DISTRIBUTION = HASH([ProductKey])
,     PARTITION     (    [OrderDateKey] RANGE RIGHT
                                    FOR VALUES    (    20000101, 20010101, 20020101, 20030101, 20040101, 20050101
                                                ,    20060101, 20070101, 20080101, 20090101, 20100101, 20110101
                                                ,    20120101, 20130101, 20140101, 20150101, 20160101, 20170101
                                                ,    20180101, 20190101, 20200101, 20210101, 20220101, 20230101
                                                ,    20240101, 20250101, 20260101, 20270101, 20280101, 20290101
                                                )
                )
)
AS
SELECT
    [ProductKey]  
,    [OrderDateKey]
,    [DueDateKey]  
,    [ShipDateKey]
,    [CustomerKey]
,    [PromotionKey]
,    [CurrencyKey]
,    [SalesTerritoryKey]
,    [SalesOrderNumber]
,    [SalesOrderLineNumber]
,    [RevisionNumber]
,    [OrderQuantity]
,    [UnitPrice]
,    [ExtendedAmount]
,    [UnitPriceDiscountPct]
,    ISNULL(CAST(5 as float),0) AS [DiscountAmount]
,    [ProductStandardCost]
,    [TotalProductCost]
,    ISNULL(CAST(CASE WHEN [SalesAmount] <=5 THEN 0
         ELSE [SalesAmount] - 5
         END AS MONEY),0) AS [SalesAmount]
,    [TaxAmt]
,    [Freight]
,    [CarrierTrackingNumber]
,    [CustomerPONumber]
FROM    [dbo].[FactInternetSales]
OPTION (LABEL = 'CTAS : Update')
;

--Step 02. Rename the tables
RENAME OBJECT [dbo].[FactInternetSales]   TO [FactInternetSales_old];
RENAME OBJECT [dbo].[FactInternetSales_u] TO [FactInternetSales];

--Step 03. Drop the old table
DROP TABLE [dbo].[FactInternetSales_old]

ملاحظة

من الممكن أن تستفيد إعادة إنشاء الجداول الكبيرة من استخدام ميزات إدارة حمل العمل الخاص بتجمع SQL المخصصة. لمزيد من المعلومات، قم بمراجعة إدارة حمل العمل باستخدام فئات الموارد.

التحسين من خلال تبديل القسم

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

موضح أدناه خطوات إجراء تبديل القسم:

  1. أنشئ قسم فارغ
  2. يرجي إجراء «التحديث» باعتباره CTAS
  3. بدل البيانات الموجودة إلى الجدول الخارجي
  4. بدل في البيانات الجديدة
  5. نظف البيانات

ومع ذلك، للمساعدة في تحديد الأقسام المراد تبديلها، أنشئ الإجراء المساعد التالي.

CREATE PROCEDURE dbo.partition_data_get
    @schema_name           NVARCHAR(128)
,    @table_name               NVARCHAR(128)
,    @boundary_value           INT
AS
IF OBJECT_ID('tempdb..#ptn_data') IS NOT NULL
BEGIN
    DROP TABLE #ptn_data
END
CREATE TABLE #ptn_data
WITH    (    DISTRIBUTION = ROUND_ROBIN
        ,    HEAP
        )
AS
WITH CTE
AS
(
SELECT     s.name                            AS [schema_name]
,        t.name                            AS [table_name]
,         p.partition_number                AS [ptn_nmbr]
,        p.[rows]                        AS [ptn_rows]
,        CAST(r.[value] AS INT)            AS [boundary_value]
FROM        sys.schemas                    AS s
JOIN        sys.tables                    AS t    ON  s.[schema_id]        = t.[schema_id]
JOIN        sys.indexes                    AS i    ON     t.[object_id]        = i.[object_id]
JOIN        sys.partitions                AS p    ON     i.[object_id]        = p.[object_id]
                                                AND i.[index_id]        = p.[index_id]
JOIN        sys.partition_schemes        AS h    ON     i.[data_space_id]    = h.[data_space_id]
JOIN        sys.partition_functions        AS f    ON     h.[function_id]        = f.[function_id]
LEFT JOIN    sys.partition_range_values    AS r     ON     f.[function_id]        = r.[function_id]
                                                AND r.[boundary_id]        = p.[partition_number]
WHERE i.[index_id] <= 1
)
SELECT    *
FROM    CTE
WHERE    [schema_name]        = @schema_name
AND        [table_name]        = @table_name
AND        [boundary_value]    = @boundary_value
OPTION (LABEL = 'dbo.partition_data_get : CTAS : #ptn_data')
;
GO

يعمل هذا الإجراء على زيادة إعادة استخدام التعليمة البرمجية إلى الحد الأقصى والحفاظ على مثال تبديل القسم أكثر إحكامًا.

توضح التعليمة البرمجية التالية الخطوات المذكورة مسبقًا لتحقيق روتين تبديل قسم كامل.

--Create a partitioned aligned empty table to switch out the data
IF OBJECT_ID('[dbo].[FactInternetSales_out]') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[FactInternetSales_out]
END

CREATE TABLE [dbo].[FactInternetSales_out]
WITH
(    DISTRIBUTION = HASH([ProductKey])
,    CLUSTERED COLUMNSTORE INDEX
,     PARTITION     (    [OrderDateKey] RANGE RIGHT
                                    FOR VALUES    (    20020101, 20030101
                                                )
                )
)
AS
SELECT *
FROM    [dbo].[FactInternetSales]
WHERE 1=2
OPTION (LABEL = 'CTAS : Partition Switch IN : UPDATE')
;

--Create a partitioned aligned table and update the data in the select portion of the CTAS
IF OBJECT_ID('[dbo].[FactInternetSales_in]') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[FactInternetSales_in]
END

CREATE TABLE [dbo].[FactInternetSales_in]
WITH
(    DISTRIBUTION = HASH([ProductKey])
,    CLUSTERED COLUMNSTORE INDEX
,     PARTITION     (    [OrderDateKey] RANGE RIGHT
                                    FOR VALUES    (    20020101, 20030101
                                                )
                )
)
AS
SELECT
    [ProductKey]  
,    [OrderDateKey]
,    [DueDateKey]  
,    [ShipDateKey]
,    [CustomerKey]
,    [PromotionKey]
,    [CurrencyKey]
,    [SalesTerritoryKey]
,    [SalesOrderNumber]
,    [SalesOrderLineNumber]
,    [RevisionNumber]
,    [OrderQuantity]
,    [UnitPrice]
,    [ExtendedAmount]
,    [UnitPriceDiscountPct]
,    ISNULL(CAST(5 as float),0) AS [DiscountAmount]
,    [ProductStandardCost]
,    [TotalProductCost]
,    ISNULL(CAST(CASE WHEN [SalesAmount] <=5 THEN 0
         ELSE [SalesAmount] - 5
         END AS MONEY),0) AS [SalesAmount]
,    [TaxAmt]
,    [Freight]
,    [CarrierTrackingNumber]
,    [CustomerPONumber]
FROM    [dbo].[FactInternetSales]
WHERE    OrderDateKey BETWEEN 20020101 AND 20021231
OPTION (LABEL = 'CTAS : Partition Switch IN : UPDATE')
;

--Use the helper procedure to identify the partitions
--The source table
EXEC dbo.partition_data_get 'dbo','FactInternetSales',20030101
DECLARE @ptn_nmbr_src INT = (SELECT ptn_nmbr FROM #ptn_data)
SELECT @ptn_nmbr_src

--The "in" table
EXEC dbo.partition_data_get 'dbo','FactInternetSales_in',20030101
DECLARE @ptn_nmbr_in INT = (SELECT ptn_nmbr FROM #ptn_data)
SELECT @ptn_nmbr_in

--The "out" table
EXEC dbo.partition_data_get 'dbo','FactInternetSales_out',20030101
DECLARE @ptn_nmbr_out INT = (SELECT ptn_nmbr FROM #ptn_data)
SELECT @ptn_nmbr_out

--Switch the partitions over
DECLARE @SQL NVARCHAR(4000) = '
ALTER TABLE [dbo].[FactInternetSales]    SWITCH PARTITION '+CAST(@ptn_nmbr_src AS VARCHAR(20))    +' TO [dbo].[FactInternetSales_out] PARTITION '    +CAST(@ptn_nmbr_out AS VARCHAR(20))+';
ALTER TABLE [dbo].[FactInternetSales_in] SWITCH PARTITION '+CAST(@ptn_nmbr_in AS VARCHAR(20))    +' TO [dbo].[FactInternetSales] PARTITION '        +CAST(@ptn_nmbr_src AS VARCHAR(20))+';'
EXEC sp_executesql @SQL

--Perform the clean-up
TRUNCATE TABLE dbo.FactInternetSales_out;
TRUNCATE TABLE dbo.FactInternetSales_in;

DROP TABLE dbo.FactInternetSales_out
DROP TABLE dbo.FactInternetSales_in
DROP TABLE #ptn_data

يرجي التقليل من التسجيل بدفعات صغيرة

بالنسبة لعمليات تعديل البيانات الكبيرة، يمكن أن يكون من المنطقي تقسيم العملية إلى أجزاء أو دفعات لتوسيع نطاق وحدة العمل.

تعد التعليمة البرمجية التالية هي مثال عملي. عين حجم الدُفعة على رقم مبسط لإبراز التقنية. في الواقع، سيكون حجم الدُفعة أكبر بكثير.

SET NO_COUNT ON;
IF OBJECT_ID('tempdb..#t') IS NOT NULL
BEGIN
    DROP TABLE #t;
    PRINT '#t dropped';
END

CREATE TABLE #t
WITH    (    DISTRIBUTION = ROUND_ROBIN
        ,    HEAP
        )
AS
SELECT    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS seq_nmbr
,        SalesOrderNumber
,        SalesOrderLineNumber
FROM    dbo.FactInternetSales
WHERE    [OrderDateKey] BETWEEN 20010101 and 20011231
;

DECLARE    @seq_start        INT = 1
,        @batch_iterator    INT = 1
,        @batch_size        INT = 50
,        @max_seq_nmbr    INT = (SELECT MAX(seq_nmbr) FROM dbo.#t)
;

DECLARE    @batch_count    INT = (SELECT CEILING((@max_seq_nmbr*1.0)/@batch_size))
,        @seq_end        INT = @batch_size
;

SELECT COUNT(*)
FROM    dbo.FactInternetSales f

PRINT 'MAX_seq_nmbr '+CAST(@max_seq_nmbr AS VARCHAR(20))
PRINT 'MAX_Batch_count '+CAST(@batch_count AS VARCHAR(20))

WHILE    @batch_iterator <= @batch_count
BEGIN
    DELETE
    FROM    dbo.FactInternetSales
    WHERE EXISTS
    (
            SELECT    1
            FROM    #t t
            WHERE    seq_nmbr BETWEEN  @seq_start AND @seq_end
            AND        FactInternetSales.SalesOrderNumber        = t.SalesOrderNumber
            AND        FactInternetSales.SalesOrderLineNumber    = t.SalesOrderLineNumber
    )
    ;

    SET @seq_start = @seq_end
    SET @seq_end = (@seq_start+@batch_size);
    SET @batch_iterator +=1;
END

الإيقاف المؤقت والتحجيم

يتيح لك تجمع SQL المخصص إيقاف تجمع SQL المخصص واستئنافه وتوسيع نطاقه عند الطلب. عندما تقوم بإيقاف مجموعة SQL المخصصة الخاصة بك مؤقتًا أو توسيع نطاقها، فمن المهم أن تفهم أنه يتم إنهاء أي العمليات أثناء الطيران على الفور؛ التسبب في التراجع عن أي عمليات مفتوحة. إذا كان عبء العمل الخاص بك أصدر تعديلًا على البيانات طويل الأمد وغير مكتمل قبل الإيقاف المؤقت أو عملية القياس، فحينئذٍ يلزم التراجع عن هذا العمل. يمكن أن يؤثر هذا التراجع على الوقت المستغرق لإيقاف مجموعة SQL المخصصة أو توسيع نطاقها.

هام

كل من UPDATE وDELETEعمليتان مسجلتان بالكامل، وبالتالي فإن عمليات التراجع/الإعادة هذه يمكن أن تستغرق وقتًا أطول بكثير من العمليات التي تم تسجيلها بأدنى حد.

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

  • أعد كتابة العمليات طويلة المدى عن طريق استخدام CTAS
  • قسّم العملية إلى أجزاء تعمل في مجموعة فرعية من الصفوف

الخطوات التالية

قم بمراجعة عمليات في تجمع SQL المخصص لمعرفة المزيد حول مستويات العزل وحدود العمليات. للحصول على نظرة عامة حول أفضل الممارسات الأخرى، قم بمراجعة أفضل ممارسات تجمع SQL المخصص.

  • Last updated on