Saya mengalami hal yang aneh di mana menjalankan kueri saya membutuhkan waktu satu detik, lalu menekan f5 lagi tanpa mengubah satu hal pun sekarang membuat kueri yang sama persis membutuhkan waktu 10 detik. Dan itu akan terus bergantian. Lari pertama instan, lari berikutnya lambat, lari berikutnya instan, dan seterusnya.

Saya pikir ini mungkin rencana kueri jadi saya ingin menambahkan OPSI (REKOMPILASI) ke kueri saya, tetapi saya tidak yakin harus meletakkannya di mana. Apakah saya meletakkannya di tempat yang tepat? Kueri ini pada akhirnya akan menjadi prosedur tersimpan, saya hanya melakukan hal-hal seperti ini untuk saat ini karena lebih mudah untuk diuji.

DECLARE @LocalCompanyCode VARCHAR(5)
SET @LocalCompanyCode = '03'

DECLARE @LocalDivisionCode VARCHAR(5)
SET @LocalDivisionCode = '001'

DECLARE @LocalFromDate DATETIME
SET @LocalFromDate = '1/1/2018'

DECLARE @LocalToDate DATETIME
SET @LocalToDate = '9/1/2019'

DECLARE @LocalDetailLevel VARCHAR(50)
SET @LocalDetailLevel = 'color'

DECLARE @LocalOrderBy VARCHAR(50)
SET @LocalOrderBy = 'ordered'


IF @LocalDetailLevel = 'master'
BEGIN

    SELECT * FROM (
        SELECT DISTINCT

        Master_Item
        ,'--' Item_Number
        ,'--' Color_Code 
        ,Description
        ,'--' Color_Description

        ,SUM(Unit_Retail) Sum_Unit_Retail
        ,AVG(Unit_Retail)  Avg_Unit_Retail

        ,SUM(Unit_MarkDown)  Sum_Unit_MarkDown
        ,AVG(Unit_MarkDown)  Avg_Unit_MarkDown
        ,AVG(Unit_MarkDown_Percent)  Avg_Unit_MarkDown_Percent

        ,SUM(Sell_Price)  Sum_Sell_Price
        ,AVG(Sell_Price)  Avg_Sell_Price

        ,SUM(Discount_Value)  Sum_Discount_Value
        ,AVG(Discount_Value)  Avg_Discount_Value
        ,AVG(Unit_Discount_Value_Percent)  Avg_Discount_Value_Percent

        ,SUM(Sale_Price)  Sum_Sale_Price
        ,AVG(Sale_Price)  Avg_Sale_Price

        ,SUM(Royalty_Cost)  Sum_Royalty_Cost
        ,AVG(Royalty_Cost)  Avg_Royalty_Cost
        ,AVG(Unit_Royalty_Cost_Percent)  Avg_Royalty_Cost_Percent

        ,SUM(Item_Cost)  Sum_Item_Cost
        ,AVG(Item_Cost)  Avg_Item_Cost
        ,AVG(Unit_Item_Cost_Percent)  Avg_Item_Cost_Percent

        ,SUM(Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight)  Sum_Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight
        ,AVG(Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight)  Avg_Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight
        ,AVG(Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight_Percent)  Avg_Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight_Percent

        ,MAX(Quantity_Invoiced+Quantity_Allocated)  Max_Quantity

        ,SUM(Quantity_Invoiced+Quantity_Allocated)  Total_Units

        ,COUNT(DISTINCT Customer_Purchase_Order_Number ) Total_Orders_Cont
        ,-1 Percent_Of_Orders_Cont

        ,SUM(Quantity_Invoiced+Quantity_Allocated) / COUNT(DISTINCT Customer_Purchase_Order_Number) Average_Order_Quantity

        ,SUM(Quantity_Returned)  Total_Units_Returned
        ,MAX(Quantity_Returned)  Max_Quantity_Returned
        ,SUM(Quantity_Returned)  
        /NULLIF(SUM(Quantity_Invoiced+Quantity_Allocated) ,0) Return_Percentage

        ,SUM(CASE WHEN F.Line_Status = 'CANCELLED' THEN Quantity_Ordered ELSE 0 END)  Cancelled_Count
        ,SUM(CASE WHEN F.Line_Status = 'CANCELLED' THEN Quantity_Ordered ELSE 0 END)  
        / NULLIF(SUM(Quantity_Ordered) ,0) Cancelled_Count_Percent

        ,SUM(CASE WHEN (Tags not like '%customerrequested_cancel%' and Tags not like '%ia_cancel%' and (FulfillmentState like 'partial%' or FinancialState like 'partial%')) THEN Short_Shipped ELSE 0 END)  Short_Shipped_Count
        ,SUM(CASE WHEN (Tags not like '%customerrequested_cancel%' and Tags not like '%ia_cancel%' and (FulfillmentState like 'partial%' or FinancialState like 'partial%')) THEN Short_Shipped ELSE 0 END)  
        / NULLIF(SUM(Quantity_Ordered) ,0) Short_Shipped_Count_Percent

    FROM 
        FinalEcomTable F

    WHERE
        1=1
        AND (F.Company_Code = @LocalCompanyCode OR @LocalCompanyCode IS NULL)  
        AND (F.Division_Code = @LocalDivisionCode OR @LocalDivisionCode IS NULL)
        AND F.Coal_Date BETWEEN @LocalFromDate AND DATEADD(dayofyear, 1, @LocalToDate)

    GROUP BY Master_Item, Item_Number, Color_Code, Description, Color_Description


    ) T

    ORDER BY  
        CASE WHEN @LocalOrderBy = 'ordered' THEN Total_Units END DESC, 
        CASE WHEN @LocalOrderBy = 'returned' THEN Total_Units_Returned END DESC,
        CASE WHEN @LocalOrderBy = 'cancelled' THEN Cancelled_Count END DESC,
        CASE WHEN @LocalOrderBy = 'shortshipped' THEN Short_Shipped_Count END DESC

 --------------------------------------------I put it here--------------------      
    OPTION  (RECOMPILE)

