Group By Clause taking too long.

From: kdabda (anonymous_at_discussions.microsoft.com)
Date: 04/06/04

  • Next message: anonymous_at_discussions.microsoft.com: "Re: Number to Date." 
    Date: Tue, 6 Apr 2004 13:36:03 -0700

    Below is a view that I use inside a stored procedure. I need to find the most recent record according to my group by clause. Unfortunately it takes way too long to run. Any ideas on how to make a group by query faster?

    SELECT TOP 100 PERCENT dbo.cp_sku_qty.sku_retail_id, dbo.cp_sku_qty_retail_price.store_number, MAX(dbo.cp_sku_qty_retail_price.effective_timestamp) AS MaxOfeffective_timestamp, dbo.cp_sku_qty.delete_date,bo.cp_sku_qty.combined_pos_description, dbo.cp_sku.sku_description, dbo.cp_sku.inventory_category
    FROM dbo.cp_sku_qty
    LEFT OUTER JOIN dbo.cp_sku_qty_to_upc_plu_xref ON dbo.cp_sku_qty.sku_retail_id = dbo.cp_sku_qty_to_upc_plu_xref.sku_retail_id
    LEFT OUTER JOIN dbo.cp_sku_qty_retail_price ON dbo.cp_sku_qty.sku_retail_id = dbo.cp_sku_qty_retail_price.sku_retail_id LEFT OUTER JOIN dbo.cp_sku ON dbo.cp_sku_qty.sku_number = dbo.cp_sku.sku_number
    WHERE (dbo.cp_sku_qty_retail_price.store_number <> - 1) AND
     (dbo.cp_sku_qty_retail_price.effective_timestamp < { fn NOW() } + 9) OR
                          (dbo.cp_sku_qty_retail_price.effective_timestamp IS NULL)
    GROUP BY dbo.cp_sku_qty.sku_retail_id, dbo.cp_sku_qty_retail_price.store_number, dbo.cp_sku_qty.delete_date, dbo.cp_sku_qty.combined_pos_description, dbo.cp_sku.sku_description, dbo.cp_sku.inventory_category
    HAVING (dbo.cp_sku_qty.delete_date > { fn NOW() } OR
                          dbo.cp_sku_qty.delete_date IS NULL) AND (NOT (dbo.cp_sku.sku_description LIKE '%SHIPPER%'))

  • Next message: anonymous_at_discussions.microsoft.com: "Re: Number to Date."