Optimizing SQL Query

[AmrMohammed]

Greetings

I want to optimize a SQL Query. The problem I have a database full of such Queries and I need help for optimizing those Queries the problem with the Query is that it contains dozens of CASE blocks and within those CASE blocks there are calculations the Query results in a 2185409 rows and it takes at least 04:30 minutes and on other machines 11:00 minutes and that is too much time :( I cannot publish the whole Query as I do not have it but I can show you part of it

SUM(    CASE WHEN @Unit_Code > 0 
            THEN (ISNULL(Item_InStore.InStore_BegBalQty,0) / ISNULL([dbo].GetFactorByUnitInStore(@Unit_Code,InStore_ID),1)) 
            ELSE (  case 
                    when @Unit_Type = 1 then (ISNULL(Item_InStore.InStore_BegBalQty,0)) 
                    else (ISNULL(Item_InStore.InStore_BegBalQty,0) * ISNULL((1/LargeUnit_UnitData.UnitData_FinalFactor),0)) 
                    end) 
            END) AS Balance,0 AS BonusBalance,
        CASE 
        WHEN @ItemPrice > 0 
        THEN 
            (   CASE 
                WHEN @Unit_Code > 0 
                THEN ((ISNULL(dbo.GetItemPrice(IUD.UnitData_ID,@ItemPrice),0)) * ISNULL([dbo].GetFactorByUnitInStore(@Unit_Code,Item_InStore.InStore_ID),0)) 
                ELSE (  case 
                        when @Unit_Type = 1 
                        then ((ISNULL(dbo.GetItemPrice(IUD.UnitData_ID,@ItemPrice),0))) 
                        else ((ISNULL(dbo.GetItemPrice(IUD.UnitData_ID,@ItemPrice),0)) / ISNULL((1/LargeUnit_UnitData.UnitData_FinalFactor),1)) 
                        end ) 
                END)
        ELSE
        (
            CASE 
            WHEN @ShowItemCost = 1 
            Then
                CASE 
                WHEN @CostType = 2 
                THEN
                    (   CASE 
                        WHEN @Unit_Code > 0 
                        THEN ((ISNULL(Item_InStore.InStore_Avg,0) + (CASE WHEN ISNULL(Item_ImaginaryPercent,0) > 0 THEN ((ISNULL(Item_ImaginaryPercent,0) / 100)* ISNULL(Item_InStore.InStore_Avg,0) ) ELSE ISNULL(Item_ImaginaryAvg,0) END)) * ISNULL([dbo].GetFactorByUnitInStore(@Unit_Code,Item_InStore.InStore_ID),0)) 
                        ELSE (  case 
                                when @Unit_Type = 1 
                                then ISNULL(Item_InStore.InStore_Avg,0) + (CASE WHEN ISNULL(Item_ImaginaryPercent,0) > 0 THEN ((ISNULL(Item_ImaginaryPercent,0) / 100)* ISNULL(Item_InStore.InStore_Avg,0) ) ELSE ISNULL(Item_ImaginaryAvg,0) END)
                                else ((ISNULL(Item_InStore.InStore_Avg,0) + (CASE WHEN ISNULL(Item_ImaginaryPercent,0) > 0 THEN ((ISNULL(Item_ImaginaryPercent,0) / 100)* ISNULL(Item_InStore.InStore_Avg,0) ) ELSE ISNULL(Item_ImaginaryAvg,0) END))/ ISNULL((1/LargeUnit_UnitData.UnitData_FinalFactor),1)) 
                                end ) 
                        END)
                WHEN @CostType = 3 
                THEN
                    (   CASE 
                        WHEN @Unit_Code > 0 
                        THEN ((ISNULL(dbo.GetItemPrice(IUD.UnitData_ID,@ItemPriceID),0))) 
                        ELSE (  case 
                                when @Unit_Type = 1 
                                then ((ISNULL(dbo.GetItemPrice(IUD.UnitData_ID,@ItemPriceID),0))) 
                                else ((ISNULL(dbo.GetItemPrice(IUD.UnitData_ID,@ItemPriceID),0)) / ISNULL((1/LargeUnit_UnitData.UnitData_FinalFactor),1)) 
                                end ) 
                        END)
                ELSE
                    (   CASE 
                        WHEN @Unit_Code > 0 
                        THEN ((ISNULL(Item_InStore.InStore_Avg,0)) * ISNULL([dbo].GetFactorByUnitInStore(@Unit_Code,Item_InStore.InStore_ID),0)) 
                        ELSE (  case 
                                when @Unit_Type = 1 
                                then ((ISNULL(Item_InStore.InStore_Avg,0))) 
                                else ((ISNULL(Item_InStore.InStore_Avg,0)) / ISNULL((1/LargeUnit_UnitData.UnitData_FinalFactor),1)) 
                                end ) 
                        END)
                END
            ELSE 0 
            END
        ) 
        END AS AVGCost,0 AS Weight

The Problem is that the Query contains 2 - 3 blocks of the one above and I need to optimize it any ideas of help from experts :)

[pritaeas]

Looks like a block like this:

CASE 
    WHEN @Unit_Code > 0 THEN ((ISNULL(dbo.GetItemPrice(IUD.UnitData_ID,@ItemPriceID),0))) 
    ELSE (  
        case 
            when @Unit_Type = 1 then ((ISNULL(dbo.GetItemPrice(IUD.UnitData_ID,@ItemPriceID),0))) 
            else ((ISNULL(dbo.GetItemPrice(IUD.UnitData_ID,@ItemPriceID),0)) / ISNULL((1/LargeUnit_UnitData.UnitData_FinalFactor),1)) 
        end ) 
END)

Can be rewritten to this:

CASE 
    WHEN @Unit_Code > 0 OR @Unit_Type = 1 THEN ((ISNULL(dbo.GetItemPrice(IUD.UnitData_ID, @ItemPriceID), 0))) 
    ELSE ((ISNULL(dbo.GetItemPrice(IUD.UnitData_ID, @ItemPriceID), 0)) / ISNULL((1 / LargeUnit_UnitData.UnitData_FinalFactor), 1)) 
        end ) 
END)

Or perhaps even:

((ISNULL(dbo.GetItemPrice(IUD.UnitData_ID, @ItemPriceID), 0))) /
CASE 
    WHEN @Unit_Code > 0 OR @Unit_Type = 1 THEN 1
    ELSE ISNULL((1 / LargeUnit_UnitData.UnitData_FinalFactor), 1)) 
END)

You call a lot of functions. Perhaps it might be better to get all those values first, and then do a self-join with all those values. But that is something that is a lot of work and needs some profiling first. E.g. I see this a lot: ISNULL(dbo.GetItemPrice(IUD.UnitData_ID, @ItemPriceID), 0) Why not put that in a variable, instead of calculating it each time.

[AmrMohammed]

Thanks for your reply "pritaeas"

The problem that I'm new at the company where I'm working and do not know the database structure and how everything is going there :( and I'm just trying to optimize the work as these Query is a part of a stored procedure and its execution time is at least 04:30 minutes. So I cannot change the Query and the stored as I do not know the structure and its behaviour well I'm trying to find some solutions, such as changing the use of all CASE statements and things like this.

Thanks again

[pritaeas]

such as changing the use of all CASE statements and things like this.

Like the sample above, just try to find repeated code and eliminate it.