آیا Select Into به صورت Batch اجرا می شود؟ چرا؟

آیا Select Into به صورت Batch اجرا می شود؟ چرا؟

نوشته شده توسط: حمید فرد
تاریخ انتشار: ۲۳ شهریور ۱۳۹۴
آخرین بروزرسانی: ۱۰ اردیبهشت ۱۴۰۱
زمان مطالعه: 8 دقیقه
۰
(۰)

مقدمه

بیشتر مدیران و توسعه دهنده گان پایگاه داده SQL Server در این تفکر هستند که چون دستور Select Into به صورت Bulk Operation اجرا می شود پس این دستور به صورت Batch اجرا می شود. خیر! این تفکر کاملا اشتباه است. دستور Select Into به دلیل آنکه SQL Server کمترین تراکنش را در Transaction Log فایل برای دستورات Bulk ذخیره می کند به همین دلیل این دستور از سرعت بالایی در مقایسه با دستور    معمولی Insert بر خوردار است.
توجه داشته باشید که SQL Server فقط تراکنشهایی که در رابطه با Data Page ها باشد ذخیره می کند و در زمانی که از Transaction Log فایل پشتیبان گرفته شود تمامی Data Page هایی که در عملیات Bulk تغییر داده شده باشند در فایل پشتیبان ذخیره می شود (خارج از این بحث).

دستور Select Into تمامی رکوردهای جدول منبع را به صورت یک به یک خوانده و به صورت یک به یک در جدول جدید وارد می کند. پس در نتیجه این عملیات به صورت Batch اجرا نمی شوند. تصویر زیر داده های ذخیره شده در فایل تراکنش بعد از دستور معمولی Insert را نشان می دهد. همانطور که مشاهده می کنید قبل و بعد از عملیات LOP_INSERT_ROW ، عملیاتی همچون LOP_BEGIN_XACT و LOP_COMMIT_XACT وجود دارد. این نشان می دهد که هر یک از عملیات Insert یک به یک در فایل تراکنش ذخیره می شود.
حال زمان آن رسیده که به داده های فایل تراکنش بعد از اجرا دستور Select Into نگاهی بیاندازیم. همانطور که مشاهده می کنید، عملیاتی همیچون LOP_BEGIN_XACT، LOP_INSERT_ROW، LOP_COMMIT_XACT وجود ندارد. در حقیقت این عملیات در پشتضمینه اجرا می شود ولی به دلیل رفتار SQL Server در مقابل عملیات Bulk این تراکنشهای ذخیره نمی شوند. ولی همانطور که مشاهده می کنید تمامی عملیات درباره Data Page ها تماما در فایل تراکنش ذخیره شده است.

پیشنهاد می شود که دستور Select Into در زمان Off-Peak سیستم اجرا شود به دلیل داشتن ستیز (Contention) بر بروی IAM, PFS, GAM و SGAM.

 شما می توانید نوع اجرا دستور را که به صورت ROW است از Execution Plan مشاهده کنید.

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

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

