حذف اطلاعات از جدول با تعداد رکورد بالا با کمترین Log در SQL Server

حذف اطلاعات از جدول با تعداد رکورد بالا با کمترین Log در SQL Server

نوشته شده توسط: محسن فرهنگیان
۲۸ خرداد ۱۳۹۹
زمان مطالعه: 12 دقیقه
۱
(۱)

مقدمه

به عنوان یک DBA یا Developer گاها پیش می‌آید که لازم باشد از یک جدول خیلی بزرگ تعداد محدودی از رکوردها رو نگه داریم و بخش زیادی از مقادیر آن جدول رو حذف کنیم (به عنوان مثال ۱۰۰۰ رکورد از یک جدول ۱ میلیاردی را نگه داریم و مابقی رکوردها رو حذف کنیم) که اگر برای حذف رکوردها بخواهیم از روش‌های معمول استفاده کنیم زمان خیلی زیادی جهت حذف رکوردها مورد نیاز است و لاگ زیادی تولید می‍شود و در نهایت سیستم دچار مشکل می‌گردد امروز می‌خواهم روشی را به شما آموزش دهم که مناسب حذف تعداد رکورد زیاد از جداول خیلی بزرگ و نگه داشتن بخشی از اطلاعات آن جدول است.
دقت کنید که در این سناریو جدول اصلی هیچ کلید خارجی ندارد . اگر جداول کلید خارجی داشته باشه ، شما باید یک سناریوی چند جدولی داشته باشید تا جامعیت اطلاعات حفظ گردد. این آموزش برای یک سناریوی ساده مثل جدول‌های Log و بدون محدودیت کلید خارجی در نظر گرفته شده است .
خب برای شروع کار یک جدول تستی ایجاد می‌کنیم

مرحله ۱: ایجاد جدول

CREATE TABLE [dbo].[Test1]  (
   Col1_id INT IDENTITY(1,1),
   Col2_D DATETIME DEFAULT GETDATE(),
   Col3_C NCHAR(20) DEFAULT 'My test desc!',
   Col4_T NVARCHAR(MAX) DEFAULT REPLICATE('0123456789', 100)
);
GO

در مرحله بعد تعدادی رکورد تستی در جدول وارد می‌کنیم.
برای این مثال ۵۰۰۰ رکورد و ۲ ایندکس ایجاد می‌کنیم.

مرحله ۲: ایجاد دیتای تستی

--۲. Load Test Data
SET NOCOUNT ON;
INSERT [dbo].[Test1]  (Col2_D, Col3_C, Col4_T)
VALUES (DEFAULT, 'My test desc!', DEFAULT);
GO 5000
--Check it.
SELECT top 10 * FROM [dbo].[Test1];
SELECT COUNT(*) As 'Test1' FROM [dbo].[Test1];
GO
--Create Indexes
CREATE CLUSTERED INDEX [ClusteredIndex_Test1_Col2_D] ON [dbo].[Test1]
([Col2_D] ASC)
GO
CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex_Test1_Col1_ID] ON [dbo].[Test1]
([Col1_id] ASC)
GO

تصویر زیر نتیجه کوری بالا را نمایش می‌دهد:

مرحله ۳: ایجاد یک جدول خالی با ساختار مشابه جدول اصلی

--۳. Now let's duplicate the original schema and change the table and index names
CREATE TABLE [dbo].[Test1_SWITCH] (
   Col1_ID INT IDENTITY(1,1),
   Col2_D DATETIME DEFAULT GETDATE(),
   Col3_C NCHAR(20) DEFAULT 'My test desc!',
   Col4_T NVARCHAR(MAX) DEFAULT REPLICATE('0123456789', 201)
);
CREATE CLUSTERED INDEX [ClusteredIndex_Test1_SWITCH_Col2_D] ON [dbo].[Test1_SWITCH]
([Col2_D] ASC)
GO
CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex_Test1_SWITCH_Col1_ID] ON [dbo].[Test1_SWITCH]
([Col1_id] ASC)
GO
--Check it
SELECT COUNT(*) AS 'Test1' FROM [dbo].[Test1];
SELECT COUNT(*) AS 'Test1_SWITCH' FROM [dbo].[Test1_SWITCH];

تصویر زیر ۵۰۰۰ ردیف در جدول اصلی و ۰ ردیف در جدول جدید را نشان می‌دهدمرحله ۴: سویج جداول

دستور Alter Table SWITCH را اجرا و تعداد رکوردها را بررسی کند.

