خانه SQL Server آیا Select Into به صورت Batch اجرا می شود؟ چرا؟ SQL Server نوشته شده توسط: حمید فرد ۲۳ شهریور ۱۳۹۴ زمان مطالعه: 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 مشاهده کنید. چه رتبه ای می‌دهید؟ میانگین ۰ / ۵. از مجموع ۰ اولین نفر باش برچسب ها # Database Engine# Select Into# SQL Server# آموزش SQL Server معرفی نویسنده مقالات 6 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده حمید فرد پروفایل نویسنده معرفی محصول احمدرضا صدیقی دوره آموزش برنامه نویسی جاوا استاندارد 1.990.000 تومان مقالات مرتبط ۰۹ اردیبهشت SQL Server حمله SQL Injection چیست؟ صفرتاصد حمله تزریق SQL و راه های جلوگیری از آن تیم فنی نیک آموز ۰۶ اردیبهشت SQL Server پایگاه داده برداری چیست؟ بررسی کاربردها، نحوه کار و آینده Vector Database تیم فنی نیک آموز ۰۲ اردیبهشت SQL Server تاثیر ایندکس های Computed-Column روی دستور DBCC CHECKDB تورج عزیزی ۲۶ فروردین SQL Server دستور SELECT TOP در SQL Server تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ حسن ضرابی ۱۱ / ۰۲ / ۰۱ - ۰۷:۰۵ با سلام و خسته نباشید این مقاله عالی بود از آقای مهندس مسعود طاهری هم از بابت لینهایی که ارسال کرده اند هم ممنونم پاسخ به دیدگاه مسعود طاهری ۲۸ / ۰۹ / ۹۴ - ۰۷:۳۳ سلام سناریو شما را کامل نمی دانم اما اگر کلی بخواهم جواب بدهم بلی – روش های زیر می تواند مفید باشد استفاده از کلاس SQLBulkCopy در دات نت و درج دیتا در جدول به شکل Bulk Insert یک روش دیگر هم استفاده از TVP است https://msdn.microsoft.com/enus/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx http://www.codeproject.com/Articles/39161/C-and-Table-Value-Parameters هر دو این موضوع در دوره SQL Server ویژه برنامه نویسان بررسی شده است پاسخ به دیدگاه مسعود طاهری ۲۸ / ۰۹ / ۹۴ - ۰۷:۳۳ سلام سناریو شما را کامل نمی دانم اما اگر کلی بخواهم جواب بدهم بلی – روش های زیر می تواند مفید باشد استفاده از کلاس SQLBulkCopy در دات نت و درج دیتا در جدول به شکل Bulk Insert یک روش دیگر هم استفاده از TVP است https://msdn.microsoft.com/enus/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx http://www.codeproject.com/Articles/39161/C-and-Table-Value-Parameters هر دو این موضوع در دوره SQL Server ویژه برنامه نویسان بررسی شده است پاسخ به دیدگاه میلاد فیروزی ۲۸ / ۰۹ / ۹۴ - ۰۷:۴۱ آیا برای Insert کردن رکوردهای تعداد بالا از SELECT INTO دستور پرسرعت تری هم وجود داره؟؟؟؟ پاسخ به دیدگاه سیدمهدی معصومی ۳۱ / ۰۶ / ۹۴ - ۱۰:۰۴ با سلامبسیا عالی و جالب بودممنون پاسخ به دیدگاه Hamid J. Fard ۲۵ / ۰۶ / ۹۴ - ۰۱:۲۳ تورج: البته اون عکسی که گذاشتم در رابطه با Lock درمورد Lockها روی جدول منبع است نه جدول جدیدی که ساخته می شه. پاسخ به دیدگاه تورج عزیزی ۲۵ / ۰۶ / ۹۴ - ۱۱:۴۳ ممنون، اینکه چرا SQL Server روی رکوردی که در حال درج شدن هست Lock میزنه رو می تونیم به شکل زیر تفسیر کنیم؟ “چون ممکنه تراکنش دیگه ای اون رو رکورد رو ناقص بخونه (مثلاً به جای خوندن کل ردیف چند تا ستون رو بیشتر نمی تونه بخونه) یا اینکه در حالی که هنوز رکورد به طور کامل با تمام اطلاعات ثبت نشده تراکنش دیگه ای بیاد و اون رو تغییر بده!” پاسخ به دیدگاه Hamid J. Fard ۲۵ / ۰۶ / ۹۴ - ۰۱:۳۵ تورج: یه جورایی درست می گی ولی اینجوری نیست که به جای کل ردیف چند تا ستون رو بخونه (روند ثبت رکورد فرق داره و در اینجا بحثش نمی گنجه). قفل کردن رکوردها برای Concurrency هستش. وقتی رکوردی قفل می شه (قفل ها در SQL Server انواع متفاوتی دارند که اینجا جاش نیست) همانطور که گفتی تراکنش دیگه ای نمی تونه اون رو تغییر بده ولی در شرایطی می تونه اون رو بخونه. به عنوان مثال یک رکورد آپدیت شده ولی هنوز تراکنش Commit نشده در این صورت شما می تونید داده تغییر داده شده رو بخونید ولی نمی تونید تغییر بدید و اگر بخواید تغییر بدید تراکنش جدید توسط تراکنش قبلی Block میشه. پاسخ به دیدگاه تورج عزیزی ۲۳ / ۰۶ / ۹۴ - ۰۵:۳۹ یک سوال دیگه اینکه آیا تنظیم Isolation Level برای این دستور هم ممکنه؟یعنی اینکه آیا موقع خوندن از جدول مبدا هر رکورد رو لاک میکنه؟ و ما میتونیم اون رو با WITHNOLOCK بخونیم؟ پاسخ به دیدگاه Hamid J. Fard ۲۴ / ۰۶ / ۹۴ - ۰۵:۰۵ بازهم شما؟ (شوخی کردم) 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 تداخل پیدا می کنه)) پاسخ به دیدگاه Hamid J. Fard ۲۴ / ۰۶ / ۹۴ - ۰۵:۴۵ تورج: عذر می خوام بابت اینکه دیر شد جواب بدم. امروز به کلی سرم شلوغ بود. ۱- هر Insert در Select Into یک تراکنش جدا هستش و خود آن تراکنش به صورت Implicit است یعنی به خودیه خود Commit می شود. ۲- تراکنشها تا قبل از Crash به عنوان Commited حساب می شوند ولی تراکنشهایی که همزمان با Crash مواجه شده اند به دلیل آنکه Commit شده اند در زمان Undo به unCommitted تبدیل شده یا به عبارت دیگری Rollback می شوند. ۳- Select Into خود یک تراکنش پدر است که تمامی تراکنش های Insert به صورت Implicit داخل آن است. ولی برای Rollback کرد آن باید یک تراکنش به صورت Explicit ساخته شود. پاسخ به دیدگاه Hamid J. Fard ۲۳ / ۰۶ / ۹۴ - ۰۲:۱۲ ممنون. پاسخ به دیدگاه 1 2
