Compressing Integers
-
Arrgghh
2* 10^10
Apparently you all are very poor at reading my mind.
-
...or we just need to install that new clairvoyance plugin into our browsers.
-
The randomness component of this question makes it tricky. We don't know the full details of the columnstore compression algorithm, so it's difficult, it not impossible, to construct a data set that gets better compression under page compression compared to columnstore compression. A simple test with 1048576 random integers suggests that columnstore will be the winner:
USE tempdb;CREATE TABLE RS_NO_COMPRESS (ID INT NOT NULL);
CREATE TABLE RS_ROW_COMPRESS (ID INT NOT NULL) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE RS_PAGE_COMPRESS (ID INT NOT NULL) WITH (DATA_COMPRESSION = PAGE);
CREATE TABLE CS (ID INT NOT NULL, INDEX CCI_CS CLUSTERED COLUMNSTORE);CREATE TABLE STG_RAND (ID INT NOT NULL);DECLARE @rows_inserted INT = 0;
SET NOCOUNT ON;
BEGIN TRANSACTION
WHILE @rows_inserted < 1048576
BEGIN
INSERT INTO STG_RAND
SELECT CAST(2000000000 * RAND() AS INT);
SET @rows_inserted = @rows_inserted + 1;
END;
COMMIT TRANSACTION;INSERT INTO RS_NO_COMPRESS WITH (TABLOCK)
SELECT * FROM STG_RAND;INSERT INTO RS_ROW_COMPRESS WITH (TABLOCK)
SELECT * FROM STG_RAND;INSERT INTO RS_PAGE_COMPRESS WITH (TABLOCK)
SELECT * FROM STG_RAND;INSERT INTO CS WITH (TABLOCK)
SELECT * FROM STG_RAND;EXEC sp_spaceused 'RS_NO_COMPRESS'; -- 27792 KB
EXEC sp_spaceused 'RS_ROW_COMPRESS'; -- 23376 KB
EXEC sp_spaceused 'RS_PAGE_COMPRESS'; -- 23376 KB
EXEC sp_spaceused 'CS'; -- 9896 KBThe winning table might be around 10 TB if fully populated, so I don't know if this meets the spirit of the question.
- Steve Jones - SSC Editor - Tuesday, September 26, 2017 11:38 AM
My crystal ball is badly cracked, so....
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex” - Stewart "Arturius" Campbell - Wednesday, September 27, 2017 12:02 AMSteve Jones - SSC Editor - Tuesday, September 26, 2017 11:38 AM
My crystal ball is badly cracked, so....
Just for the record: one trillion is 10**12 -- Trillion
So I think you missed it by an order of magnitude... But thanks anyway!
-
Haven't used columnstore yet so learned something new, thanks.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply