توابع COMPRESS و DECOMPRESS در  SQL Server 2016

توابع COMPRESS و DECOMPRESS در SQL Server 2016

نوشته شده توسط: سید محمد حسینی
۰۹ اسفند ۱۳۹۴
زمان مطالعه: 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 استفاده شده برای خواندن اطلاعات از این دو جدول ، می توان به میزان کارایی فشرده سازی در جداول پی برد.
نکته: در انتها باید به این نکته مهم اشاره کنم که داده های فشرده شده نمی توانند در ایندکس استفاده شوند.

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

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

اولین نفر باش

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

    • سلام
      آیا تنطیم کردن datacompression page در ایندکس های پرکاربرد باعث خرابی میشن؟
      من اجرا کردم و دیدم که logical read خیلی کم شدو سرعت کوئری م بالاتر رفته ولی جای دیگه خوندم که نباید روی جداول یا ایندکس هایی که read ,write بالا دارن اجرا کرد
      ممنون میشم جواب بدید

    • سلام
      آیا تنطیم کردن datacompression page در ایندکس های پرکاربرد باعث خرابی میشن؟
      من اجرا کردم و دیدم که logical read خیلی کم شدو سرعت کوئری م بالاتر رفته ولی جای دیگه خوندم که نباید روی جداول یا ایندکس هایی که read ,write بالا دارن اجرا کرد
      ممنون میشم جواب بدید

    • با تشکر از شما
      Missing indexes
      Outdated statistics
      Non-SARGable predicates
      Implicit conversions
      Parameter sniffing.
      Ad hoc non-parameterized queries
      Inappropriate parallelism
      و….
      در افزایش CPU Usage تاثیر مستقیم دارند.
      تاملی بودن سایت نیک آموز واقعا یک نقطه ی عطف محسوب می شود.

    • با تشکر از شما

      Missing indexes
      Outdated statistics
      Non-SARGable predicates
      Implicit conversions
      Parameter sniffing.
      Ad hoc non-parameterized queries
      Inappropriate parallelism
      و….
      در افزایش CPU Usage تاثیر مستقیم دارند.

      تاملی بودن سایت نیک آموز واقعا یک نقطه ی عطف محسوب می شود.

    • با سپاس فراوان از شما :
      در این لینک :
      https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/12/08/built-in-functions-for-compressiondecompression-in-sql-server-2016/
      این موضوع اومده که :

      These functions use standard GZip algorithm so you can compress data in application layer/client side and send compressed data to SQL Server, or you can compress data in SELECT query and DECOMPRESS it in client side.

      هدف از عنوان این موضوع اینه که ما یه سرور داریم که CPU Usage آن به هنگام اجرای دستورات select بالاست (Decompress). از Compression بروی جدول و ایندکس استفاده میشه.

      افزایش CPU Usage در هنگام Fetch Data چه علتی به غیر Compression میتواند داشته باشد ؟؟

      خیلی خیلی ممنون از سایت خوبتون.

      • با سلام و عرض ادب
        به نقل از مهندس مسعود طاهری

        این مسئله به دلیل استفاده نادرست از ایندکس‌ها و Tune نیودن کوئری‌هاست.

    • با سپاس فراوان از شما :
      در این لینک :
      https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/12/08/built-in-functions-for-compressiondecompression-in-sql-server-2016/
      این موضوع اومده که :
      These functions use standard GZip algorithm so you can compress data in application layer/client side and send compressed data to SQL Server, or you can compress data in SELECT query and DECOMPRESS it in client side.
      هدف از عنوان این موضوع اینه که ما یه سرور داریم که CPU Usage آن به هنگام اجرای دستورات select بالاست (Decompress). از Compression بروی جدول و ایندکس استفاده میشه.
      افزایش CPU Usage در هنگام Fetch Data چه علتی به غیر Compression میتواند داشته باشد ؟؟
      خیلی خیلی ممنون از سایت خوبتون.

      • با سلام و عرض ادب
        به نقل از مهندس مسعود طاهری
        این مسئله به دلیل استفاده نادرست از ایندکس‌ها و Tune نیودن کوئری‌هاست.

    • با عرض سلام و خسته نباشید

      این فانکشن ها بار فشرده سازی را به سمت کلاینت و لایه برنامه می برند (مثلا افزایش CPU Usage)، یا اینکه این بار همچنان سمت سرور SQL است ؟؟

      ممنون

      • سلام و عرض ادب

        به نقل از مهندس مسعود طاهری

        دوست عزیز فشرده سازی سمت سرور انجام می‌شود.

    • با عرض سلام و خسته نباشید
      این فانکشن ها بار فشرده سازی را به سمت کلاینت و لایه برنامه می برند (مثلا افزایش CPU Usage)، یا اینکه این بار همچنان سمت سرور SQL است ؟؟
      ممنون

      • سلام و عرض ادب
        به نقل از مهندس مسعود طاهری
        دوست عزیز فشرده سازی سمت سرور انجام می‌شود.

    • سلام

      البته با توجه به تستی که من قبلاً انجام دادم متوجه شدم که اینمورد فقط برای جداول خاصی با حجم دیتاهای کم می تونه کاربرد موثری برای برنامه نویس و یا DBA داشته باشه (به عنوان مثال جدول کاربران و یا از این دست …)
      در جداولی با تعداد رکوردهای بالا، اینمورد واقعاً Performance بانک اطلاعاتی را تحت تاثیر منفی خودش قرار میده وشاید هم در مواردی منجربه Down شدن سرور در شرکتها و سازمانهای بزرگ  با تعداد کاربران زیاد بشه. بنابراین از این تکنیک خوبی هم که مایکروسافت در اختیار ما قرار داده باید مثل سایر تکنیک های دیگه، ازش بجا و درست استفاده کرد.
      • سلام 

        درست فرمودید
        اگر از این روش زیاد استفاده شود مطمئنا نتیجه منفی در بر خواهد داشت
        به عنوان مثال در یک جدول (حتی بزرگ) که فرضا در یک یا دو ستون آن متن طولانی ذخیره میشود مناسب است، چون حجم جدول و در نتیجه IO را کم میکند و بار پردازشی CPU هم زیاد نخواهد بود
        ولی اگر برای تعداد زیادی از ستون های یک جدول استفاده شود قطعا مناسب نخواهد بود
        هم از نظر حجم کاهش داده شده که ممکن است جواب برعکس بدهد(که در مقاله به آن اشاره شده است)
        هم از نظر بار پردازشی تحمیل شده به سیستم
    • سلام

      البته با توجه به تستی که من قبلاً انجام دادم متوجه شدم که اینمورد فقط برای جداول خاصی با حجم دیتاهای کم می تونه کاربرد موثری برای برنامه نویس و یا DBA داشته باشه (به عنوان مثال جدول کاربران و یا از این دست …)
      در جداولی با تعداد رکوردهای بالا، اینمورد واقعاً Performance بانک اطلاعاتی را تحت تاثیر منفی خودش قرار میده وشاید هم در مواردی منجربه Down شدن سرور در شرکتها و سازمانهای بزرگ  با تعداد کاربران زیاد بشه. بنابراین از این تکنیک خوبی هم که مایکروسافت در اختیار ما قرار داده باید مثل سایر تکنیک های دیگه، ازش بجا و درست استفاده کرد.
      • سلام 

        درست فرمودید
        اگر از این روش زیاد استفاده شود مطمئنا نتیجه منفی در بر خواهد داشت
        به عنوان مثال در یک جدول (حتی بزرگ) که فرضا در یک یا دو ستون آن متن طولانی ذخیره میشود مناسب است، چون حجم جدول و در نتیجه IO را کم میکند و بار پردازشی CPU هم زیاد نخواهد بود
        ولی اگر برای تعداد زیادی از ستون های یک جدول استفاده شود قطعا مناسب نخواهد بود
        هم از نظر حجم کاهش داده شده که ممکن است جواب برعکس بدهد(که در مقاله به آن اشاره شده است)
        هم از نظر بار پردازشی تحمیل شده به سیستم
  • 1
  • 2