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

تلميح

Microsoft Fabric Data Warehouse هو مستودع علائقي على نطاق مؤسسي قائم على أساس بحيرة البيانات، مع بنية جاهزة للمستقبل، وذكاء اصطناعي مدمج، وميزات جديدة. إذا كنت جديدا في مستودع البيانات، ابدأ ب Fabric Data Warehouse. يمكن لأحمال عمل تجمع SQL المخصصة الحالية الترقية إلى Fabric للوصول إلى قدرات جديدة في علوم البيانات، والتحليلات اللحظية، والتقارير.

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

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

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

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

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

تسجيل السجلات البسيطة مقابل الكاملة

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

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

ملحوظة

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

العمليات المسجلة بشكل أقل قدر ممكن

يمكن تسجيل العمليات التالية بشكل أدنى:

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

ملحوظة

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

تقليل قطع الأشجار مع الحمل السائب

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

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

ومن الجدير بالذكر أن أي كتابات لتحديث الفهارس الثانوية أو غير المجمعة ستكون دائما عمليات مسجلة بالكامل.

مهم

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

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

تحسين الحذف

DELETE هي عملية مسجلة بالكامل. إذا كنت بحاجة لحذف كمية كبيرة من البيانات في جدول أو قسم، غالبا ما يكون ذلك أكثر منطقية للبيانات SELECT التي ترغب في الاحتفاظ بها، والتي يمكن تشغيلها كعملية مسجلة بشكل محدود. لاختيار البيانات، أنشئ جدولا جديدا باستخدام CTAS. بعد الإنشاء، استخدم 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];

تحسين التحديثات

UPDATE هو عملية مسجلة بالكامل. إذا كنت بحاجة إلى تحديث عدد كبير من الصفوف في جدول أو قسم، غالبا ما يكون من الأكثر كفاءة استخدام عملية مسجلة بأقل قدر ممكن مثل 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. لمزيد من المعلومات، راجع فئات الموارد لإدارة عبء العمل.

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

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

الخطوات اللازمة لتنفيذ تبديل التقسيم هي كما يلي:

إنشاء قسم خروج فارغ
قم بإجراء 'التحديث' ك CTAS
قم بتحويل البيانات الموجودة إلى جدول الخروج
تبديل البيانات الجديدة
نظف البيانات

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

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 2026-03-26

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

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

تسجيل السجلات البسيطة مقابل الكاملة

العمليات المسجلة بشكل أقل قدر ممكن

تقليل قطع الأشجار مع الحمل السائب

تحسين الحذف

تحسين التحديثات

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

تقليل قطع الأشجار بدفعات صغيرة

إرشادات التوقف والتوسع

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

الملاحظات

الموارد الإضافية