بنچمارک سرعت Insert & Update در SQL Server

بنچمارک کارایی: مقایسه سرعت Insert & Update داده در SQL Server

این مقاله به بررسی عملکرد Insert & Update رکوردهای پایگاه داده در SQL Server می‌پردازد. ما روش‌های مختلفی را آزمایش می‌کنیم تا تفاوت‌های کارایی را مشخص کرده و بینش‌هایی برای بهینه‌سازی عملیات داده به دست آوریم. در محیط‌های پرکار، حتی تفاوت‌های کوچک در زمان اجرای دستورات می‌توانند تأثیر قابل توجهی بر عملکرد کلی سیستم داشته باشند. با مقایسه سناریوهای مختلف درج و به‌روزرسانی، می‌توانیم بهترین روش‌ها را برای برنامه‌های خود شناسایی کنیم.

برای شروع، یک پایگاه داده آزمایشی و یک جدول ساده ایجاد می‌کنیم. این جدول برای نگهداری داده‌های مورد نیاز برای تست درج و به‌روزرسانی طراحی شده است.

USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'InsertUpdateTest')
BEGIN
    ALTER DATABASE InsertUpdateTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE InsertUpdateTest;
END;
GO

CREATE DATABASE InsertUpdateTest;
GO

USE InsertUpdateTest;
GO

CREATE TABLE dbo.TestTable
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    SomeValue VARCHAR(200) NOT NULL,
    SomeOtherValue VARCHAR(200) NOT NULL,
    CreatedDate DATETIME NOT NULL DEFAULT GETDATE()
);
GO

اکنون که جدول پایه را ایجاد کرده‌ایم، داده‌های اولیه را برای آزمایش به‌روزرسانی‌ها به آن اضافه می‌کنیم. این مرحله به ما امکان می‌دهد تا سناریوهای به‌روزرسانی را در برابر یک مجموعه داده موجود ارزیابی کنیم. ما از یک حلقه برای درج 100,000 رکورد اولیه استفاده می‌کنیم.

SET NOCOUNT ON;
GO

DECLARE @i INT = 0;
WHILE @i < 100000
BEGIN
    INSERT INTO dbo.TestTable (SomeValue, SomeOtherValue)
    VALUES ('Initial Value ' + CAST(@i AS VARCHAR(10)), 'Another Initial Value ' + CAST(@i AS VARCHAR(10)));
    SET @i = @i + 1;
END;
GO

SELECT COUNT(*) AS InitialRecordCount FROM dbo.TestTable;
GO

با آماده‌سازی داده‌های اولیه، می‌توانیم آزمایش‌های خود را برای مقایسه عملکرد درج و به‌روزرسانی آغاز کنیم. این بخش سناریوهای مختلفی را برای درج داده‌ها بررسی می‌کند، از درج تک‌رکورد تا درج دسته‌ای. درک اینکه کدام روش درج برای حجم‌های کاری مختلف کارآمدتر است، برای بهینه‌سازی عملکرد پایگاه داده ضروری است.

تست عملکرد درج (INSERT)

در این بخش، سه روش متداول برای درج داده‌ها را آزمایش می‌کنیم: درج تک‌ردیفی، درج چندردیفی در یک دستور، و استفاده از SQL Bulk Copy. ما عملکرد هر کدام را با درج 100,000 رکورد جدید ارزیابی می‌کنیم.

روش 1: درج تک‌ردیفی در یک حلقه

این روش شامل درج رکوردها به صورت یک به یک در یک حلقه است. این رویکرد اغلب برای حجم‌های کوچک داده استفاده می‌شود، اما می‌تواند برای حجم‌های بزرگتر ناکارآمد باشد.

-- Clear existing data for a clean test of inserts
TRUNCATE TABLE dbo.TestTable;
GO

DECLARE @StartTime DATETIME = GETDATE();
DECLARE @i INT = 0;
WHILE @i < 100000
BEGIN
    INSERT INTO dbo.TestTable (SomeValue, SomeOtherValue)
    VALUES ('Single Insert Value ' + CAST(@i AS VARCHAR(10)), 'Single Other Value ' + CAST(@i AS VARCHAR(10)));
    SET @i = @i + 1;
END;
DECLARE @EndTime DATETIME = GETDATE();
SELECT DATEDIFF(ms, @StartTime, @EndTime) AS SingleInsertDurationMs;
SELECT COUNT(*) AS RecordsAfterSingleInsert FROM dbo.TestTable;
GO

روش 2: درج چندردیفی در یک دستور

SQL Server اجازه می‌دهد چندین ردیف در یک دستور `INSERT` درج شوند. این روش به طور کلی کارآمدتر از درج تک‌ردیفی است، زیرا سربار تراکنش و شبکه را کاهش می‌دهد.

TRUNCATE TABLE dbo.TestTable;
GO

DECLARE @StartTime DATETIME = GETDATE();
DECLARE @i INT = 0;
DECLARE @BatchSize INT = 1000; -- Inserting 1000 rows at a time
WHILE @i < 100000
BEGIN
    INSERT INTO dbo.TestTable (SomeValue, SomeOtherValue)
    SELECT TOP (@BatchSize) 'Batch Insert Value ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i AS VARCHAR(10)),
                           'Batch Other Value ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i AS VARCHAR(10))
    FROM sys.objects AS o1
    CROSS JOIN sys.objects AS o2
    ORDER BY NEWID(); -- Generate enough rows for the batch
    
    SET @i = @i + @BatchSize;
END;
DECLARE @EndTime DATETIME = GETDATE();
SELECT DATEDIFF(ms, @StartTime, @EndTime) AS BatchInsertDurationMs;
SELECT COUNT(*) AS RecordsAfterBatchInsert FROM dbo.TestTable;
GO

توجه داشته باشید که کد بالا برای تولید `BatchSize` ردیف به صورت مصنوعی از `sys.objects` استفاده می‌کند. در سناریوی واقعی، داده‌ها از منبع دیگری (مانند یک جدول موقت یا پارامترهای جدولی) تأمین می‌شوند.

روش 3: استفاده از SqlBulkCopy (یا BULK INSERT)

`SqlBulkCopy` در دات‌نت (و `BULK INSERT` در T-SQL) برای درج مقادیر بسیار زیاد داده از یک فایل یا منبع داده دیگر به یک جدول SQL Server، بسیار کارآمد است. این روش اغلب سریع‌ترین گزینه برای درج حجم زیادی از داده است.

TRUNCATE TABLE dbo.TestTable;
GO

-- Simulating BULK INSERT from a CSV file (conceptually)
-- For an actual BULK INSERT, you would need a physical file.
-- Example:
-- BULK INSERT dbo.TestTable
-- FROM 'C:\temp\MyData.csv'
-- WITH (
--     FIELDTERMINATOR = ',',
--     ROWTERMINATOR = '\n'
-- );

-- Since we can't execute file operations directly in a T-SQL script,
-- and for consistency in an isolated test, we'll demonstrate the concept
-- by showing how much faster a well-optimized batch insert can be.
-- The performance of actual BULK INSERT is typically superior to
-- repeated INSERT statements or even multi-row INSERTs for very large datasets.

-- For this demonstration, we'll use a very large multi-row insert (conceptually similar to a single batch load).
DECLARE @StartTime DATETIME = GETDATE();
DECLARE @i INT = 0;
INSERT INTO dbo.TestTable (SomeValue, SomeOtherValue)
SELECT 'Bulk Sim Value ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(10)),
       'Bulk Sim Other Value ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(10))
