Sometimes It's The Little Things That Count (6)

Way long ago, Version 11 of SQL Anywhere introduced UNLOAD COMPRESSED... here's an excerpt from the "What's New in Version 11.0.0" section in the Help:

---

UNLOAD statement enhancements   When using the UNLOAD statement, you can now specify whether to compress and/or encrypt the data that is being unloaded by specifying the COMPRESSED or ENCRYPTED clauses, respectively. See UNLOAD statement.

Files compressed or encrypted using these clauses can only be loaded (for example, using LOAD TABLE) by SQL Anywhere 11.0.0 database servers (or newer). Files compressed or encrypted using other tools are not usable by SQL Anywhere.

---

Whaddaya mean, it didn't make it to the Top 10 list?

Apparently not...

Does UNLOAD COMPRESSED work?

It sure does; here's a test involving three small tables... small, as 25,000 rows and 11M of disk space in the database.

Here's one of the tables and one of the rows in that table... lots of numbers, lots of strings, some NULLs, nothing too exciting, just your average Table From Hell (because in the real world, this table contains millions of rows, gigabytes of data):

-- DBA.table_2 (table_id 739) in ddd - Dec 12 2010 9:05:26AM - Print - Foxhound © 2010 RisingRoad

CREATE TABLE DBA.table_2 ( -- 14,068 rows, 7.3M total = 7M table + 0 ext + 304k index, 544 bytes per row
   sampling_id                                   UNSIGNED INT NOT NULL,
   sample_set_number             /* PK        */ UNSIGNED BIGINT NOT NULL,
   connection_number             /* PK        */ BIGINT NOT NULL,

   ... 60 columns omitted ...

   CONSTRAINT ASA245 PRIMARY KEY ( -- 304k
      sample_set_number,
      connection_number )
 );

-- Parents of DBA.table_2
-- none --
 
-- Children
-- none -- 

3,34,1,,,,,,,'11.0.1.2276',4096,0.093696,0,3157,1954,617,619,1,34,0,0,0,0,0,
'2010-12-01 06:21:33.254000','call dbo.sa_post_login_procedure()',0,0,0,
'2010-12-01 06:21:32.968000',0,'ddd11-2','',15,5,0,3,0,23,20,2,0,22,'Idle',
0.104363,0.007064,0.032855,0.000000,0.000238,'PREFETCH',22,0,0,24,'',0,'DBA',
0.093696,0.000000,1,0,0,0

Here are the UNLOAD and LOAD statements used to test the COMPRESSED feature:

UNLOAD TABLE table_2 TO 'temp/unloaded_2_compressed.dat'
   APPEND OFF
   COMPRESSED
   DELIMITED BY ','
   ESCAPES ON
   FORMAT 'TEXT'
   QUOTES ON;

LOAD TABLE copy_table_2 FROM 'temp/unloaded_2_compressed.dat'
   CHECK CONSTRAINTS OFF
   COMPRESSED
   COMPUTES OFF
   DELIMITED BY ','
   ESCAPES ON
   FORMAT 'TEXT'
   ORDER OFF
   QUOTES ON
   STRIP OFF;

Does COMPRESSED save space?

Lots, as in output files that are 88% smaller in this test.

Is COMPRESSED slower?

The answer is no, COMPRESSED is actually a bit faster if you count the LOAD time: 8% faster in this test.

Here are the numbers...

Why don't we all use UNLOAD COMPRESSED, all the time?

You've got me, I dunno :)