END

IF @LocalDetailLevel = 'size'
BEGIN

    SELECT * FROM (
        SELECT DISTINCT

        Master_Item
        ,Item_Number
        ,'--' Color_Code 
        ,Description
        ,'--' Color_Description
        ,SUM(Unit_Retail)  Sum_Unit_Retail
        ,AVG(Unit_Retail)  Avg_Unit_Retail

        ,SUM(Unit_MarkDown)  Sum_Unit_MarkDown
        ,AVG(Unit_MarkDown)  Avg_Unit_MarkDown
        ,AVG(Unit_MarkDown_Percent)  Avg_Unit_MarkDown_Percent

        ,SUM(Sell_Price)  Sum_Sell_Price
        ,AVG(Sell_Price)  Avg_Sell_Price

        ,SUM(Discount_Value)  Sum_Discount_Value
        ,AVG(Discount_Value)  Avg_Discount_Value
        ,AVG(Unit_Discount_Value_Percent)  Avg_Discount_Value_Percent

        ,SUM(Sale_Price)  Sum_Sale_Price
        ,AVG(Sale_Price)  Avg_Sale_Price

        ,SUM(Royalty_Cost)  Sum_Royalty_Cost
        ,AVG(Royalty_Cost)  Avg_Royalty_Cost
        ,AVG(Unit_Royalty_Cost_Percent)  Avg_Royalty_Cost_Percent

        ,SUM(Item_Cost)  Sum_Item_Cost
        ,AVG(Item_Cost)  Avg_Item_Cost
        ,AVG(Unit_Item_Cost_Percent)  Avg_Item_Cost_Percent

        ,SUM(Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight)  Sum_Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight
        ,AVG(Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight)  Avg_Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight
        ,AVG(Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight_Percent)  Avg_Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight_Percent

        ,MAX(Quantity_Invoiced+Quantity_Allocated)  Max_Quantity

        ,SUM(Quantity_Invoiced+Quantity_Allocated)  Total_Units

        ,COUNT(DISTINCT Customer_Purchase_Order_Number) Total_Orders_Cont
        ,-1 Percent_Of_Orders_Cont

        ,SUM(Quantity_Invoiced+Quantity_Allocated) / COUNT(DISTINCT Customer_Purchase_Order_Number) Average_Order_Quantity

        ,SUM(Quantity_Returned)  Total_Units_Returned
        ,MAX(Quantity_Returned)  Max_Quantity_Returned
        ,SUM(Quantity_Returned)  
        /NULLIF(SUM(Quantity_Invoiced+Quantity_Allocated) ,0) Return_Percentage

        ,SUM(CASE WHEN F.Line_Status = 'CANCELLED' THEN Quantity_Ordered ELSE 0 END)  Cancelled_Count
        ,SUM(CASE WHEN F.Line_Status = 'CANCELLED' THEN Quantity_Ordered ELSE 0 END)  
        / NULLIF(SUM(Quantity_Ordered) ,0) Cancelled_Count_Percent

        ,SUM(CASE WHEN (Tags not like '%customerrequested_cancel%' and Tags not like '%ia_cancel%' and (FulfillmentState like 'partial%' or FinancialState like 'partial%')) THEN Short_Shipped ELSE 0 END)  Short_Shipped_Count
        ,SUM(CASE WHEN (Tags not like '%customerrequested_cancel%' and Tags not like '%ia_cancel%' and (FulfillmentState like 'partial%' or FinancialState like 'partial%')) THEN Short_Shipped ELSE 0 END)  
        / NULLIF(SUM(Quantity_Ordered) ,0) Short_Shipped_Count_Percent


    FROM 
        FinalEcomTable F

    WHERE
        1=1
        AND (F.Company_Code = @LocalCompanyCode OR @LocalCompanyCode IS NULL)  
        AND (F.Division_Code = @LocalDivisionCode OR @LocalDivisionCode IS NULL)
        AND F.Coal_Date BETWEEN @LocalFromDate AND DATEADD(dayofyear, 1, @LocalToDate)

    GROUP BY Master_Item, Item_Number, Color_Code, Description, Color_Description

    ) T

    ORDER BY  
        CASE WHEN @LocalOrderBy = 'ordered' THEN Total_Units END DESC, 
        CASE WHEN @LocalOrderBy = 'returned' THEN Total_Units_Returned END DESC,
        CASE WHEN @LocalOrderBy = 'cancelled' THEN Cancelled_Count END DESC,
        CASE WHEN @LocalOrderBy = 'shortshipped' THEN Short_Shipped_Count END DESC

    OPTION  (RECOMPILE)

