CODEWITHSUNDEEP
sqlsql-serversql-server-2012
RGS
RGS

Reputation: 5211

Why query takes more time to execute when I use if else statement in stored procedure?

I have used below query in my stored procedure. When I am using if else statement in the stored procedure query takes more time to execute. When I remove if else statement query takes less time to return result.

If @P_Make = 'ALL' Or IsNull(@P_Make, '') = ''
Begin
    Set @P_Make = '%';
End
Else
Begin
    Set @P_Make = @P_Make + '%';
End 
If @P_Model = 'ALL' Or IsNull(@P_Model, '') = ''
Begin
    Set @P_Model = '%';
End
Else
Begin
    Set @P_Model = @P_Model + '%';
End     
If @P_Location = 'ALL' Or IsNull(@P_Location, '') = ''
Begin
    Set @P_Location = '%';
End
Else
Begin
    Set @P_Location = @P_Location + '%';
End
If @P_City = 'ALL' Or IsNull(@P_City, '') = ''
Begin
    Set @P_City = '%';
End
Else
Begin
    Set @P_City = @P_City + '%';
End
If @P_Category = 'ALL' Or IsNull(@P_Category, '') = ''
Begin
    Set @P_Category = '%';
End
Else
Begin
   Set @P_Category = Case When @P_Category = 'Bikes & Scooters' Then '2W%'
                             When @P_Category = '3 Wheelers' Then '3W%'
                             When @P_Category = 'Cars & SUVs' Then '4W%'
                             When @P_Category = 'Trucks' Then 'CV%'
                             When @P_Category = 'Farm Equipments' Then 'FE%'
                             When @P_Category = 'Industrial Equipments' Then 'IE%'
                             When @P_Category = 'Construction Equipments' Then 'CE%'
                             Else @P_Category + '%'
                        End
    End
If @P_Service = 'ALL' Or IsNull(@P_Service, '') = ''
Begin
    Set @P_Service = ''
End

Select Count(C.Sal_Pk_Id) 
From dbo.Auction (NoLock) A
Inner Join dbo.PLACE (NoLock) B On A.Auc_Place_Fk_Id = B.Place_Pk_Id
Inner Join dbo.SALES_DETAILS (NoLock) C On A.Auc_Code = C.Sal_Auc_Code
Inner Join dbo.AUCTIONSERVICES (NoLock) D On C.Sal_Regno = D.Auc_Service_Regno And C.Sal_Auc_Code = D.Auc_Service_Auctioncode
Inner Join dbo.LOT (NoLock) E On C.Sal_Lot_No = E.Lot_Lot_No And C.Sal_Auc_Code = E.Lot_Auc_Code 
Inner Join dbo.INVENTORY (NoLock) F On C.Sal_Regno = F.Inv_H_Reg_No
Where C.[Status] = 'L' And E.Lot_Sold_Status = 'S'
And IsNull(B.Place_City, '') Like @P_City And IsNull(B.Place_State, '') Like @P_Location
And IsNull(F.Inv_H_Category, '') Like @P_Category And IsNull(F.Inv_H_Mfg_Name, '') Like @P_Make
And IsNull(F.Inv_H_Model, '') Like @P_Model

Query without if else statement:

Select Count(C.Sal_Pk_Id) 
From dbo.Auction (NoLock) A
Inner Join dbo.PLACE (NoLock) B On A.Auc_Place_Fk_Id = B.Place_Pk_Id
Inner Join dbo.SALES_DETAILS (NoLock) C On A.Auc_Code = C.Sal_Auc_Code
Inner Join dbo.AUCTIONSERVICES (NoLock) D On C.Sal_Regno = D.Auc_Service_Regno And C.Sal_Auc_Code = D.Auc_Service_Auctioncode
Inner Join dbo.LOT (NoLock) E On C.Sal_Lot_No = E.Lot_Lot_No And C.Sal_Auc_Code = E.Lot_Auc_Code 
Inner Join dbo.INVENTORY (NoLock) F On C.Sal_Regno = F.Inv_H_Reg_No
Where C.[Status] = 'L' And E.Lot_Sold_Status = 'S'
And (@P_City = '' Or IsNull(B.Place_City, '') = @P_City)
And (@P_Location= '' Or IsNull(B.Place_State, '') = @P_Location)
And (@P_Category = '' Or IsNull(F.Inv_H_Category, '') = @P_Category)
And (@P_Make = '' Or IsNull(F.Inv_H_Mfg_Name, '') = @P_Make)
And (@P_Model = '' Or IsNull(F.Inv_H_Model, '') = @P_Model)

Why the query output takes less time when I remove if else statement in the stored procedure?

Upvotes: 0

Views: 318

Answers (2)

Luaan
Luaan

Reputation: 63772

There's two main reasons - like and execution plan.

like '%something' is the slowest possible thing you can filter by - it means going row by row, reading the whole data in the column and doing a comparison. It can't use any index seeks, only scans.

Second, you only get one execution plan, based on the first way the procedure is called. This pretty much guarantees that for any other input data, the performance will suffer. In your second example, while the plan is again sub-optimal and dependent on the initial inputs, it doesn't completely ignore all the possible filters - it just optimizes based on statistics.

Dynamic filters aren't something nobody tried to solve before. Learn from the best: http://www.sommarskog.se/dyn-search.html

Upvotes: 3

Alex Vazhev
Alex Vazhev

Reputation: 1461

I think the query without if else is faster because it does not have a Like operator 

Where C.[Status] = 'L' And E.Lot_Sold_Status = 'S'
And IsNull(B.Place_City, '') Like @P_City And IsNull(B.Place_State, '') Like @P_Location
And IsNull(F.Inv_H_Category, '') Like @P_Category And IsNull(F.Inv_H_Mfg_Name, '') Like @P_Make
And IsNull(F.Inv_H_Model, '') Like @P_Model

and I suppose that the second query can use indexes more efficiently. The first and second queries are different, please look at Actual Execution Plan and you will realize why it happens.

Upvotes: 2

Related Questions