Sql using different execution plans when starting a request and when executing this request inside a stored procedure?

Question

I have this request,

Declare @Prcocessrate float declare @item varchar(20) declare @process varchar(20) declare @branch varchar(20) set @item = 'shirt' set @process = 'kt' set @branch = '1' select @Prcocessrate = ProcessPrice from itemwiseprocessrate where itemname=@Item and Process=@process and branchid=@branch 

when I run it alone, only 3 steps are displayed in the execution plan, see for yourself.

but i have sp_newBooking procedure like

 ALTER PROC sp_newbooking -- other arguements-- AS BEGIN --OTHER FLAGS-- ELSE IF (@Flag = 32) BEGIN declare @ItemId varchar(max),@ProcessRate float --set @BranchId='1' select @ProcessCode = DefaultProcessCode from mstconfigsettings where branchid=@BranchId select @ItemId= DefaultItemId from mstconfigsettings where branchid=@BranchId select @ItemName= ItemName from itemmaster where itemid=@ItemId and branchid=@BranchId select @ProcessRate = ProcessPrice from itemwiseprocessrate where itemname=@ItemName and ProcessCode=@ProcessCode and branchid=@BranchId if(@ProcessRate is not null) select @ItemName as ItemName,@ProcessCode as ProcessCode,@ProcessRate as ProcessRate else select @ItemName as ItemName,@ProcessCode as ProcessCode,'0' as ProcessRate END -- OTHER FLAGS -- END 

Now !, when I run this

 exec sp_newbooking @flag = 32, @Branchid = 1 

In the execution plan, 6 steps are displayed ! Here's a drawing ..!
See Request 4

Why does he perform 6 steps to execute the same query when executing from this procedure, while he performs 3 steps when executing one? Wtf is that?

Answer 1

There are many reasons why SQL may use different execution plans. It could be:

• Different parameters for the same queries (you use constants in the first example, queries in the second can have different values)
• Different data (which means that you are doing queries on dev and production)
• The sniffing parameter is lower, but at the beginning the procedure may have the “desired” parameters
• Various data types - as @MartinSmith points out, we do not see parameter declarations. You may have a variable that does not match the type of field with which it is mapped.

Sniffing parameter
The stored procedures make a ' sniffing ' parameter , which is a blessing (if it works for you) and a scourge (if it works against you). First go through, someone is looking for Zebr% for zerbrowski. The last name index understands that this is very specific and returns, say, 3 lines from a million - therefore, one execution plan is built. When compiling proc for a result with a low line, the next search is performed for S% . Well, S is your most common name and corresponds to 93.543 lines from 1 million.

So what can you do?
There are many steps you can take to test this ....

• Look closely at the data types of the variables, comparing the ad hock, proc query and the base table (sp_columns mytable).
• Isolate moving parts
  • Make sure that you are working in both queries on the same system with the same data
  • Trace to ensure that the first time you run proc, use the expected parameters
  • Try running an ad hock request with several different parameters and see how the execution plan changes.
  • If you cannot isolate activity, temporarily add WITH RECOMPILE to proc to compare execution plans. Otherwise, run DBCC FREEPROCCACHE before starting. ( DISCLAIMER - if it is a living system, make sure you understand what they will do ).