How to speed up multiple join query

Answers

• Options

  Navin_C Vertica Customer ✭

  August 2020

  Based on the query and plan (along with statistics)
  Some generic optimization rules .
  1. Always reduce the data before any joins as much possible.
  2. When joining, make sure smaller tables are on the left side of join syntax, which makes this data set to be in memory / broadcasted to all the vertica nodes and makes join faster.
  3. Join on INT columns, preferred over any other types, it makes it faster.
  4. Select on the columns which are really needed. -- Reduce materializing of columns
  5. Make sure stats are updated on the table.

  In this case, one of your joins is assuming the huge table in the INNER part of join, which can ask for more memory and reduce the execution speed.
  -- Outer -> JOIN MERGEJOIN(inputs presorted) [LeftOuter] [Cost: 3M, Rows: 44M] (PATH ID: 3)
  | | | Join Cond: (a.product_id = c.product_id)
  | | | +-- Outer -> STORAGE ACCESS for a [Cost: 460K, Rows: 44M] (PATH ID: 4)
  | | | | Projection: newbiz_product_dev.products_super
  | | | | Materialize: a.product_id
  | | | +-- Inner -> STORAGE ACCESS for c [Cost: 2M, Rows: 44M] (PATH ID: 5)
  | | | | Projection: newbiz_product_dev.ws_product_brand_map_super
  | | | | Materialize: c.product_id, c.mall_id, c.pipe_id, c.brand_id, c.domain_brand, c.brand, c.brand_status, c.brand_ctrl_disp_yn, c.reg_user, c.reg_dt, c.mod_user, c.mod_dt

  0
• Options

  HyeontaeJu Vertica Customer ✭

  August 2020

  @Navin_C Thank you very much,,, all table's data is very big,,
  products : 40,000,000
  ws_product_brand_map : 40,000,000
  ws_product_category_map : 40,000,000
  ws_category = 4,000
  so,, In this case How can i reduce the response time ??

  0
• Options

  Sankarmn Community Edition User ✭✭

  August 2020

  @HyeontaeJu , Optimal performance depends in great part on the projections that are available for a given query. Check best practices for joins, https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/Optimizations/OptimizingJOINQueries.htm?tocpath=Analyzing Data|Query Optimization|JOIN Queries|_____0

  1
• Options

  marcothesane - Select Field - Administrator

  August 2020

  You have a great deal of MERGEJOIN operators - this is very good and can't be beaten.

  None of the JOIN operators show RESEGMENT or BROADCAST or ROUND-ROBIN, so you have segmented your table's data well.

  But:
  What are you running your query with?
  According to what the statistics say, you are fetching 44 million rows from Vertica to the client.

  To avoid the data fetching process, just try:

  CREATE LOCAL TEMPORARY TABLE dropme
  ON COMMIT PRESERVE ROWS AS
  -- your full query
  SELECT
  *
  FROM
  products a
  LEFT OUTER JOIN products_image b ON
  a.product_id = b.product_id
  LEFT OUTER JOIN ws_product_brand_map c ON
  a.product_id = c.product_id
  LEFT OUTER JOIN ws_product_category_map d ON
  a.product_id = d.product_id
  LEFT OUTER JOIN ws_category e ON
  d.cate_mst_cd = e.master_code
  -- your full query ends here
  KSAFE 0
  ;
  

  .. and measure the time when the data does not have to be prepared for sending to client, and the client has no data to fetch.

  You seriously risk to test the network speed rather than the database.

  0