{"id":3515,"date":"2018-06-11T12:48:07","date_gmt":"2018-06-11T12:48:07","guid":{"rendered":"http:\/\/bicortex.com\/?p=3515"},"modified":"2018-06-11T22:32:36","modified_gmt":"2018-06-11T22:32:36","slug":"vertica-mpp-database-overview-and-tpc-ds-benchmark-performance-analysis-part-4","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/vertica-mpp-database-overview-and-tpc-ds-benchmark-performance-analysis-part-4\/","title":{"rendered":"Vertica MPP Database Overview and TPC-DS Benchmark Performance Analysis (Part 4)"},"content":{"rendered":"

Testing Continued…<\/h3>\n

In the previous post<\/a> I started looking at some of the TPC-DS queries’ performance across a single node and multi-node environments. This post continues with the theme of performance analysis across the remaining set of queries and looks at high-level interactions between Vertica and Tableau\/PowerBI. Firstly though, let’s look at the remaining ten queries and how their execution times fared in the context of three different data and cluster sizes.<\/p>\n

\"\"<\/a><\/p>\n

\r\n--Query 44\r\nSELECT asceding.rnk,\r\n               i1.i_product_name best_performing,\r\n               i2.i_product_name worst_performing\r\nFROM  (SELECT *\r\n       FROM   (SELECT item_sk,\r\n                      Rank()\r\n                        OVER (\r\n                          ORDER BY rank_col ASC) rnk\r\n               FROM   (SELECT ss_item_sk         item_sk,\r\n                              Avg(ss_net_profit) rank_col\r\n                       FROM   tpc_ds.store_sales ss1\r\n                       WHERE  ss_store_sk = 4\r\n                       GROUP  BY ss_item_sk\r\n                       HAVING Avg(ss_net_profit) > 0.9 *\r\n                              (SELECT Avg(ss_net_profit)\r\n                                      rank_col\r\n                               FROM   tpc_ds.store_sales\r\n                               WHERE  ss_store_sk = 4\r\n                                      AND ss_cdemo_sk IS\r\n                                          NULL\r\n                               GROUP  BY ss_store_sk))V1)\r\n              V11\r\n       WHERE  rnk < 11) asceding,\r\n      (SELECT *\r\n       FROM   (SELECT item_sk,\r\n                      Rank()\r\n                        OVER (\r\n                          ORDER BY rank_col DESC) rnk\r\n               FROM   (SELECT ss_item_sk         item_sk,\r\n                              Avg(ss_net_profit) rank_col\r\n                       FROM   tpc_ds.store_sales ss1\r\n                       WHERE  ss_store_sk = 4\r\n                       GROUP  BY ss_item_sk\r\n                       HAVING Avg(ss_net_profit) > 0.9 *\r\n                              (SELECT Avg(ss_net_profit)\r\n                                      rank_col\r\n                               FROM   tpc_ds.store_sales\r\n                               WHERE  ss_store_sk = 4\r\n                                      AND ss_cdemo_sk IS\r\n                                          NULL\r\n                               GROUP  BY ss_store_sk))V2)\r\n              V21\r\n       WHERE  rnk < 11) descending,\r\n      tpc_ds.item i1,\r\n      tpc_ds.item i2\r\nWHERE  asceding.rnk = descending.rnk\r\n       AND i1.i_item_sk = asceding.item_sk\r\n       AND i2.i_item_sk = descending.item_sk\r\nORDER  BY asceding.rnk\r\nLIMIT 100;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
\r\n--Query 46\r\nSELECT c_last_name,\r\n               c_first_name,\r\n               ca_city,\r\n               bought_city,\r\n               ss_ticket_number,\r\n               amt,\r\n               profit\r\nFROM   (SELECT ss_ticket_number,\r\n               ss_customer_sk,\r\n               ca_city            bought_city,\r\n               Sum(ss_coupon_amt) amt,\r\n               Sum(ss_net_profit) profit\r\n        FROM   tpc_ds.store_sales,\r\n               tpc_ds.date_dim,\r\n               tpc_ds.store,\r\n               tpc_ds.household_demographics,\r\n               tpc_ds.customer_address\r\n        WHERE  store_sales.ss_sold_date_sk = date_dim.d_date_sk\r\n               AND store_sales.ss_store_sk = store.s_store_sk\r\n               AND store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk\r\n               AND store_sales.ss_addr_sk = customer_address.ca_address_sk\r\n               AND ( household_demographics.hd_dep_count = 6\r\n                      OR household_demographics.hd_vehicle_count = 0 )\r\n               AND date_dim.d_dow IN ( 6, 0 )\r\n               AND date_dim.d_year IN ( 2000, 2000 + 1, 2000 + 2 )\r\n               AND store.s_city IN ( 'Midway', 'Fairview', 'Fairview',\r\n                                     'Fairview',\r\n                                     'Fairview' )\r\n        GROUP  BY ss_ticket_number,\r\n                  ss_customer_sk,\r\n                  ss_addr_sk,\r\n                  ca_city) dn,\r\n       tpc_ds.customer,\r\n       tpc_ds.customer_address current_addr\r\nWHERE  ss_customer_sk = c_customer_sk\r\n       AND customer.c_current_addr_sk = current_addr.ca_address_sk\r\n       AND current_addr.ca_city <> bought_city\r\nORDER  BY c_last_name,\r\n          c_first_name,\r\n          ca_city,\r\n          bought_city,\r\n          ss_ticket_number\r\nLIMIT 100;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
\r\n--Query 47\r\nWITH v1 \r\n     AS (SELECT i_category, \r\n                i_brand, \r\n                s_store_name, \r\n                s_company_name, \r\n                d_year, \r\n                d_moy, \r\n                Sum(ss_sales_price)         sum_sales, \r\n                Avg(Sum(ss_sales_price)) \r\n                  OVER ( \r\n                    partition BY i_category, i_brand, s_store_name, \r\n                  s_company_name, \r\n                  d_year) \r\n                                            avg_monthly_sales, \r\n                Rank() \r\n                  OVER ( \r\n                    partition BY i_category, i_brand, s_store_name, \r\n                  s_company_name \r\n                    ORDER BY d_year, d_moy) rn \r\n         FROM   tpc_ds.item,\r\n                tpc_ds.store_sales,\r\n                tpc_ds.date_dim,\r\n                tpc_ds.store\r\n         WHERE  ss_item_sk = i_item_sk \r\n                AND ss_sold_date_sk = d_date_sk \r\n                AND ss_store_sk = s_store_sk \r\n                AND ( d_year = 1999 \r\n                       OR ( d_year = 1999 - 1 \r\n                            AND d_moy = 12 ) \r\n                       OR ( d_year = 1999 + 1 \r\n                            AND d_moy = 1 ) ) \r\n         GROUP  BY i_category, \r\n                   i_brand, \r\n                   s_store_name, \r\n                   s_company_name, \r\n                   d_year, \r\n                   d_moy), \r\n     v2 \r\n     AS (SELECT v1.i_category, \r\n                v1.d_year, \r\n                v1.d_moy, \r\n                v1.avg_monthly_sales, \r\n                v1.sum_sales, \r\n                v1_lag.sum_sales  psum, \r\n                v1_lead.sum_sales nsum \r\n         FROM   v1, \r\n                v1 v1_lag, \r\n                v1 v1_lead \r\n         WHERE  v1.i_category = v1_lag.i_category \r\n                AND v1.i_category = v1_lead.i_category \r\n                AND v1.i_brand = v1_lag.i_brand \r\n                AND v1.i_brand = v1_lead.i_brand \r\n                AND v1.s_store_name = v1_lag.s_store_name \r\n                AND v1.s_store_name = v1_lead.s_store_name \r\n                AND v1.s_company_name = v1_lag.s_company_name \r\n                AND v1.s_company_name = v1_lead.s_company_name \r\n                AND v1.rn = v1_lag.rn + 1 \r\n                AND v1.rn = v1_lead.rn - 1) \r\nSELECT * \r\nFROM   v2 \r\nWHERE  d_year = 1999 \r\n       AND avg_monthly_sales > 0 \r\n       AND CASE \r\n             WHEN avg_monthly_sales > 0 THEN Abs(sum_sales - avg_monthly_sales) \r\n                                             \/ \r\n                                             avg_monthly_sales \r\n             ELSE NULL \r\n           END > 0.1 \r\nORDER  BY sum_sales - avg_monthly_sales, \r\n          3\r\nLIMIT 100; \r\n<\/pre>\n
\"\"<\/a><\/p>\n
\r\n--Query 54\r\nWITH my_customers\r\n     AS (SELECT DISTINCT c_customer_sk,\r\n                         c_current_addr_sk\r\n         FROM   (SELECT cs_sold_date_sk     sold_date_sk,\r\n                        cs_bill_customer_sk customer_sk,\r\n                        cs_item_sk          item_sk\r\n                 FROM   tpc_ds.catalog_sales\r\n                 UNION ALL\r\n                 SELECT ws_sold_date_sk     sold_date_sk,\r\n                        ws_bill_customer_sk customer_sk,\r\n                        ws_item_sk          item_sk\r\n                 FROM   tpc_ds.web_sales) cs_or_ws_sales,\r\n                tpc_ds.item,\r\n                tpc_ds.date_dim,\r\n                tpc_ds.customer\r\n         WHERE  sold_date_sk = d_date_sk\r\n                AND item_sk = i_item_sk\r\n                AND i_category = 'Sports'\r\n                AND i_class = 'fitness'\r\n                AND c_customer_sk = cs_or_ws_sales.customer_sk\r\n                AND d_moy = 5\r\n                AND d_year = 2000),\r\n     my_revenue\r\n     AS (SELECT c_customer_sk,\r\n                Sum(ss_ext_sales_price) AS revenue\r\n         FROM   my_customers,\r\n                tpc_ds.store_sales,\r\n                tpc_ds.customer_address,\r\n                tpc_ds.store,\r\n                tpc_ds.date_dim\r\n         WHERE  c_current_addr_sk = ca_address_sk\r\n                AND ca_county = s_county\r\n                AND ca_state = s_state\r\n                AND ss_sold_date_sk = d_date_sk\r\n                AND c_customer_sk = ss_customer_sk\r\n                AND d_month_seq BETWEEN (SELECT DISTINCT d_month_seq + 1\r\n                                         FROM   tpc_ds.date_dim\r\n                                         WHERE  d_year = 2000\r\n                                                AND d_moy = 5) AND\r\n                                        (SELECT DISTINCT\r\n                                        d_month_seq + 3\r\n                                         FROM   tpc_ds.date_dim\r\n                                         WHERE  d_year = 2000\r\n                                                AND d_moy = 5)\r\n         GROUP  BY c_customer_sk),\r\n     segments\r\n     AS (SELECT Cast(( revenue \/ 50 ) AS INT) AS segment\r\n         FROM   my_revenue)\r\nSELECT segment,\r\n               Count(*)     AS num_customers,\r\n               segment * 50 AS segment_base\r\nFROM   segments\r\nGROUP  BY segment\r\nORDER  BY segment,\r\n          num_customers\r\nLIMIT 100;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
\r\n--Query 57\r\nWITH v1\r\n     AS (SELECT i_category,\r\n                i_brand,\r\n                cc_name,\r\n                d_year,\r\n                d_moy,\r\n                Sum(cs_sales_price)                                    sum_sales\r\n                ,\r\n                Avg(Sum(cs_sales_price))\r\n                  OVER (\r\n                    partition BY i_category, i_brand, cc_name, d_year)\r\n                avg_monthly_sales\r\n                   ,\r\n                Rank()\r\n                  OVER (\r\n                    partition BY i_category, i_brand, cc_name\r\n                    ORDER BY d_year, d_moy)                            rn\r\n         FROM   tpc_ds.item,\r\n                tpc_ds.catalog_sales,\r\n                tpc_ds.date_dim,\r\n                tpc_ds.call_center\r\n         WHERE  cs_item_sk = i_item_sk\r\n                AND cs_sold_date_sk = d_date_sk\r\n                AND cc_call_center_sk = cs_call_center_sk\r\n                AND ( d_year = 2000\r\n                       OR ( d_year = 2000 - 1\r\n                            AND d_moy = 12 )\r\n                       OR ( d_year = 2000 + 1\r\n                            AND d_moy = 1 ) )\r\n         GROUP  BY i_category,\r\n                   i_brand,\r\n                   cc_name,\r\n                   d_year,\r\n                   d_moy),\r\n     v2\r\n     AS (SELECT v1.i_brand,\r\n                v1.d_year,\r\n                v1.avg_monthly_sales,\r\n                v1.sum_sales,\r\n                v1_lag.sum_sales  psum,\r\n                v1_lead.sum_sales nsum\r\n         FROM   v1,\r\n                v1 v1_lag,\r\n                v1 v1_lead\r\n         WHERE  v1.i_category = v1_lag.i_category\r\n                AND v1.i_category = v1_lead.i_category\r\n                AND v1.i_brand = v1_lag.i_brand\r\n                AND v1.i_brand = v1_lead.i_brand\r\n                AND v1. cc_name = v1_lag. cc_name\r\n                AND v1. cc_name = v1_lead. cc_name\r\n                AND v1.rn = v1_lag.rn + 1\r\n                AND v1.rn = v1_lead.rn - 1)\r\nSELECT *\r\nFROM   v2\r\nWHERE  d_year = 2000\r\n       AND avg_monthly_sales > 0\r\n       AND CASE\r\n             WHEN avg_monthly_sales > 0 THEN Abs(sum_sales - avg_monthly_sales)\r\n                                             \/\r\n                                             avg_monthly_sales\r\n             ELSE NULL\r\n           END > 0.1\r\nORDER  BY sum_sales - avg_monthly_sales,\r\n          3\r\nLIMIT 100; \r\n<\/pre>\n
\"\"<\/a><\/p>\n
\r\n--Query 64\r\nWITH cs_ui \r\n     AS (SELECT cs_item_sk, \r\n                Sum(cs_ext_list_price) AS sale, \r\n                Sum(cr_refunded_cash + cr_reversed_charge \r\n                    + cr_store_credit) AS refund \r\n         FROM   tpc_ds.catalog_sales,\r\n                tpc_ds.catalog_returns\r\n         WHERE  cs_item_sk = cr_item_sk \r\n                AND cs_order_number = cr_order_number \r\n         GROUP  BY cs_item_sk \r\n         HAVING Sum(cs_ext_list_price) > 2 * Sum( \r\n                cr_refunded_cash + cr_reversed_charge \r\n                + cr_store_credit)), \r\n     cross_sales \r\n     AS (SELECT i_product_name         product_name, \r\n                i_item_sk              item_sk, \r\n                s_store_name           store_name, \r\n                s_zip                  store_zip, \r\n                ad1.ca_street_number   b_street_number, \r\n                ad1.ca_street_name     b_streen_name, \r\n                ad1.ca_city            b_city, \r\n                ad1.ca_zip             b_zip, \r\n                ad2.ca_street_number   c_street_number, \r\n                ad2.ca_street_name     c_street_name, \r\n                ad2.ca_city            c_city, \r\n                ad2.ca_zip             c_zip, \r\n                d1.d_year              AS syear, \r\n                d2.d_year              AS fsyear, \r\n                d3.d_year              s2year, \r\n                Count(*)               cnt, \r\n                Sum(ss_wholesale_cost) s1, \r\n                Sum(ss_list_price)     s2, \r\n                Sum(ss_coupon_amt)     s3 \r\n         FROM   tpc_ds.store_sales,\r\n                tpc_ds.store_returns,\r\n                cs_ui, \r\n                tpc_ds.date_dim d1,\r\n                tpc_ds.date_dim d2,\r\n                tpc_ds.date_dim d3,\r\n                tpc_ds.store,\r\n                tpc_ds.customer,\r\n                tpc_ds.customer_demographics cd1,\r\n                tpc_ds.customer_demographics cd2,\r\n                tpc_ds.promotion,\r\n                tpc_ds.household_demographics hd1,\r\n                tpc_ds.household_demographics hd2,\r\n                tpc_ds.customer_address ad1,\r\n                tpc_ds.customer_address ad2,\r\n                tpc_ds.income_band ib1,\r\n                tpc_ds.income_band ib2,\r\n                tpc_ds.item\r\n         WHERE  ss_store_sk = s_store_sk \r\n                AND ss_sold_date_sk = d1.d_date_sk \r\n                AND ss_customer_sk = c_customer_sk \r\n                AND ss_cdemo_sk = cd1.cd_demo_sk \r\n                AND ss_hdemo_sk = hd1.hd_demo_sk \r\n                AND ss_addr_sk = ad1.ca_address_sk \r\n                AND ss_item_sk = i_item_sk \r\n                AND ss_item_sk = sr_item_sk \r\n                AND ss_ticket_number = sr_ticket_number \r\n                AND ss_item_sk = cs_ui.cs_item_sk \r\n                AND c_current_cdemo_sk = cd2.cd_demo_sk \r\n                AND c_current_hdemo_sk = hd2.hd_demo_sk \r\n                AND c_current_addr_sk = ad2.ca_address_sk \r\n                AND c_first_sales_date_sk = d2.d_date_sk \r\n                AND c_first_shipto_date_sk = d3.d_date_sk \r\n                AND ss_promo_sk = p_promo_sk \r\n                AND hd1.hd_income_band_sk = ib1.ib_income_band_sk \r\n                AND hd2.hd_income_band_sk = ib2.ib_income_band_sk \r\n                AND cd1.cd_marital_status <> cd2.cd_marital_status \r\n                AND i_color IN ( 'cyan', 'peach', 'blush', 'frosted', \r\n                                 'powder', 'orange' ) \r\n                AND i_current_price BETWEEN 58 AND 58 + 10 \r\n                AND i_current_price BETWEEN 58 + 1 AND 58 + 15 \r\n         GROUP  BY i_product_name, \r\n                   i_item_sk, \r\n                   s_store_name, \r\n                   s_zip, \r\n                   ad1.ca_street_number, \r\n                   ad1.ca_street_name, \r\n                   ad1.ca_city, \r\n                   ad1.ca_zip, \r\n                   ad2.ca_street_number, \r\n                   ad2.ca_street_name, \r\n                   ad2.ca_city, \r\n                   ad2.ca_zip, \r\n                   d1.d_year, \r\n                   d2.d_year, \r\n                   d3.d_year) \r\nSELECT cs1.product_name, \r\n       cs1.store_name, \r\n       cs1.store_zip, \r\n       cs1.b_street_number, \r\n       cs1.b_streen_name, \r\n       cs1.b_city, \r\n       cs1.b_zip, \r\n       cs1.c_street_number, \r\n       cs1.c_street_name, \r\n       cs1.c_city, \r\n       cs1.c_zip, \r\n       cs1.syear, \r\n       cs1.cnt, \r\n       cs1.s1, \r\n       cs1.s2, \r\n       cs1.s3, \r\n       cs2.s1, \r\n       cs2.s2, \r\n       cs2.s3, \r\n       cs2.syear, \r\n       cs2.cnt \r\nFROM   cross_sales cs1, \r\n       cross_sales cs2 \r\nWHERE  cs1.item_sk = cs2.item_sk \r\n       AND cs1.syear = 2001 \r\n       AND cs2.syear = 2001 + 1 \r\n       AND cs2.cnt <= cs1.cnt \r\n       AND cs1.store_name = cs2.store_name \r\n       AND cs1.store_zip = cs2.store_zip \r\nORDER  BY cs1.product_name, \r\n          cs1.store_name, \r\n          cs2.cnt; \t\r\n<\/pre>\n
\"\"<\/a><\/p>\n
\r\n--Query 74\r\nWITH year_total \r\n     AS (SELECT c_customer_id    customer_id, \r\n                c_first_name     customer_first_name, \r\n                c_last_name      customer_last_name, \r\n                d_year           AS year1, \r\n                Sum(ss_net_paid) year_total, \r\n                's'              sale_type \r\n         FROM   tpc_ds.customer,\r\n                tpc_ds.store_sales,\r\n                tpc_ds.date_dim\r\n         WHERE  c_customer_sk = ss_customer_sk \r\n                AND ss_sold_date_sk = d_date_sk \r\n                AND d_year IN ( 1999, 1999 + 1 ) \r\n         GROUP  BY c_customer_id, \r\n                   c_first_name, \r\n                   c_last_name, \r\n                   d_year \r\n         UNION ALL \r\n         SELECT c_customer_id    customer_id, \r\n                c_first_name     customer_first_name, \r\n                c_last_name      customer_last_name, \r\n                d_year           AS year1, \r\n                Sum(ws_net_paid) year_total, \r\n                'w'              sale_type \r\n         FROM   tpc_ds.customer,\r\n                tpc_ds.web_sales,\r\n                tpc_ds.date_dim\r\n         WHERE  c_customer_sk = ws_bill_customer_sk \r\n                AND ws_sold_date_sk = d_date_sk \r\n                AND d_year IN ( 1999, 1999 + 1 ) \r\n         GROUP  BY c_customer_id, \r\n                   c_first_name, \r\n                   c_last_name, \r\n                   d_year) \r\nSELECT t_s_secyear.customer_id, \r\n               t_s_secyear.customer_first_name, \r\n               t_s_secyear.customer_last_name \r\nFROM   year_total t_s_firstyear, \r\n       year_total t_s_secyear, \r\n       year_total t_w_firstyear, \r\n       year_total t_w_secyear \r\nWHERE  t_s_secyear.customer_id = t_s_firstyear.customer_id \r\n       AND t_s_firstyear.customer_id = t_w_secyear.customer_id \r\n       AND t_s_firstyear.customer_id = t_w_firstyear.customer_id \r\n       AND t_s_firstyear.sale_type = 's' \r\n       AND t_w_firstyear.sale_type = 'w' \r\n       AND t_s_secyear.sale_type = 's' \r\n       AND t_w_secyear.sale_type = 'w' \r\n       AND t_s_firstyear.year1 = 1999 \r\n       AND t_s_secyear.year1 = 1999 + 1 \r\n       AND t_w_firstyear.year1 = 1999 \r\n       AND t_w_secyear.year1 = 1999 + 1 \r\n       AND t_s_firstyear.year_total > 0 \r\n       AND t_w_firstyear.year_total > 0 \r\n       AND CASE \r\n             WHEN t_w_firstyear.year_total > 0 THEN t_w_secyear.year_total \/ \r\n                                                    t_w_firstyear.year_total \r\n             ELSE NULL \r\n           END > CASE \r\n                   WHEN t_s_firstyear.year_total > 0 THEN \r\n                   t_s_secyear.year_total \/ \r\n                   t_s_firstyear.year_total \r\n                   ELSE NULL \r\n                 END \r\nORDER  BY 1, \r\n          2, \r\n          3\r\nLIMIT 100; \t\t\r\n<\/pre>\n
\"\"<\/a><\/p>\n
\r\n--Query 75\r\nWITH all_sales\r\n     AS (SELECT d_year,\r\n                i_brand_id,\r\n                i_class_id,\r\n                i_category_id,\r\n                i_manufact_id,\r\n                Sum(sales_cnt) AS sales_cnt,\r\n                Sum(sales_amt) AS sales_amt\r\n         FROM   (SELECT d_year,\r\n                        i_brand_id,\r\n                        i_class_id,\r\n                        i_category_id,\r\n                        i_manufact_id,\r\n                        cs_quantity - COALESCE(cr_return_quantity, 0)        AS\r\n                        sales_cnt,\r\n                        cs_ext_sales_price - COALESCE(cr_return_amount, 0.0) AS\r\n                        sales_amt\r\n                 FROM   tpc_ds.catalog_sales\r\n                        JOIN tpc_ds.item\r\n                          ON i_item_sk = cs_item_sk\r\n                        JOIN tpc_ds.date_dim\r\n                          ON d_date_sk = cs_sold_date_sk\r\n                        LEFT JOIN tpc_ds.catalog_returns\r\n                               ON ( cs_order_number = cr_order_number\r\n                                    AND cs_item_sk = cr_item_sk )\r\n                 WHERE  i_category = 'Men'\r\n                 UNION\r\n                 SELECT d_year,\r\n                        i_brand_id,\r\n                        i_class_id,\r\n                        i_category_id,\r\n                        i_manufact_id,\r\n                        ss_quantity - COALESCE(sr_return_quantity, 0)     AS\r\n                        sales_cnt,\r\n                        ss_ext_sales_price - COALESCE(sr_return_amt, 0.0) AS\r\n                        sales_amt\r\n                 FROM   tpc_ds.store_sales\r\n                        JOIN tpc_ds.item\r\n                          ON i_item_sk = ss_item_sk\r\n                        JOIN tpc_ds.date_dim\r\n                          ON d_date_sk = ss_sold_date_sk\r\n                        LEFT JOIN tpc_ds.store_returns\r\n                               ON ( ss_ticket_number = sr_ticket_number\r\n                                    AND ss_item_sk = sr_item_sk )\r\n                 WHERE  i_category = 'Men'\r\n                 UNION\r\n                 SELECT d_year,\r\n                        i_brand_id,\r\n                        i_class_id,\r\n                        i_category_id,\r\n                        i_manufact_id,\r\n                        ws_quantity - COALESCE(wr_return_quantity, 0)     AS\r\n                        sales_cnt,\r\n                        ws_ext_sales_price - COALESCE(wr_return_amt, 0.0) AS\r\n                        sales_amt\r\n                 FROM   tpc_ds.web_sales\r\n                        JOIN tpc_ds.item\r\n                          ON i_item_sk = ws_item_sk\r\n                        JOIN tpc_ds.date_dim\r\n                          ON d_date_sk = ws_sold_date_sk\r\n                        LEFT JOIN tpc_ds.web_returns\r\n                               ON ( ws_order_number = wr_order_number\r\n                                    AND ws_item_sk = wr_item_sk )\r\n                 WHERE  i_category = 'Men') sales_detail\r\n         GROUP  BY d_year,\r\n                   i_brand_id,\r\n                   i_class_id,\r\n                   i_category_id,\r\n                   i_manufact_id)\r\nSELECT prev_yr.d_year                        AS prev_year,\r\n               curr_yr.d_year                        AS year1,\r\n               curr_yr.i_brand_id,\r\n               curr_yr.i_class_id,\r\n               curr_yr.i_category_id,\r\n               curr_yr.i_manufact_id,\r\n               prev_yr.sales_cnt                     AS prev_yr_cnt,\r\n               curr_yr.sales_cnt                     AS curr_yr_cnt,\r\n               curr_yr.sales_cnt - prev_yr.sales_cnt AS sales_cnt_diff,\r\n               curr_yr.sales_amt - prev_yr.sales_amt AS sales_amt_diff\r\nFROM   all_sales curr_yr,\r\n       all_sales prev_yr\r\nWHERE  curr_yr.i_brand_id = prev_yr.i_brand_id\r\n       AND curr_yr.i_class_id = prev_yr.i_class_id\r\n       AND curr_yr.i_category_id = prev_yr.i_category_id\r\n       AND curr_yr.i_manufact_id = prev_yr.i_manufact_id\r\n       AND curr_yr.d_year = 2002\r\n       AND prev_yr.d_year = 2002 - 1\r\n       AND Cast(curr_yr.sales_cnt AS DECIMAL(17, 2)) \/ Cast(prev_yr.sales_cnt AS\r\n                                                                DECIMAL(17, 2))\r\n           < 0.9\r\nORDER  BY sales_cnt_diff\r\nLIMIT 100; \r\n<\/pre>\n
\"\"<\/a><\/p>\n
\r\n--Query 97\r\nWITH ssci\r\n     AS (SELECT ss_customer_sk customer_sk,\r\n                ss_item_sk     item_sk\r\n         FROM   tpc_ds.store_sales,\r\n                tpc_ds.date_dim\r\n         WHERE  ss_sold_date_sk = d_date_sk\r\n                AND d_month_seq BETWEEN 1196 AND 1196 + 11\r\n         GROUP  BY ss_customer_sk,\r\n                   ss_item_sk),\r\n     csci\r\n     AS (SELECT cs_bill_customer_sk customer_sk,\r\n                cs_item_sk          item_sk\r\n         FROM   tpc_ds.catalog_sales,\r\n                tpc_ds.date_dim\r\n         WHERE  cs_sold_date_sk = d_date_sk\r\n                AND d_month_seq BETWEEN 1196 AND 1196 + 11\r\n         GROUP  BY cs_bill_customer_sk,\r\n                   cs_item_sk)\r\nSELECT Sum(CASE\r\n                     WHEN ssci.customer_sk IS NOT NULL\r\n                          AND csci.customer_sk IS NULL THEN 1\r\n                     ELSE 0\r\n                   END) store_only,\r\n               Sum(CASE\r\n                     WHEN ssci.customer_sk IS NULL\r\n                          AND csci.customer_sk IS NOT NULL THEN 1\r\n                     ELSE 0\r\n                   END) catalog_only,\r\n               Sum(CASE\r\n                     WHEN ssci.customer_sk IS NOT NULL\r\n                          AND csci.customer_sk IS NOT NULL THEN 1\r\n                     ELSE 0\r\n                   END) store_and_catalog\r\nFROM   ssci\r\n       FULL OUTER JOIN csci\r\n                    ON ( ssci.customer_sk = csci.customer_sk\r\n                         AND ssci.item_sk = csci.item_sk )\r\nLIMIT 100;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
\r\n--Query 99\r\nSELECT Substr(w_warehouse_name, 1, 20), \r\n               sm_type, \r\n               cc_name, \r\n               Sum(CASE \r\n                     WHEN ( cs_ship_date_sk - cs_sold_date_sk <= 30 ) THEN 1 \r\n                     ELSE 0 \r\n                   END) AS '30 days',\r\n               Sum(CASE \r\n                     WHEN ( cs_ship_date_sk - cs_sold_date_sk > 30 ) \r\n                          AND ( cs_ship_date_sk - cs_sold_date_sk <= 60 ) THEN 1 \r\n                     ELSE 0 \r\n                   END) AS '31-60 days',\r\n               Sum(CASE \r\n                     WHEN ( cs_ship_date_sk - cs_sold_date_sk > 60 ) \r\n                          AND ( cs_ship_date_sk - cs_sold_date_sk <= 90 ) THEN 1 \r\n                     ELSE 0 \r\n                   END) AS '61-90 days',\r\n               Sum(CASE \r\n                     WHEN ( cs_ship_date_sk - cs_sold_date_sk > 90 ) \r\n                          AND ( cs_ship_date_sk - cs_sold_date_sk <= 120 ) THEN \r\n                     1 \r\n                     ELSE 0 \r\n                   END) AS '91-120 days',\r\n               Sum(CASE \r\n                     WHEN ( cs_ship_date_sk - cs_sold_date_sk > 120 ) THEN 1 \r\n                     ELSE 0 \r\n                   END) AS '>120 days'\r\nFROM   tpc_ds.catalog_sales,\r\n       tpc_ds.warehouse,\r\n       tpc_ds.ship_mode,\r\n       tpc_ds.call_center,\r\n       tpc_ds.date_dim\r\nWHERE  d_month_seq BETWEEN 1200 AND 1200 + 11 \r\n       AND cs_ship_date_sk = d_date_sk \r\n       AND cs_warehouse_sk = w_warehouse_sk \r\n       AND cs_ship_mode_sk = sm_ship_mode_sk \r\n       AND cs_call_center_sk = cc_call_center_sk \r\nGROUP  BY Substr(w_warehouse_name, 1, 20), \r\n          sm_type, \r\n          cc_name \r\nORDER  BY Substr(w_warehouse_name, 1, 20), \r\n          sm_type, \r\n          cc_name\r\nLIMIT 100; \r\n<\/pre>\n
I didn\u2019t set out on this experiment to compare Vertica\u2019s performance to other vendors in the MPP category, so I cannot comment on how it could verse against other RDBMS systems. It would have been interesting to see how, for example, Pivotal\u2019s Greenplum would match up when testing it out on the identical hardware and data volumes but this 4-part series was primarily focused on establishing the connection between the two variables: the number of nodes and data size. From that perspective, Vertica performs very well ‘out-of-the-box’, and without any tuning or tweaking it managed to not only execute the queries relatively fast (given the mediocre hardware specs) but also take full advantage of the extra computing resources to distribute the load across the available nodes and cut queries execution times.<\/p>\n
Looking at the results and how they present the dichotomies across the multitude of different configurations, the first thing that jumps out is the fact the differences between performance levels across various data volumes and node counts are very linear. On an average, the query execution speed doubles for every scaling factor increase and goes down by a factor of two for every node that\u2019s removed. There are some slight variations but on the whole Vertica performance is consistent and predictable, and all twenty queries exhibiting similar pattern when dealing with more data and\/or more computing power thrown at it (additional nodes).<\/p>\n
Looking at the side-by-side comparison of the execution results across single and multi-node configurations the differences between across scaling factors of 100, 200 and 300 are very consistent (click on image to expand).<\/p>\n
\"\"<\/a><\/p>\n
Converting these differences (single vs multi-node deployment) into percentage yielded an average of 243% increase for the scaling factor of 100, 252% increase for the scaling factor of 200 and 217% increase for the scaling factor of 300 as per the chart below (click on image to expand).<\/p>\n
\"\"<\/a><\/p>\n
Finally, let’s look at how Vertica (three-node cluster with 100 scaling factor TPC-DS data) performed against some ad hoc generated queries in PowerBI and Tableau applications. Below are two short video clips depicting a rudimentary analysis of store sales data (this fact table contains close to 300 million records) against date, item, store and customer_address dimensions. It’s the type of rapid-fire, exploratory analysis one would conduct to analyse retail data to answer some immediate questions. I also set up Tableau and PowerBI side-by-side a process viewer\/system monitor (htop), with four panes displaying core performance metrics e.g. CPU load, swap, memory status etc. across the three nodes and the machine hosting visualisation application (bottom view) so that I could observer (and record) systems’ behaviour when interactively aggregating data. In this way it was easy to see load distribution and how each host reacted (performance-wise) to queries issued based on this analysis. With respect to PowerBI, you can see that my three years old work laptop I recorded this on was not up to the task due to large amount of CPU cycles consumed by screen-recording software alone. On the flip side, Tableau run on my dual-CPU Mac Pro so the whole process was stutter-free and could reflect the fact that given access to a better hardware, PowerBI may also perform better.<\/p>\n
I used Tableau Desktop version 2018.1 with native support for Vertica. As opposed to PowerBI, Tableau had no issues reading data from ‘customer’ dimension but in order to make those two footages as comparable as possible I abandoned it and used ‘customer_address’ instead. In terms of raw performance, this mini-cluster actually turned out to be borderline usable and in most cases I didn’t need to wait more than 10 seconds for aggregation or filtering to complete. Each node CPU spiked close to 100 percent during queries execution but given the volume of data and the mediocre hardware this data was crunched on, I would say that Vertica performed quite well.<\/p>\n