CODEWITHSUNDEEP
sql-serverif-statementstored-procedures
Allison Paiva
Allison Paiva

Reputation: 37

How to optimize the stored procedure code

I have a stored procedure which accepts 5 input parameters. The columns which the stored procedure returns is the same, but where condition changes according the params passed in.

Currently I'm using if, else-if to check for different conditions, and select the statements according to where conditions. Can anyone help me with other optimized way?

Update: now I have almost 14 parameters and check the conditions with all possible combinations. Is there any way in SQL for this check, apart from giving all conditions explicitly?

Here is the piece of stored procedure:

if(@OwnerWWID is null and @OwnerEmailID is null and @SourceURL is null and @ServerName is null and @Wave is not null)
begin
    select distinct 
        mp.Id, mp.SourceURL,.......
    from
        dbo.MPlanner as mp with(nolock)
    where 
        mp.MigrationWave = @Wave
end
else if(@OwnerWWID is not null and @OwnerEmailID is null and @SourceURL is null and @ServerName is null and @Wave is null)
begin
    select distinct 
        mp.Id, mp.SourceURL,.......
    from
        dbo.MPlanner as mp with(nolock)
    where  
        mp.OwnerWWID like @OwnerWWID + '%'
end
else if(@OwnerWWID is null and @OwnerEmailID is not null and @SourceURL is null and @ServerName is null and @Wave is null)
begin
    select distinct  
        mp.Id, mp.SourceURL,.......
    from
        dbo.MPlanner as mp with(nolock)
    where 
        ((mp.[Document Library Owner Email IDs] like @OwnerEmailID + '%' 
          or mp.[Site Owner Email IDs] like @OwnerEmailID + '%' 
          or mp.SourceURL like @OwnerEmailID + '%' 
          or mp.UserEmailID like @OwnerEmailID + '%')
         and DestinationType = 'sp') 
        or ((mp.OwnerEmailID like @OwnerEmailID + '%' 
             or mp.SourceURL like @OwnerEmailID + '%' 
             or mp.UserEmailID like @OwnerEmailID + '%') 
            and mp.DestinationType = 'odfb' and mp.OwnerJJEDSStatus = 1)
end

Upvotes: 0

Views: 1026

Answers (1)

allmhuran
allmhuran

Reputation: 4464

I think understand why you want to change it. All of these if-else blocks start to become difficult to manage, especially if you later want to be able to change the procedure to filter on multiple parameters, not just a single parameter. The number of conditional queries explodes.

There is a way to do this with a single statement. It's the "optional parameter" pattern. It looks like this:

    select  ...
    from    dbo.MPlanner mp
    where   (@wave is null or mp.MigrationWave = @wave)
    and     (@OwnerWWID is null or mpOwnerWWID like @ownerWWID + '%')
    and     (@OwnerEmailId is null or ...)
    option  (recompile);

The pattern should be clear: If the parameter has no value, ignore it. Otherwise use it for filtering.

Be aware that while this can be a huge optimization in terms of code volume, it can actually be a deoptimization in terms execution performance. The option (recompile) is critical. Without it, SQL will generate a plan based on the first time the procedure is run, but obviously the plan to use in a situation like depends entirely on which parameters are set.

The option recompile tells SQL: "Hey, every time you run this statement, figure out a good plan based on the values of the variables that you see this time". So, if @wave (for example) is null for some specific execution, SQL can simply short circuit that predicate out entirely. If you only populate a single parameter with a not null value, only that condition will actually be tested.

Upvotes: 3

Related Questions