END

IF @LocalDetailLevel = 'color'
BEGIN

    SELECT * FROM (
        SELECT DISTINCT

        Master_Item
        ,Item_Number
        ,Color_Code 
        ,Description
        ,Color_Description
        ,SUM(Unit_Retail)  Sum_Unit_Retail
        ,AVG(Unit_Retail)  Avg_Unit_Retail

        ,SUM(Unit_MarkDown)  Sum_Unit_MarkDown
        ,AVG(Unit_MarkDown)  Avg_Unit_MarkDown
        ,AVG(Unit_MarkDown_Percent)  Avg_Unit_MarkDown_Percent

        ,SUM(Sell_Price)  Sum_Sell_Price
        ,AVG(Sell_Price)  Avg_Sell_Price

        ,SUM(Discount_Value)  Sum_Discount_Value
        ,AVG(Discount_Value)  Avg_Discount_Value
        ,AVG(Unit_Discount_Value_Percent)  Avg_Discount_Value_Percent

        ,SUM(Sale_Price)  Sum_Sale_Price
        ,AVG(Sale_Price)  Avg_Sale_Price

        ,SUM(Royalty_Cost)  Sum_Royalty_Cost
        ,AVG(Royalty_Cost)  Avg_Royalty_Cost
        ,AVG(Unit_Royalty_Cost_Percent)  Avg_Royalty_Cost_Percent

        ,SUM(Item_Cost)  Sum_Item_Cost
        ,AVG(Item_Cost)  Avg_Item_Cost
        ,AVG(Unit_Item_Cost_Percent)  Avg_Item_Cost_Percent

        ,SUM(Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight)  Sum_Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight
        ,AVG(Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight)  Avg_Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight
        ,AVG(Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight_Percent)  Avg_Order_Gross_Profit_Minus_Discounts_And_Royalty_And_Freight_Percent

        ,MAX(Quantity_Invoiced+Quantity_Allocated)  Max_Quantity

        ,SUM(Quantity_Invoiced+Quantity_Allocated)  Total_Units

        ,COUNT(DISTINCT Customer_Purchase_Order_Number) Total_Orders_Cont
        ,-1 Percent_Of_Orders_Cont

        ,SUM(Quantity_Invoiced+Quantity_Allocated) / COUNT(DISTINCT Customer_Purchase_Order_Number) Average_Order_Quantity

        ,SUM(Quantity_Returned)  Total_Units_Returned
        ,MAX(Quantity_Returned)  Max_Quantity_Returned
        ,SUM(Quantity_Returned)  
        /NULLIF(SUM(Quantity_Invoiced+Quantity_Allocated) ,0) Return_Percentage

        ,SUM(CASE WHEN F.Line_Status = 'CANCELLED' THEN Quantity_Ordered ELSE 0 END)  Cancelled_Count
        ,SUM(CASE WHEN F.Line_Status = 'CANCELLED' THEN Quantity_Ordered ELSE 0 END)  
        / NULLIF(SUM(Quantity_Ordered) ,0) Cancelled_Count_Percent

        ,SUM(CASE WHEN (Tags not like '%customerrequested_cancel%' and Tags not like '%ia_cancel%' and (FulfillmentState like 'partial%' or FinancialState like 'partial%')) THEN Short_Shipped ELSE 0 END)  Short_Shipped_Count
        ,SUM(CASE WHEN (Tags not like '%customerrequested_cancel%' and Tags not like '%ia_cancel%' and (FulfillmentState like 'partial%' or FinancialState like 'partial%')) THEN Short_Shipped ELSE 0 END)  
        / NULLIF(SUM(Quantity_Ordered) ,0) Short_Shipped_Count_Percent

    FROM 
        FinalEcomTable F

    WHERE
        1=1
        AND (F.Company_Code = @LocalCompanyCode OR @LocalCompanyCode IS NULL)  
        AND (F.Division_Code = @LocalDivisionCode OR @LocalDivisionCode IS NULL)
        AND F.Coal_Date BETWEEN @LocalFromDate AND DATEADD(dayofyear, 1, @LocalToDate)

    GROUP BY Master_Item, Item_Number, Color_Code, Description, Color_Description

    ) T

    ORDER BY  
        CASE WHEN @LocalOrderBy = 'ordered' THEN Total_Units END DESC, 
        CASE WHEN @LocalOrderBy = 'returned' THEN Total_Units_Returned END DESC,
        CASE WHEN @LocalOrderBy = 'cancelled' THEN Cancelled_Count END DESC,
        CASE WHEN @LocalOrderBy = 'shortshipped' THEN Short_Shipped_Count END DESC

    OPTION  (RECOMPILE)

END
0
nathan 19 September 2019, 23:34

1 menjawab

Jawaban Terbaik

Ya, di situlah Anda meletakkannya jika Anda ingin mengkompilasi ulang pernyataan itu setiap kali.

Jika kode ini adalah bagian dari proc dan Anda ingin mengkompilasi ulang seluruh proc setiap kali, Anda melakukannya dengan menambahkan WITH RECOMPILE ke definisi prosedur.

0
Greg Low 19 September 2019, 23:52