Re: Got timeout writing communication packets
Posted by: Peter Brawley
Date: August 06, 2021 09:12AM

Formatting ths big query for human readability ...

SELECT ytd.productcode,ytd.currency,ytd.totalorders,ytd.totalvisits,ytd.totalproductviews,ytd.totalcartadds,ytd.totalunits,ytd.customscore1,ytd.customscore2,ytd.customscore3,14day.totalorders,14day.totalvisits,14day.totalproductviews,14day.totalcartadds,14day.totalunits,14day.customscore1,14day.customscore2,14day.customscore3,7day.totalorders,7day.totalvisits,7day.totalproductviews,7day.totalcartadds,7day.totalunits,7day.customscore1,7day.customscore2,7day.customscore3,1day.totalorders,1day.totalvisits,1day.totalproductviews,1day.totalcartadds,1day.totalunits,1day.customscore1,1day.customscore2,1day.customscore3
FROM (
  SELECT 
    item_t0.p_productcode as productcode, item_t0.p_currency as currency, 
    SUM( item_t0.p_totalorders ) totalorders, 
    SUM( item_t0.p_totalvisits ) totalvisits, 
    SUM( item_t0.p_totalproductviews ) totalproductviews, 
    SUM( item_t0.p_totalcartadds ) totalcartadds, 
    SUM( item_t0.p_totalunits ) totalunits, SUM( item_t0.p_customscore1 ) customscore1, SUM( item_t0.p_customscore2 ) customscore2, 
    SUM( item_t0.p_customscore3 ) customscore3
  FROM productanalytics item_t0 
  WHERE ( item_t0.p_datetime >= '2021-01-01 00:00:00' AND item_t0.p_datetime <= '2021-07-28 23:59:59') 
    AND (item_t0.TypePkString=8796126871634 ) 
  GROUP BY item_t0.p_productcode , item_t0.p_currency
) ytd
LEFT JOIN (
  SELECT 
    item_t1.p_productcode as productcode, item_t1.p_currency as currency, 
    SUM( item_t1.p_totalorders ) totalorders, 
    SUM( item_t1.p_totalvisits ) totalvisits, 
    SUM( item_t1.p_totalproductviews ) totalproductviews, SUM( item_t1.p_totalcartadds ) totalcartadds, SUM( item_t1.p_totalunits ) totalunits, 
    SUM( item_t1.p_customscore1 ) customscore1, 
    SUM( item_t1.p_customscore2 ) customscore2, SUM( item_t1.p_customscore3 ) customscore3
  FROM productanalytics item_t1
  WHERE ( item_t1.p_datetime >= '2021-07-15 00:00:00' AND item_t1.p_datetime <= '2021-07-28 23:59:59') 
    AND (item_t1.TypePkString=8796126871634 ) 
  GROUP BY item_t1.p_productcode , item_t1.p_currency 
) 14day ON ytd.productcode=14day.productcode AND ytd.currency=14day.currency
LEFT JOIN (
  SELECT 
    item_t2.p_productcode as productcode, 
    item_t2.p_currency as currency, 
    SUM( item_t2.p_totalorders ) totalorders, 
    SUM( item_t2.p_totalvisits ) totalvisits, 
    SUM( item_t2.p_totalproductviews ) totalproductviews, 
    SUM( item_t2.p_totalcartadds ) totalcartadds, 
    SUM( item_t2.p_totalunits ) totalunits, 
    SUM( item_t2.p_customscore1 ) customscore1, 
    SUM( item_t2.p_customscore2 ) customscore2, SUM( item_t2.p_customscore3 ) customscore3
  FROM productanalytics item_t2
  WHERE ( item_t2.p_datetime >= '2021-07-22 00:00:00' AND item_t2.p_datetime <= '2021-07-28 23:59:59') 
    AND (item_t2.TypePkString=8796126871634 ) GROUP BY item_t2.p_productcode , item_t2.p_currency
) 7day ON ytd.productcode=7day.productcode AND ytd.currency=7day.currency
LEFT JOIN (
  SELECT 
    item_t3.p_productcode as productcode, item_t3.p_currency as currency, 
    SUM( item_t3.p_totalorders ) totalorders, 
    SUM( item_t3.p_totalvisits ) totalvisits, 
    SUM( item_t3.p_totalproductviews ) totalproductviews, 
    SUM( item_t3.p_totalcartadds ) totalcartadds, 
    SUM( item_t3.p_totalunits ) totalunits, 
    SUM( item_t3.p_customscore1 ) customscore1, 
    SUM( item_t3.p_customscore2 ) customscore2, SUM( item_t3.p_customscore3 ) customscore3
  FROM productanalytics item_t3
  WHERE ( item_t3.p_datetime >= '2021-07-28 00:00:00' AND item_t3.p_datetime <= '2021-07-28 23:59:59') 
    AND (item_t3.TypePkString=8796126871634 ) 
  GROUP BY item_t3.p_productcode , item_t3.p_currency
) 1day ON ytd.productcode=1day.productcode AND ytd.currency=1day.currency\G

The table on the left side of a Left Join has to be read raw. There's the bottleneck---this query needs to read 62.9 million rows without benefit of an index, that's a performance killer ...

id: 1
...
possible_keys: NULL
key: NULL
...
rows: 62855913

If you can lose the Left Joins in favour of Inner Joins, you can help this query with covering indexes.

And if you upgrade to 8.0, I think you can restructure this query toward much better efficiency using CTEs.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.