0

For reporting purposes, we have a DWH (data warehouse) doing ETLs (extract-transform-load) to retrieve data from selected tables in a production OLTP (on-line transaction processing) database.

The ETL extracts data with incremental manner, so it only fetches the changed part of data. Tentatively, we believe this should not affect the size of data as a result.

It is a simple mapping, so for the selected tables, DWH has the same columns as OLTP. The DWH is SQL Server and the OLTP database is MySQL. Of course, the MySQL data types need to translate to corresponding types in SQL Server context, and we followed the standard in Microsoft SSMA (SQL Server Migration Assistant).

We noticed that the data became times bigger in SQL Server than in MySQL. For example, in an e-commerce Magento application:

  • The sales_order table contains 7'100'000 rows with size 5.5GB.
  • However, in the data warehouse, the same table sized 20GB with the same number of rows.
    Please see a partial table definition below.

We checked the SQL Server database, it has SQL_Latin1_General_CP1_CI_AS collation and Simple recovery model. And the MySQL OLTP has default collation latin1_swedish_ci.

Our Questions:

  • In our setting, why SQL Server got times bigger than MySQL for the same data? Please kindly point out if we missed something and the DWH can get smaller instead.
  • The straight mapping between OLTP and DWH was simple to implement, and it worked so far, so good. However, we knew that there are many columns fetched but never used in reporting. So, we wonder if there are better designs or best practices in data warehousing.

We highly appreciate any hints and suggestions.

Details of the sample partial table definitions:

  1. MySQL OLTP, also refer to the model in Magento open-source repository:
-- msab_magento.sales_order definition

