Click on the action buttons in the action panel to compute the compression size and get the query execution times for tables with and without compression and generate a graph comparing the same

Operation



Get Compressed Size

Collects statistics for the FACT tables, retrieves their size and estimated compression ratio. The SQL used to collect this information are :



CALL ADMIN_CMD
   ('RUNSTATS ON TABLE SCHEMA.TABLE_NAME')

-- Determines the size of a table
SELECT CAST(CAST(NPAGES AS DOUBLE)/256 AS DECIMAL(5,3)) 
  FROM SYSIBM.SYSTABLES 
  WHERE CREATOR= 'SCHEMA' AND NAME='TABLE_NAME'
  
-- Estimates the compression size and compression ratio of an uncompressed table
SELECT PAGES_SAVED_PERCENT, CAST (TABSIZE*(CAST(100 - PAGES_SAVED_PERCENT AS DOUBLE)/100) AS DECIMAL (5,3)) 
  FROM TABLE(SELECT CAST(CAST(NPAGES AS DOUBLE)/256 AS DECIMAL(5,3)) AS TABSIZE FROM SYSIBM.SYSTABLES 
  WHERE CREATOR= 'SCHEMA' AND NAME='TABLE_NAME') 
	AS T1, TABLE (SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO('SCHEMA', 'TABLE_NAME', 'ESTIMATE')) AS T2
	

Size Comparison

  1. A graph comparing the uncompressed table size, the estimated table size and the actual compressed size is generated.
  2. If you hover your cursor over the bars in the graph you will see the exact sizes of each table.


Get Query Execution Times

Query execution time for tables with and without compression is computed

Note :

Results may vary depending on the volume of data. For optimal performance, use high volume of data.

To increase the volume of data, load the fact tables 'te_temp.subscriber_rated_usage' and 'te_temp.subscriber_dedicated_account_usage' with the corresponding .del files present in the LoadFiles directory.

Performance Comparison

  1. A graph comparing the query execution time of tables with and without compression is generated.
  2. If you hover your cursor over the bars in the graph you will see the exact execution time for each run.


Result


The tables size is considerably reduced after compression (depending on the number of rows in the table - more the no. of rows, better the compression ratio) resulting in reduced storage costs

The query execution time would reduce even with compression enabled.

Note :

The graph here might show an increase in the query execution time with Compression. This is because the amount of data in the tables is very low. The cost of retrieval of the data from the compressed table is not compensated by the amount of data.