--۴. Switch the data to a new table
ALTER TABLE [dbo].[Test1] SWITCH to [dbo].[Test1_SWITCH];
SELECT COUNT(*) AS 'Test1' FROM [dbo].[Test1];
SELECT COUNT(*) AS 'Test1_SWITCH' FROM [dbo].[Test1_SWITCH];

هماهنطور که مشاهده می‌کنید اطلاعات با سرعت بالا به جدول جدید انتقال پیدا کردند.

مرحله ۵: انتقال دیتای مورد نیاز به جدول اصلی

حالا می‌توانید مجموعه کوچکی از رکوردها را به جدول اصلی منتقل کنید در این مثال من یک فیلد Identity دارم ، بنابراین برای حفظ مقادیر شمارشی این فیلد باید از IDENTITY_INSERT استفاده کنیم. این مورد ممکنه است در سناریوی دنیای واقعی شما یکسان نباشد.

--۵. Now we can load the small subset of data back to the table.
SET NOCOUNT ON;
SET IDENTITY_INSERT [dbo].[Test1] ON;
INSERT dbo.Test1 (Col1_ID, Col2_D, Col3_C, Col4_T)
SELECT Col1_ID, Col2_D, Col3_C, Col4_T FROM [dbo].[Test1_SWITCH] WHERE Col1_ID <= 1000;
SET IDENTITY_INSERT [dbo].[Test1] OFF;
SELECT COUNT(*) AS 'Test1' FROM [dbo].[Test1];
SELECT COUNT(*) AS 'Test1_SWITCH' FROM [dbo].[Test1_SWITCH];

مرحله ۶: حذف جدول Test1_Switch

--۶. Finally drop the newly created switch table.
DROP TABLE [dbo].[Test1_SWITCH];

 

چه رتبه ای می‌دهید؟

میانگین ۱ / ۵. از مجموع ۱

اولین نفر باش

title sign
معرفی نویسنده
محسن فرهنگیان
مقالات
2 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
محسن فرهنگیان
پروفایل نویسنده
title sign
دیدگاه کاربران

    • بسیار عالی

    • دستور مفیدی بود. من قبلا ندیده بودم. حتی مهندس طاهری هم در کورس‌هاشون به این دستور اشاره نکرده بودن..

      • سلام ، دوره پرفورمنس ۲۰۱۷ جلسه ۱۵ استاد به این نکته اشاره کردن.

    • باسلام و خسته نباشید
      ممنون از مقاله مفید و کاربردیتون.
      با تشکر

      • سلام باتشکر از شما
        همونطور که ابتدای مقاله نوشته شده این آموزش درخصوص جدول های ساده بدون ریلیشن و یا تریگر می باشد که قاعدتا در صورت وجود سناریو حذف رکورد ها تغییر میکنه.

    • باسلام و خسته نباشید
      ممنون از مقاله مفید و کاربردیتون.

      با تشکر

      • سلام باتشکر از شما
        همونطور که ابتدای مقاله نوشته شده این آموزش درخصوص جدول های ساده بدون ریلیشن و یا تریگر می باشد که قاعدتا در صورت وجود سناریو حذف رکورد ها تغییر میکنه.

    • سلام،
      با تشکر از مقاله مفیدتان،
      هنگامی که رکوردها را از جدول Switch به جدول اصلی اینسرت می نماییم باید به این موضوع توجه باشیم که جدول اصلی Trigger After Insert نداشته باشد و در صورتی که تریگری وجود داشته باشد باید آن را Disable و جدول را Lock نماییم و مجدد Enable نماییم. زیرا به ازای این رکوردها عمل تریگر یک بار انجام شده و دیگر نباید رخ دهد.
      درست است که تریگرها اصلا نباید نوشته شوند اما در سناریو این مقاله کاملا با به این موضوع توجه داشت.

    • سلام،
      با تشکر از مقاله مفیدتان،
      هنگامی که رکوردها را از جدول Switch به جدول اصلی اینسرت می نماییم باید به این موضوع توجه باشیم که جدول اصلی Trigger After Insert نداشته باشد و در صورتی که تریگری وجود داشته باشد باید آن را Disable و جدول را Lock نماییم و مجدد Enable نماییم. زیرا به ازای این رکوردها عمل تریگر یک بار انجام شده و دیگر نباید رخ دهد.
      درست است که تریگرها اصلا نباید نوشته شوند اما در سناریو این مقاله کاملا با به این موضوع توجه داشت.