CREATE TABLE `sales_order` (
  `entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
  `state` varchar(32) DEFAULT NULL COMMENT 'State',
  `status` varchar(32) DEFAULT NULL COMMENT 'Status',
  `coupon_code` varchar(255) DEFAULT NULL COMMENT 'Coupon Code',
  `protect_code` varchar(255) DEFAULT NULL COMMENT 'Protect Code',
  `shipping_description` varchar(255) DEFAULT NULL COMMENT 'Shipping Description',
  `is_virtual` smallint(5) unsigned DEFAULT NULL COMMENT 'Is Virtual',
  `store_id` smallint(5) unsigned DEFAULT NULL COMMENT 'Store ID',
  `customer_id` int(10) unsigned DEFAULT NULL COMMENT 'Customer ID',
  `base_discount_amount` decimal(20,4) DEFAULT NULL COMMENT 'Base Discount Amount',
  `base_discount_canceled` decimal(20,4) DEFAULT NULL COMMENT 'Base Discount Canceled',
  `base_discount_invoiced` decimal(20,4) DEFAULT NULL COMMENT 'Base Discount Invoiced',
  `base_discount_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Base Discount Refunded',
  `base_grand_total` decimal(20,4) DEFAULT NULL COMMENT 'Base Grand Total',
  `base_shipping_amount` decimal(20,4) DEFAULT NULL COMMENT 'Base Shipping Amount',
  `base_shipping_canceled` decimal(20,4) DEFAULT NULL COMMENT 'Base Shipping Canceled',
  `base_shipping_invoiced` decimal(20,4) DEFAULT NULL COMMENT 'Base Shipping Invoiced',
  `base_shipping_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Base Shipping Refunded',
  `base_shipping_tax_amount` decimal(20,4) DEFAULT NULL COMMENT 'Base Shipping Tax Amount',
  `base_shipping_tax_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Base Shipping Tax Refunded',
  `base_subtotal` decimal(20,4) DEFAULT NULL COMMENT 'Base Subtotal',
  `base_subtotal_canceled` decimal(20,4) DEFAULT NULL COMMENT 'Base Subtotal Canceled',
  `base_subtotal_invoiced` decimal(20,4) DEFAULT NULL COMMENT 'Base Subtotal Invoiced',
  `base_subtotal_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Base Subtotal Refunded',
  `base_tax_amount` decimal(20,4) DEFAULT NULL COMMENT 'Base Tax Amount',
  `base_tax_canceled` decimal(20,4) DEFAULT NULL COMMENT 'Base Tax Canceled',
  `base_tax_invoiced` decimal(20,4) DEFAULT NULL COMMENT 'Base Tax Invoiced',
  `base_tax_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Base Tax Refunded',
  `base_to_global_rate` decimal(20,4) DEFAULT NULL COMMENT 'Base To Global Rate',
  `base_to_order_rate` decimal(20,4) DEFAULT NULL COMMENT 'Base To Order Rate',
  `base_total_canceled` decimal(20,4) DEFAULT NULL COMMENT 'Base Total Canceled',
  `base_total_invoiced` decimal(20,4) DEFAULT NULL COMMENT 'Base Total Invoiced',
  `base_total_invoiced_cost` decimal(20,4) DEFAULT NULL COMMENT 'Base Total Invoiced Cost',
  `base_total_offline_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Base Total Offline Refunded',
  `base_total_online_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Base Total Online Refunded',
  `base_total_paid` decimal(20,4) DEFAULT NULL COMMENT 'Base Total Paid',
  `base_total_qty_ordered` decimal(12,4) DEFAULT NULL COMMENT 'Base Total Qty Ordered',
  `base_total_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Base Total Refunded',
  `discount_amount` decimal(20,4) DEFAULT NULL COMMENT 'Discount Amount',
  `discount_canceled` decimal(20,4) DEFAULT NULL COMMENT 'Discount Canceled',
  `discount_invoiced` decimal(20,4) DEFAULT NULL COMMENT 'Discount Invoiced',
  `discount_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Discount Refunded',
  `grand_total` decimal(20,4) DEFAULT NULL COMMENT 'Grand Total',
  `shipping_amount` decimal(20,4) DEFAULT NULL COMMENT 'Shipping Amount',
  `shipping_canceled` decimal(20,4) DEFAULT NULL COMMENT 'Shipping Canceled',
  `shipping_invoiced` decimal(20,4) DEFAULT NULL COMMENT 'Shipping Invoiced',
  `shipping_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Shipping Refunded',
  `shipping_tax_amount` decimal(20,4) DEFAULT NULL COMMENT 'Shipping Tax Amount',
  `shipping_tax_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Shipping Tax Refunded',
  `store_to_base_rate` decimal(12,4) DEFAULT NULL COMMENT 'Store To Base Rate',
  `store_to_order_rate` decimal(12,4) DEFAULT NULL COMMENT 'Store To Order Rate',
  `subtotal` decimal(20,4) DEFAULT NULL COMMENT 'Subtotal',
  `subtotal_canceled` decimal(20,4) DEFAULT NULL COMMENT 'Subtotal Canceled',
  `subtotal_invoiced` decimal(20,4) DEFAULT NULL COMMENT 'Subtotal Invoiced',
  `subtotal_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Subtotal Refunded',
  `tax_amount` decimal(20,4) DEFAULT NULL COMMENT 'Tax Amount',
  `tax_canceled` decimal(20,4) DEFAULT NULL COMMENT 'Tax Canceled',
  `tax_invoiced` decimal(20,4) DEFAULT NULL COMMENT 'Tax Invoiced',
  `tax_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Tax Refunded',
  `total_canceled` decimal(20,4) DEFAULT NULL COMMENT 'Total Canceled',
  `total_invoiced` decimal(20,4) DEFAULT NULL COMMENT 'Total Invoiced',
  `total_offline_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Total Offline Refunded',
  `total_online_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Total Online Refunded',
  `total_paid` decimal(20,4) DEFAULT NULL COMMENT 'Total Paid',
  `total_qty_ordered` decimal(12,4) DEFAULT NULL COMMENT 'Total Qty Ordered',
  `total_refunded` decimal(20,4) DEFAULT NULL COMMENT 'Total Refunded',
  `can_ship_partially` smallint(5) unsigned DEFAULT NULL COMMENT 'Can Ship Partially',
  `can_ship_partially_item` smallint(5) unsigned DEFAULT NULL COMMENT 'Can Ship Partially Item',
  `customer_is_guest` smallint(5) unsigned DEFAULT NULL COMMENT 'Customer Is Guest',
  `customer_note_notify` smallint(5) unsigned DEFAULT NULL COMMENT 'Customer Note Notify',
  `billing_address_id` int(11) DEFAULT NULL COMMENT 'Billing Address ID',
  `customer_group_id` int(11) DEFAULT NULL,
    ...
  `reward_points_balance_refund` int(11) DEFAULT NULL COMMENT 'Reward Points Balance Refund',
  PRIMARY KEY (`entity_id`),
  UNIQUE KEY `SALES_ORDER_INCREMENT_ID_STORE_ID` (`increment_id`,`store_id`),
  KEY `SALES_ORDER_STATUS` (`status`),
  KEY `SALES_ORDER_STATE` (`state`),
  KEY `SALES_ORDER_STORE_ID` (`store_id`),
  KEY `SALES_ORDER_CREATED_AT` (`created_at`),
  KEY `SALES_ORDER_CUSTOMER_ID` (`customer_id`),
  KEY `SALES_ORDER_EXT_ORDER_ID` (`ext_order_id`),
  KEY `SALES_ORDER_QUOTE_ID` (`quote_id`),
  KEY `SALES_ORDER_UPDATED_AT` (`updated_at`),
  KEY `SALES_ORDER_SEND_EMAIL` (`send_email`),
  KEY `SALES_ORDER_EMAIL_SENT` (`email_sent`),
  CONSTRAINT `SALES_ORDER_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE SET NULL,
  CONSTRAINT `SALES_ORDER_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=71xxxxx DEFAULT CHARSET=utf8 COMMENT='Sales Flat Order';

  1. SQL Server DWH, generated by Microsoft SSMA for MySQL:
/****** Object:  Table [msab_magento].[sales_order]    Script Date: 10/11/2023 3:17:43 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [msab_magento].[sales_order](
    [entity_id] [bigint] IDENTITY(2956088,1) NOT NULL,
    [state] [nvarchar](32) NULL,
    [status] [nvarchar](32) NULL,
    [coupon_code] [nvarchar](255) NULL,
    [protect_code] [nvarchar](255) NULL,
    [shipping_description] [nvarchar](255) NULL,
    [is_virtual] [int] NULL,
    [store_id] [int] NULL,
    [customer_id] [bigint] NULL,
    [discount_amount] [decimal](20, 4) NULL,
    [discount_canceled] [decimal](20, 4) NULL,
    [discount_invoiced] [decimal](20, 4) NULL,
    [discount_refunded] [decimal](20, 4) NULL,
    [grand_total] [decimal](20, 4) NULL,
    [shipping_amount] [decimal](20, 4) NULL,
    [shipping_canceled] [decimal](20, 4) NULL,
    [shipping_invoiced] [decimal](20, 4) NULL,
    [shipping_refunded] [decimal](20, 4) NULL,
    [shipping_tax_amount] [decimal](20, 4) NULL,
    [shipping_tax_refunded] [decimal](20, 4) NULL,
    [store_to_base_rate] [decimal](12, 4) NULL,
    [store_to_order_rate] [decimal](12, 4) NULL,
    [subtotal] [decimal](20, 4) NULL,
    [subtotal_canceled] [decimal](20, 4) NULL,
    [subtotal_invoiced] [decimal](20, 4) NULL,
    [subtotal_refunded] [decimal](20, 4) NULL,
    [tax_amount] [decimal](20, 4) NULL,
    [tax_canceled] [decimal](20, 4) NULL,
    [tax_invoiced] [decimal](20, 4) NULL,
    [tax_refunded] [decimal](20, 4) NULL,
    [total_canceled] [decimal](20, 4) NULL,
    [total_invoiced] [decimal](20, 4) NULL,
    [total_offline_refunded] [decimal](20, 4) NULL,
    [total_online_refunded] [decimal](20, 4) NULL,
    [total_paid] [decimal](20, 4) NULL,
    [total_qty_ordered] [decimal](12, 4) NULL,
    [total_refunded] [decimal](20, 4) NULL,
    [can_ship_partially] [int] NULL,
    [can_ship_partially_item] [int] NULL,
    [customer_is_guest] [int] NULL,
    [customer_note_notify] [int] NULL,
    [billing_address_id] [int] NULL,
    [customer_group_id] [int] NULL,
    [edit_increment] [int] NULL,
    ...
    [shipping_incl_tax] [decimal](20, 4) NULL,
 CONSTRAINT [PK_sales_order_entity_id] PRIMARY KEY CLUSTERED 
(
    [entity_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY],
 CONSTRAINT [sales_order$SALES_ORDER_INCREMENT_ID_STORE_ID] UNIQUE NONCLUSTERED 
(
    [increment_id] ASC,
    [store_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [msab_magento].[sales_order] ADD  DEFAULT (NULL) FOR [state]
GO

ALTER TABLE [msab_magento].[sales_order] ADD  DEFAULT (NULL) FOR [status]
GO

...

ALTER TABLE [msab_magento].[sales_order] ADD  DEFAULT (NULL) FOR [shipping_incl_tax]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'msab_magento.sales_order' , @level0type=N'SCHEMA',@level0name=N'msab_magento', @level1type=N'TABLE',@level1name=N'sales_order'
GO

14
  • 2
    One factor is SQL Server uses a UCS-2 collation (similar to UTF-16) by default, which is two bytes for most characters for nvarchar columns, where MySql is UTF-8, which is one byte for most characters. Oct 11, 2023 at 21:32
  • 1
    Another factor is if it's set to FULL recovery mode, you may also have a large transaction log after importing that you really don't need for a data warehouse. Oct 11, 2023 at 21:36
  • 1
    @JoelCoehoorn utf8 old spec in mysql can take up to 3 bytes, under the new spec for mysql up to 4 bytes per character.
    – Shadow
    Oct 11, 2023 at 21:43
  • 1
    The text talks about "latin1" on both sides, yet, as Joel points out, MySQL says DEFAULT CHARSET=utf8 and SQL Server says NVARCHAR. That might explain a factor of 2, but not a factor of 20/5.5.
    – Rick James
    Oct 11, 2023 at 22:26
  • 1
    Do most of the columns contain NULL? Does the bigger copy use 10 bytes (or whatever) anyway?
    – Rick James
    Oct 11, 2023 at 22:34

1 Answer 1

1

The sales_order table contains 7'100'000 rows with size 5.5GB. However, in the data warehouse, the same table sized 20GB

SQL Server has several different table compression options. The one most commonly used for large data warehouse tables is Columnstore, which can produce 10x compression on tables with millions of rows.

But both ROW and PAGE compression will change the storage format for all your DECIMAL columns from fixed-width to variable-width. In an uncompressed table DECIMAL(20,4) is a 13-byte fixed-width column.

1
  • 1
    We have verified the Row Compression on a virtual machine. 1) Without any compression, the raw data size is 30GB, bigger than 20GB so we will check if the DWH server already has some compression settings. 2) After compressing the table, the size dropped to 14GB. 3) After compressing all the indices, the size finally dropped to 4.6GB. So, it is indeed 10x compression. So, our next step will be to test the Page Compression mode as well and figure out whether we need to relax a little for performance reasons. Many thanks for the help, and have a good weekend, everyone.
    – James
    Oct 14, 2023 at 0:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.