Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21435

Select statement with several inner joins has really poor performance

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.3.15
    • Fix Version/s: None
    • Component/s: None
    • Labels:
    • Environment:
      RHEL7 ES 64bit

      Description

      WITH
      `Retailers` AS (
      SELECT `SALES_REGION`.`SALES_REGION_CODE` AS `Sales_region_code`, `RETAILER_SITE`.`RETAILER_SITE_CODE` AS `Retailer_site_code`
      FROM `gosalesrt_1021`.`retailer_contact` `RETAILER_CONTACT`
      INNER JOIN `gosaleshr_1021`.`gender_lookup` `GENDER_LOOKUP` ON `RETAILER_CONTACT`.`GENDER_CODE` = `GENDER_LOOKUP`.`GENDER_CODE`
      INNER JOIN `gosalesrt_1021`.`retailer_site_mb` `RETAILER_SITE_MB` ON `RETAILER_CONTACT`.`RETAILER_SITE_CODE` = `RETAILER_SITE_MB`.`RETAILER_SITE_CODE`
      INNER JOIN `gosalesrt_1021`.`retailer_site` `RETAILER_SITE` ON `RETAILER_SITE`.`RETAILER_SITE_CODE` = `RETAILER_CONTACT`.`RETAILER_SITE_CODE`
      INNER JOIN `gosales_1021`.`country` `COUNTRY` ON `RETAILER_SITE`.`RTL_COUNTRY_CODE` = `COUNTRY`.`COUNTRY_CODE`
      INNER JOIN `gosalesrt_1021`.`retailer` `RETAILER` ON `RETAILER`.`RETAILER_CODE` = `RETAILER_SITE_MB`.`RETAILER_CODE`
      INNER JOIN `gosales_1021`.`sales_region` `SALES_REGION` ON `COUNTRY`.`SALES_REGION_CODE` = `SALES_REGION`.`SALES_REGION_CODE`
      ),

      `Sales` AS (
      SELECT (EXTRACT(YEAR FROM `ORDER_HEADER`.`ORDER_DATE`) * 10000 + EXTRACT(MONTH
      FROM `ORDER_HEADER`.`ORDER_DATE`) * 100) + EXTRACT(DAY FROM `ORDER_HEADER`.`ORDER_DATE`) AS `Day_key_order_date`,
      `ORDER_HEADER`.`RETAILER_SITE_CODE` AS `Retailer_site_code`, `ORDER_HEADER`.`ORDER_DATE` AS `Order_date`,
      `ORDER_DETAILS`.`QUANTITY` AS `Quantity`, `ORDER_DETAILS`.`UNIT_COST` AS `Unit_cost`, `ORDER_DETAILS`.`UNIT_SALE_PRICE` AS `Unit_sale_price`,
      `ORDER_DETAILS`.`QUANTITY` * `ORDER_DETAILS`.`UNIT_SALE_PRICE` AS `Revenue`,
      `ORDER_DETAILS`.`QUANTITY` * `ORDER_DETAILS`.`UNIT_SALE_PRICE` - `ORDER_DETAILS`.`QUANTITY` * `ORDER_DETAILS`.`UNIT_COST` AS `Gross_profit`
      FROM `gosales_1021`.`order_header` `ORDER_HEADER`
      INNER JOIN `gosales_1021`.`order_details` `ORDER_DETAILS` ON `ORDER_HEADER`.`ORDER_NUMBER` = `ORDER_DETAILS`.`ORDER_NUMBER`
      )

      SELECT `TIME_DIMENSION`.`CURRENT_YEAR`, `TIME_DIMENSION`.`QUARTER_KEY`, `Sales`.`Gross_profit`, `Sales`.`Revenue`
      FROM `Retailers`
      INNER JOIN `Sales` ON `Retailers`.`Retailer_site_code` = `Sales`.`Retailer_site_code`
      INNER JOIN `gosales_1021`.`time_dimension` `TIME_DIMENSION` ON `Sales`.`Day_key_order_date` = `TIME_DIMENSION`.`DAY_KEY`
      WHERE MOD(`TIME_DIMENSION`.`MONTH_KEY`, 100) <> 0 AND `Retailers`.`Sales_region_code` = 710

      Testing above sql statement, it takes 18 minutes in my environment against 10.3.15. But when testing against 10.3.18, it takes a second.

      I have attached the 7zip compressed file for ddl

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            Mike.Savoie@ca.ibm.com Mike Savoie
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: