خانه SQL Server توابع COMPRESS و DECOMPRESS در SQL Server 2016 SQL Server افزایش سرعت SQL Server نوشته شده توسط: سید محمد حسینی تاریخ انتشار: ۰۹ اسفند ۱۳۹۴ آخرین بروزرسانی: ۱۱ اردیبهشت ۱۴۰۱ زمان مطالعه: 6 دقیقه ۰ (۰) مقدمه فشرده سازی داده ها تا نسخه SQL Server 2014 برای فشرده سازی در سطح سطر و ستون در دسترس می باشد، ولی این قابلیت فقط با فعال کردن فشرده سازی در سطح صفحه(page) یا سطر(row) قابل استفاده است. در SQL Server 2016 CTP3.1، توابعی جهت فشرده سازی داده هایی که به طور مشخص باید فشرده شوند معرفی شده است. در این مقاله بررسی می کنیم که این توابع چگونه می توانند برای ما سودمند باشند. در SQL Server 2016 CTP به طور درونی(builtin) توابعی جهت Compress و Decompress کردن داده ها ارائه شده است. COMPRESS: این تابع داده های ورودی را با استفاده از الگوریتم GZIP فشرده کرده و نتیجه باینری شده را در قالب نوع داده VARBINARY(MAX) باز می گرداند. DECOMPRESS: این تابع داده های باینری و فشرده ورودی را با استفاده از الگوریتم GZIP از حالت فشرده خارج کرده و نتیجه باینری شده را در قالب نوع داده (VARBINARY(MAX باز می گرداند. شکل کلی استفاده از این توابع در زیر ارائه شده است: COMPRESS ( expression ) DECOMPRESS ( expression ) عبارت expression می تواند یکی از نوع های داده ای زیر باشد: nvarchar(n), nvarchar(max), varchar(n), varchar(max), varbinary(n), varbinary(max), char(n), nchar(n), or binary(n) این دو تابع را می توان به صورت زیر مورد استفاده قرار داد: SELECT COMPRESS ('Compress and Decompress Function SQL Server') و در صورتی که خروجی فوق را از حالت فشرده خارج کنیم نتیجه زیر حاصل خواهد شد: SELECT DECOMPRESS (0x1F8B080000000000040073CECF2D284A2D2E5648CC4B5170494D8671DD4AF3924B32F3F31482037D1482538BCA528B004E16B8732B000000) با توجه به اینکه خروجی تابع DECOMPRESS از نوع باینری می باشد، برای دیدن داده اصلی که قبلا فشرده شده است نیاز به استفاده از تابع CAST-جهت تبدیل داده خروجی باینری به نوع داده مورد نیاز- به صورت زیر داریم: مثال هایی دیگر از توابع COMPRESS و DECOMPRESS حال که با این توابع آشنا شدیم، اجازه دهید یک جدول ایجاد کرده و تعدادی رکورد را با استفاده از تابع COMPRESS در آن درج کنیم. CREATE TABLE dbo.product ( Id INT IDENTITY(1,1), Name NVARCHAR(max), Description VARBINARY(MAX) ) GO INSERT INTO dbo.product (Name, Description) VALUES('TestDemo', COMPRESS(N'This Demo is to show how we can use the new Compress and decompress function in sql server 2016 CTP 3.1 onwards')) حال اگر از دستور SELECT به صورت عادی استفاده شود نتیجه ای مشابه زیر به دست خواهد آمد: همانطور که قبلا گفته شد، برای دیدن متن واقعی باید از تابع CAST استفاده شود. برای اینکار تابع CAST به همراه تابع DECOMPRESS را می توان به صورت زیر مورد استفاده قرار داد: SELECT Id, Name, description, CAST( DECOMPRESS(description) AS NVARCHAR(MAX)) AS DecomressedTest FROM dbo.product میزان فضای ذخیره سازی داده های فشرده شده اجازه دهید بررسی کنیم که این فشرده سازی در کجا می تواند مفید باشد. برای بررسی این موضوع ما سه رشته با طول های مختلف را ایجاد کرده و حجم آنها را پیش/پس از فشرده سازی با هم مقایسه می کنیم. DECLARE @TextToCompress1 VARCHAR(MAX) DECLARE @TextToCompress2 VARCHAR(MAX) DECLARE @TextToCompress3 VARCHAR(MAX) SELECT @TextToCompress1 = 'Data Compression SQL SERVER' SELECT @TextToCompress2 = 'The compression technique which were available with Prior Version of SQL server are Row level and Column Level compression. SQL Server 2016 CTP 3.1 Onwards we have newly introduced functions for Compression. In this Tip we are going to explore and see how this can be beneficial.' SELECT @TextToCompress3='The compression technique which were available with Prior Version of SQL server are Row level and Column Level compression. SQL Server 2016 CTP 3.1 Onwards we have newly introduced functions for Compression. In this Tip we are going to explore and see how this can be beneficial. The COMPRESS function compresses the data provided as the input expression and must be invoked for each section of data to be compressed. This is demo purposes only.' SELECT DATALENGTH(@TextToCompress1) AS 'Before_compression-1', DATALENGTH(COMPRESS(@TextToCompress1)) AS 'After_compression-1', DATALENGTH(@TextToCompress2) AS 'Before_compression-2', DATALENGTH(COMPRESS(@TextToCompress2)) AS 'After_compression-2', DATALENGTH(@TextToCompress3) AS 'Before_compression-3', DATALENGTH(COMPRESS(@TextToCompress3)) AS 'After_compression-3' در اینجا می توان به سادگی دریافت که فشرده سازی برای داده های با طول کوتاه اصلا مناسب نیست، ولی برای داده های با طول زیاد می تواند تا حد زیادی مناسب باشد، بنابراین ما نیاز به بررسی بیشتری جهت فشرده سازی داده ها داریم، زیرا این فشرده سازی در صورتی که به درستی برنامه ریزی نشده باشد می تواند سربار زیادی را تحمیل کند. استفاده از حالت های مختلف CAST به همراه DECOMPRESS همانگونه که گفته شد تابع DECOMPRESS بدون استفاده از تابع CAST قادر نیست داده های اصلی را بازگرداند، به همین علت در صورتی که از نوع داده صحیحی برای تابع CAST استفاده نشود، ممکن است نتیجه درستی بازگردانده نشود. DECLARE @varcharValue NVARCHAR(MAX) = 'SQL Server 2016 Compress and Decompress function' DECLARE @compressedValue VARBINARY(MAX) SET @compressedValue = COMPRESS(@varcharValue) SELECT @varcharValue OriginalValue, CAST(DECOMPRESS(@compressedValue) AS VARCHAR(MAX)) AS Decompress1, CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(10)) AS Decompress2, CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(20)) AS Decompress3, CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(MAX)) AS Decompress4 فضای ذخیره سازی صرفه جویی شده و میزان کارایی به دست آمده با تابع COMPRESS حال اجازه دهید یکی دیگر از جاهایی که فشرده سازی می تواند مناسب باشد را بررسی کنیم. برای این کار، دو جدول ایجاد کرده و مقداری اطلاعات را به صورت عادی و فشرده شده در این دو جدول ذخیره می کنیم و سپس میزان فضای ذخیره سازی مورد نیاز آنها را مقایسه می کنیم: CREATE TABLE Test_uncompress (test varchar(max)) --table to hold uncompressed data CREATE TABLE Test_compressed (test Varbinary(max)) -- table to hold compressed data GO -- Now insert test data into uncompressed table INSERT INTO Test_uncompress values (replicate('DemoCompress and uncompress function',5000)) GO 200000 --Insert data into compressed table by doing compress of values stored in uncompressed table INSERT INTO Test_compressed SELECT compress(test) FROM Test_uncompress اکنون اگر با استفاده از sp_spaceused دو جدول را با هم مقایسه کنیم، نتیجه زیر حاصل می شود: همانطور که مشاهده می کنید حجم جدولی که داده های آن فشرده شده است ۲۰۸۰۸KB و حجم جدولی که داده های آن فشرده نشده است ۱۶۰۰۲۰۰KB می باشد-که تفاوت چشم گیری است. همچنین، اگر از دستور SELECT برای هر دو جدول به صورت ساده استفاده گردد، می توانیم تفاوت کارایی این دو جدول را نیز مشاهده کینم، البته در این مثال از تابع CAST استفاده نشده است که این خود می تواند کمی زمان پرسوجو را افزایش دهد. SET STATISTICS TIME ON SET STATISTICS IO ON GO SELECT TOP 1000 * FROM dbo.Test_uncompress GO SELECT TOP 1000 * FROM Test_compressed GO با توجه به تصویر فوق، از میزان زمان مورد نیاز و همچنین تعداد IO استفاده شده برای خواندن اطلاعات از این دو جدول ، می توان به میزان کارایی فشرده سازی در جداول پی برد. نکته: در انتها باید به این نکته مهم اشاره کنم که داده های فشرده شده نمی توانند در ایندکس استفاده شوند. چه رتبه ای میدهید؟ میانگین ۰ / ۵. از مجموع ۰ اولین نفر باش معرفی نویسنده مقالات 11 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده سید محمد حسینی معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان 5.700.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ مهدی ربانی ذبیحی ۱۰ / ۱۲ / ۹۴ - ۱۰:۱۸ سلام واقعا چیزی نمیشه گفت جز اینکه فوق العاده بود توی مقاله های منتشر شده چند وقت اخیر واقعا میشه گفت یک مقاله خاص. پاسخ به دیدگاه سید محسن علوی ۱۰ / ۱۲ / ۹۴ - ۰۸:۲۷ سلام واقعا عالی بود فقط همین رو میتونم بگم. پاسخ به دیدگاه عاطفه حسن پور ۱۰ / ۱۲ / ۹۴ - ۰۰:۲۲ با سلام و عرض خسته نباشید بسیار بسیار مقاله عالی ارائه کردین و مهمترین وجه تمایز مقاله شما مثال های زیبا اون بود ممنون پاسخ به دیدگاه 1 2