table<\/del> clustered index scan<\/p>\n\r\nUSE [AdventureWorks2016CTP3]\r\nGO\r\nSET STATISTICS IO, TIME ON\r\nGO\r\n\r\nSELECT * FROM Person.Person\r\n--(19972 row(s) affected)\r\n--Table 'Person'. Scan count 1, logical reads 3838, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\n\r\n-- SQL Server Execution Times:\r\n-- CPU time = 31 ms, elapsed time = 1318 ms.\r\n\r\nSELECT * FROM Person.Person_gzip\r\n--(19972 row(s) affected)\r\n--Table 'Person_gzip'. Scan count 1, logical reads 1290, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\n\r\n-- SQL Server Execution Times:\r\n-- CPU time = 47 ms, elapsed time = 430 ms.\r\n\r\nSELECT * FROM Person.Person_nvarchar\r\n--(19972 row(s) affected)\r\n--Table 'Person_nvarchar'. Scan count 1, logical reads 3173, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\n\r\n-- SQL Server Execution Times:\r\n-- CPU time = 46 ms, elapsed time = 600 ms.\r\n\r\n<\/pre>\nAnd once again, when doing a full scan, the victory is for the smallest table, hence the result is the same, XML, NVARCHAR(MAX), COMPRESSED. That is reasonable, the less you read, the faster you finish.<\/p>\n
But there must be some caveats round the corner, it can’t be all benefit.<\/p>\n
For this query I’ll leave out of the equation the original table as XML cannot be queried as normal string and I feel lazy to write xquery now \ud83d\ude42<\/p>\n
I’m going to try find all married people, and I’ve seen there is an attribute in the XML that contains that info, I’d query it as a string just for demonstration purposes, do not take this as best practice, if you need to query XML, be sure you use XML data type and xquery (combined with XML indexes for best performance)<\/p>\n
\r\n-- if we look for a value within the compressed column\r\nSELECT CAST(DECOMPRESS(Demographics) AS NVARCHAR(MAX)), * \r\n\tFROM Person.Person_gzip\r\n\tWHERE CAST(DECOMPRESS(Demographics) AS NVARCHAR(MAX)) LIKE '%<MaritalStatus>M%' \r\n\r\n-- same search within the nvarchar column\r\nSELECT * \r\n\tFROM Person.Person_nvarchar\r\n\tWHERE Demographics LIKE '%<MaritalStatus>M%' \r\n<\/pre>\nAnd here is the penalty<\/p>\n
\r\n\/*\r\nSQL Server parse and compile time: \r\n CPU time = 0 ms, elapsed time = 0 ms.\r\n\r\n(10011 row(s) affected)\r\nTable 'Person_gzip'. Scan count 1, logical reads 1289, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\n\r\n SQL Server Execution Times:\r\n CPU time = 1812 ms, elapsed time = 2958 ms.\r\n\r\n(10011 row(s) affected)\r\nTable 'Person_nvarchar'. Scan count 1, logical reads 3173, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\n\r\n SQL Server Execution Times:\r\n CPU time = 282 ms, elapsed time = 992 ms.\r\n*\/\r\n<\/pre>\nDecompressing each value to find something is not that cheap anymore. After many runs I can see that consistently takes about 3 times the total time and about 5 times the CPU time than if you don’t have to decompress it.
\n <\/p>\n
Conclusion<\/strong><\/p>\nCOMPRESS is a nice feature, but be sure that fits your use case, because trying to save in storage can impact the overall performance very badly.
\n
\n <\/p>\n","protected":false},"excerpt":{"rendered":"
More new functionalities coming along with SQL Server 2016, this time I’ve been playing with COMPRESS and DECOMPRESS One of the most exciting things for me in this new version of SQL Server…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,21],"tags":[30,23,25],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/181"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=181"}],"version-history":[{"count":0,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/181\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=181"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=181"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=181"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"table_sizes\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/03\/table_sizes.png\")
<\/a><\/p>\n