اولین نفر باش

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

    • با سلام و خسته نباشید
      این مقاله عالی بود
      از آقای مهندس مسعود طاهری هم از بابت لینهایی که ارسال کرده اند هم ممنونم

    • سلام 

      سناریو شما را کامل نمی دانم اما اگر کلی بخواهم جواب بدهم 
      بلی – روش های زیر می تواند مفید باشد

      استفاده از کلاس SQLBulkCopy در دات نت و درج دیتا در جدول به شکل Bulk Insert 
      یک روش دیگر هم استفاده از TVP است
      هر دو این موضوع در دوره SQL Server ویژه برنامه نویسان بررسی شده است
    • سلام 

      سناریو شما را کامل نمی دانم اما اگر کلی بخواهم جواب بدهم 
      بلی – روش های زیر می تواند مفید باشد

      استفاده از کلاس SQLBulkCopy در دات نت و درج دیتا در جدول به شکل Bulk Insert 
      یک روش دیگر هم استفاده از TVP است
      هر دو این موضوع در دوره SQL Server ویژه برنامه نویسان بررسی شده است
    •     آیا برای Insert کردن رکوردهای تعداد بالا از SELECT INTO دستور پرسرعت تری هم وجود داره؟؟؟؟

    •    با سلام
      بسیا عالی و جالب بود
      ممنون

    •     تورج: البته اون عکسی که گذاشتم در رابطه با Lock درمورد Lockها روی جدول منبع است نه جدول جدیدی که ساخته می شه.

    •    ممنون،

      اینکه چرا SQL Server روی رکوردی که در حال درج شدن هست Lock میزنه رو می تونیم به شکل زیر تفسیر کنیم؟
      “چون ممکنه تراکنش دیگه ای اون رو رکورد رو ناقص بخونه (مثلاً به جای خوندن کل ردیف چند تا ستون رو بیشتر نمی تونه بخونه) یا اینکه در حالی که هنوز رکورد به طور کامل با تمام اطلاعات ثبت نشده تراکنش دیگه ای بیاد و اون رو تغییر بده!”
      •     تورج: 

        یه جورایی درست می گی ولی اینجوری نیست که به جای کل ردیف چند تا ستون رو بخونه (روند ثبت رکورد فرق داره و در اینجا بحثش نمی گنجه). قفل کردن رکوردها برای Concurrency هستش. وقتی رکوردی قفل می شه (قفل ها در SQL Server انواع متفاوتی دارند که اینجا جاش نیست) همانطور که گفتی تراکنش دیگه ای نمی تونه اون رو تغییر بده ولی در شرایطی می تونه اون رو بخونه. به عنوان مثال یک رکورد آپدیت شده ولی هنوز تراکنش Commit نشده در این صورت شما می تونید داده تغییر داده شده رو بخونید ولی نمی تونید تغییر بدید و اگر بخواید تغییر بدید تراکنش جدید توسط تراکنش قبلی Block میشه. 
    •    یک سوال دیگه اینکه آیا تنظیم Isolation Level برای این دستور هم ممکنه؟
      یعنی اینکه آیا موقع خوندن از جدول مبدا هر رکورد رو لاک میکنه؟ و ما میتونیم اون رو با WITHNOLOCK بخونیم؟

      •     بازهم شما؟ (شوخی کردم)

        Isolation Level روی Select Into تاثیر می ذاره به دلیل اینکه این دستور باید یک جدول یا یک سری جداول رو بخونه.
        و اینکه بابت Lock این دستور Page ها و Extent ها را Lock می کند نه رکوردها را. البته شما می تونید با دستور With NoLock داده های قفل شده توسط Select Into رو بخوانید یا داده های قفل شده در جداول دیگر را.
        به این عکس یه نگاهی بنداز. این رو برای شما آماده کردم. همانطور که میبینید. تمامی Page ها و بعضی از Extent ها قفل شده اند.

    •    Great Article!
      دو سوال:

      ۱) آیا SELECT INTO یک تراکنش به حساب میاد یا هر INSERT یک تراکنش جداست؟
      ۲) اگر در حین اجرا SELECT INTO، حالت Crash رخ بده تراکنش Uncommited محسوب میشه،
       در این صورت ، SQL Server چطور تشخیص میده که کدوم رکورد ها مربوط به SELECT INTO است؟ (چون در حین ثبت لاگ برای SELECT INTO، برای Insert های تراکنش های دیگر که Comiit شدن هم لاگ ثبت میشه (با لاگ SELECT INTO  تداخل پیدا می کنه))

      •     تورج: عذر می خوام بابت اینکه دیر شد جواب بدم. امروز به کلی سرم شلوغ بود.

        ۱- هر Insert در Select Into یک تراکنش جدا هستش و خود آن تراکنش به صورت Implicit است یعنی به خودیه خود Commit می شود.
        ۲- تراکنشها تا قبل از Crash به عنوان Commited حساب می شوند ولی تراکنشهایی که همزمان با Crash مواجه شده اند به دلیل آنکه Commit شده اند در زمان Undo به unCommitted تبدیل شده یا به عبارت دیگری Rollback می شوند.
        ۳- Select Into خود یک تراکنش پدر است که تمامی تراکنش های Insert به صورت Implicit داخل آن است. ولی برای Rollback کرد آن باید یک تراکنش به صورت Explicit ساخته شود.
    •     ممنون. 

  • 1
  • 2
هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link