CODEWITHSUNDEEP
sql-serverperformancet-sql
kevin mouton
kevin mouton

Reputation: 13

TSQL Query performance

I have the following query which takes about 20s to complete.

declare @shoppingBasketID int

select @shoppingBasketID = [uid]
from shoppingBasket sb
where sb.requestID = 21918154   and sb.[status] > 0

select
    ingredientGroup.shoppingBasketItemID as itemID,
    ingredientGroup.[uid] as groupUID
from shoppingBasketItem item
    left outer join shoppingBasketItemBundle itemBundle on itemBundle.primeMenuItemID = item.[uid]
    left outer join shoppingBasketItem bundleItem on bundleItem.[uid] = isnull(itemBundle.linkMenuItemID, item.[uid])
    left outer join shoppingBasketItemIngredientGroup ingredientGroup on ingredientGroup.shoppingBasketItemID = isnull(itemBundle.linkMenuItemID, item.[uid])
    left outer join shoppingBasketItemIngredient ingredient on ingredient.shoppingBasketItemIngredientGroupID = ingredientGroup.[uid]
where item.shoppingBasketID = @shoppingBasketID

The 'shoppingBasketItemIngredient' table has 40 millions rows. When I change the last line to the following the query returns the results almost instantly. (I moved the first select into the second select query).

where item.shoppingBasketID = (select [uid] from shoppingBasket sb  where sb.requestID = 21918154       and sb.[status] > 0)

Do you know why?

Upvotes: 0

Views: 125

Answers (2)

LukStorms
LukStorms

Reputation: 29647

You could add a query hint.

When using a variable the query optimizer could generate a slow execution plan. It's easier for a query optimizer to calculate the optimal plan when a fixed value is used.

But by adding the right hint(s) it could go for a faster execution plan.

For example:

select 
...
where item.shoppingBasketID = @shoppingBasketID
OPTION ( OPTIMIZE FOR (@shoppingBasketID UNKNOWN) );

In the example UNKNOWN was used, but you can give a value instead.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

This is too long for a comment.

Queries in stored procedures are compiled the first time they are run and the query plan is cached. So, if you test the stored procedure on an empty table, then it might generate a bad query plan -- and that doesn't get updated automatically.

You can force a recompile at either the stored procedure or query level, using the option WITH (RECOMPILE). Here is some documentation.

Upvotes: 1

Related Questions