'SQL Left Join with Where Max Date is too slow

I am creating a query on azure databricks that left joins the table of products with the table of stock.

The problem is that the stock table is a huge table, daily updated, and if I try to use the following query to get the latest data with MAX TRANSACTION_DATE, the query can take anywhere 15-20 minutes.

   LEFT JOIN
      (
         SELECT
            st.SKU_CODE,
            sum(st.UNITS_STOCK_ACTUAL) UNITS_STOCK_ACTUAL,
            sum(st.UNITS_AVAILABLE_STOCK) UNITS_AVAILABLE_STOCK 
         FROM
            db.daily_stock st 
         WHERE
            st.TRANSACTION_DATE=
            (
               SELECT
                  max(st.TRANSACTION_DATE) 
               FROM
                  db.daily_stock st 
            )
         GROUP BY
            st.SKU_CODE 
      )
      st
      ON st.SKU_CODE = SKU_CODE

Where if I use st.TRANSACTION_DATE= "2022-02-28" the query runs almost instantly.

Is there any way to optimize that where max query to run faster?

sqldatabasedatabricks


Solution 1:[1]

I was able to solve that by using the NOW() function.

st.TRANS_DATE = DATE(NOW())

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Andreas P.

Related Questions