FROM sys.objects AS o1
CROSS JOIN sys.objects AS o2
CROSS JOIN sys.objects AS o3
WHERE @i < 100000 -- Limit to 100,000 rows
AND (@i := @i + 1) IS NOT NULL; -- Increment @i (SQL Server doesn't support assignment in WHERE directly, this is conceptual for mass data gen)
-- A more practical T-SQL way to generate 100k rows for bulk-like insertion:
;WITH N1(N) AS (SELECT 1 UNION ALL SELECT 1)
,N2(N) AS (SELECT 1 FROM N1 AS T1, N1 AS T2)
,N3(N) AS (SELECT 1 FROM N2 AS T1, N2 AS T2)
,N4(N) AS (SELECT 1 FROM N3 AS T1, N3 AS T2)
,N5(N) AS (SELECT 1 FROM N4 AS T1, N4 AS T2)
,Nums(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N5)
INSERT INTO dbo.TestTable (SomeValue, SomeOtherValue)
SELECT 'Bulk Sim Value ' + CAST(N AS VARCHAR(10)), 'Bulk Sim Other Value ' + CAST(N AS VARCHAR(10))
FROM Nums
WHERE N <= 100000;

DECLARE @EndTime DATETIME = GETDATE();
SELECT DATEDIFF(ms, @StartTime, @EndTime) AS BulkSimInsertDurationMs;
SELECT COUNT(*) AS RecordsAfterBulkSimInsert FROM dbo.TestTable;
GO

نتایج نشان می‌دهند که درج چندردیفی و به‌ویژه روش‌های حجیم (مانند `BULK INSERT` یا `SqlBulkCopy`) به طور قابل توجهی سریع‌تر از درج تک‌ردیفی هستند. این به دلیل کاهش سربار تراکنش، ورودی/خروجی دیسک، و ارتباطات شبکه است.

تست عملکرد به‌روزرسانی (UPDATE)

اکنون به بررسی عملکرد به‌روزرسانی رکوردها می‌پردازیم. ما دوباره از همان 100,000 رکورد اولیه استفاده می‌کنیم و سناریوهای مختلف به‌روزرسانی را مقایسه می‌کنیم.

-- Re-populate data for update tests
TRUNCATE TABLE dbo.TestTable;
GO

DECLARE @i INT = 0;
WHILE @i < 100000
BEGIN
    INSERT INTO dbo.TestTable (SomeValue, SomeOtherValue)
    VALUES ('Initial Value ' + CAST(@i AS VARCHAR(10)), 'Another Initial Value ' + CAST(@i AS VARCHAR(10)));
    SET @i = @i + 1;
END;
GO

روش 1: به‌روزرسانی تک‌ردیفی در یک حلقه

این روش شامل به‌روزرسانی رکوردها به صورت تک به تک در یک حلقه است. این رویکرد نیز، مانند درج تک‌ردیفی، می‌تواند برای حجم‌های بزرگ داده ناکارآمد باشد.

DECLARE @StartTime DATETIME = GETDATE();
DECLARE @i INT = 1; -- Start from 1 because Id is IDENTITY
WHILE @i <= 100000
BEGIN
    UPDATE dbo.TestTable
    SET SomeValue = 'Updated Single Value ' + CAST(@i AS VARCHAR(10))
    WHERE Id = @i;
    SET @i = @i + 1;
END;
DECLARE @EndTime DATETIME = GETDATE();
SELECT DATEDIFF(ms, @StartTime, @EndTime) AS SingleUpdateDurationMs;
SELECT TOP 10 * FROM dbo.TestTable WHERE SomeValue LIKE 'Updated Single Value%';
GO

روش 2: به‌روزرسانی چندردیفی در یک دستور

SQL Server امکان به‌روزرسانی چندین ردیف با یک دستور `UPDATE` را فراهم می‌کند. این روش به طور قابل توجهی کارآمدتر از به‌روزرسانی تک‌ردیفی است.

-- Reset SomeValue for a clean test of batch update
UPDATE dbo.TestTable SET SomeValue = 'Initial Value ' + CAST(Id-1 AS VARCHAR(10));
GO

DECLARE @StartTime DATETIME = GETDATE();
UPDATE dbo.TestTable
SET SomeValue = 'Updated Batch Value ' + CAST(Id AS VARCHAR(10))
WHERE Id <= 100000; -- Update all 100,000 records in one go
DECLARE @EndTime DATETIME = GETDATE();
SELECT DATEDIFF(ms, @StartTime, @EndTime) AS BatchUpdateDurationMs;
SELECT TOP 10 * FROM dbo.TestTable WHERE SomeValue LIKE 'Updated Batch Value%';
GO

نتایج به‌وضوح نشان می‌دهند که به‌روزرسانی چندین رکورد در یک دستور `UPDATE` بسیار سریع‌تر از به‌روزرسانی تک‌ردیفی در یک حلقه است. این تفاوت عملکرد عمدتاً به دلیل سربار تراکنش و نحوه مدیریت لاگ تراکنش توسط SQL Server است.

تأثیر لاگ تراکنش

هر عملیات درج، به‌روزرسانی یا حذف در SQL Server در لاگ تراکنش ثبت می‌شود. هر چه عملیات‌های بیشتری به صورت جداگانه اجرا شوند (مانند درج یا به‌روزرسانی تک‌ردیفی در یک حلقه)، تعداد بیشتری ورودی لاگ باید ایجاد شود که می‌تواند منجر به کاهش عملکرد شود. عملیات‌های دسته‌ای چندین تغییر را در یک تراکنش بزرگ‌تر گروه‌بندی می‌کنند و در نتیجه تعداد کمتری ورودی لاگ (و سربار کمتر) ایجاد می‌کنند. این امر به ویژه برای عملیات درج و به‌روزرسانی با حجم بالا اهمیت دارد.

هنگام طراحی برنامه‌های کاربردی که نیاز به درج یا به‌روزرسانی مقادیر زیادی داده دارند، همیشه باید رویکردهای دسته‌ای را در نظر گرفت. استفاده از `INSERT` با چندین `VALUES`، `UPDATE` روی مجموعه‌ای از رکوردها، یا ابزارهایی مانند `SqlBulkCopy` می‌تواند تفاوت چشمگیری در کارایی ایجاد کند. انتخاب روش مناسب نه تنها به سرعت اجرا کمک می‌کند، بلکه منابع سیستم (مانند CPU، I/O و فضای لاگ) را نیز به طور مؤثرتری استفاده می‌کند.