1

Is it better to have one big query or N small ones?

For example to select all the customers with multiple parameters:

select
   id,
   name,
   firstname,
   city,
   birthday,
   ...
from dbo.Customers
where (@Name is null or name like @Name)
and (@FirstName is null or firstname like @FirstName )
and (@City is null or name city @City)
and (@Birthday is null or birthday like @Birthday)

Or too have multiple stored procedures like

select
   id,
   name,
   firstname,
   city,
   birthday,
   ...
from dbo.Customers
where (@Name is null or name like @Name)
and (@FirstName is null or firstname like @FirstName)

select
   id,
   name,
   firstname,
   city,
   birthday,
   ...
from dbo.Customers
where (@City is null or name city @City)

select
   id,
   name,
   firstname,
   city,
   birthday,
   ...
from dbo.Customers
where (@Birthday is null or birthday like @Birthday)
3

I would create One stored procedure with optional parameters. Also instead of using WHERE @Var is NULL or Column = @Var use dynamic sql to build your query.

This kind of query where clause results in a very unefficient execution plan.

Also Sql server doesn't do any shortcircuting when evaluating OR conditions in where clause, for example even if @Var is null evaluates true sql server may still go ahead with the other Or condition and starts evaluating the whole column against a null variable.

Also with these optional parameters, if you pass a specific set of parameters and there is no execution plan for this procedure in the proc cache sql server will create an execution plan best for these specific set of parameters and uses it, but what if you pass a very different set of parameters on the next execution of this procedure, sql server looks into the proc cache and if it find an execution plan there it just goes ahead and uses that execution plan, even though it was a good execution plan for the first set of params passed but a very bad execution plan for these very different set of params (known as Parameter sniffing).

Using dynamic sql also eliminates this parameter sniffing since it stores parameteised execution plans.

I would use one procedure with dynamic sql something like this...

CREATE PROCEDURE dbo.usp_ProcName
 @Name          VARCHAR(100) 
,@FirstName     VARCHAR(100) 
,@City          VARCHAR(100) 
,@Birthday      VARCHAR(100) 
AS 
BEGIN
  SET NOCOUNT ON;

 DECLARE @sql NVARCHAR(MAX);

SET @sql = N'  select  id
                       ,name
                       ,firstname
                       ,city
                       ,birthday
                from dbo.Customers  WHERE 1 = 1'
       + CASE WHEN @Name IS NOT NULL 
              THEN N' AND Name = @Name' ELSE N' ' END 
       + CASE WHEN @FirstName IS NOT NULL 
              THEN N' AND FirstName = @FirstName' ELSE N' ' END 
       + CASE WHEN @City IS NOT NULL 
              THEN N' AND City = @City' ELSE N' ' END 
       + CASE WHEN @Birthday IS NOT NULL 
              THEN N' AND BirthDay LIKE ''%'' + @Birthday + ''%''' ELSE N' ' END 

EXECUTE sp_executesql @Sql
                    ,N'@Name VARCHAR(100) , @FirstName VARCHAR(100) 
                       @City VARCHAR(100), @BirthDay VARCHAR(100)'
                    ,@Name
                    ,@FirstName
                    ,@City
                    ,@Birthday 
END
1

Large, general-purpose search queries often produce an inefficient plan. This can be ameliorated by adding OPTION (RECOMPILE) to the query to invoke parameter embedding. Now your problem is that the general-purpose query is recompiled with every execution, which is itself an overhead. Avoid this by pulling out the common sets of parameters and have specific queries for each:

IF @Name is not null 
and @FirstName is not null 
and @City IS NULL <etc.>
begin
  <query with just these two parameters and no NULL test>
end else
if @City is not null <etc.>
begin
  <query with just city parameter>
end  etc....

The last ELSE clause will fall into the general-purpose SELECT that has OPTION (RECOMPILE) so the added cost is incurred as infrequently as possible. You may have to monitor your application to find what combinations are actually submitted by users.

Although this makes for long SPs it avoids the run-time overhead of constructing dynamic SQL and allows for better plan caching.

Now you have to address parameter sniffing. Do this by having each of the specific queries in its own SP. These will each have their own plan in cache, and each will be smaller than that for a single SP holding all the queries. This way infrequently used search SPs' plans can be evicted from cache while frequently used ones are retained. Also the recompile cost of each is smaller should it be needed. Parameter sniffing issues are limited to one search SP at a time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.