How to tune/improve Stored Procedure which takes many parameters to generate a dynamic SQL Statement

I have several SQL Server 2012 stored procedures which take nearly 50 optional parameters and dynamically build a SQL statement based on what parameters have been given.  Below is a pared down version of one of the procedures.

Is there a more performance friendly method for me to be able to perform the same task?  I'm believe that by dynamically creating a SQL statement at each call I'm not benefiting form the performance gains associated with Stored Procs.

CREATE PROCEDURE [dbo].[DSS_SP_CustomerOrderDetails]
	@BusinessUnit int,
	@Division nvarchar(max) = null,
	--45 more parameters here
	@PickListNo nvarchar(max) = null,
	@UserEmail nvarchar(max) = null,
	@SecurityGroups nvarchar(max) = null
	AS
BEGIN
	SET NOCOUNT ON;

	--Load Associated divisions
	CREATE TABLE #Divs
	(
		FWID int
	)
	Insert #Divs
	EXEC[dbo].[DSS_SP_PermittedDivisions] @BusinessUnit = @BusinessUnit, @UserEmail = @UserEmail, @SecurityGroup = @SecurityGroups
	
	--Load Associated salespersons
	CREATE TABLE #Sales
	(
		FWID int
	)
	Insert #Sales
	EXEC[dbo].[DSS_SP_Permittedsalespersons] @BusinessUnit = @BusinessUnit, @UserEmail = @UserEmail, @SecurityGroup = @SecurityGroups
	declare @sql nvarchar(max), @where nvarchar(max), @And nvarchar(5)

	select @sql = N' Select
      [BusinessUnitID]
      ,[BusinessUnitDesc]
	  --Many more rows selected here
	  ,case
		when oplinestatusid = 9 then
			Quantity
		else 
			null
		end as QuantityCancelled
      ,[FactoryCost]
      ,[ExtFactoryCost]
  FROM [BBC_DataWarehouse].[dbo].[DSS_TB_CustomerOrderDetail] '
  
select @where =  N' BusinessUnitID=' + CONVERt(nvarchar, @BusinessUnit), @and = ' and '

if @OrderCode is not null 
begin
	select @where = @where + @and + N' OrderCode in (' + BBC_DataWarehouse.dbo.[DSS_FN_DelimitText](@OrderCode) + ')', @and = ' and '
end

if @BaseOrderCode is not null 
begin
	select @where = @where + @and + N' BaseOrderCode in (' + BBC_DataWarehouse.dbo.[DSS_FN_DelimitText](@BaseOrderCode) + ')', @and = ' and '
end
--45 similar if clauses here
if @ExtDiscountMax is not null 
begin
	select @where = @where + @and + N' (Discount * Quantity) <= ' + @ExtDiscountMax, @and = ' and '
end

--Restrict to rows with divisions and salespersons existing in temp tbles
select @where = @where + @and + N' DivisionID in (SELECT FWID from #Divs) ' , @and = ' and '
select @where = @where + @and + N' SalespersonID in (SELECT FWID from #Sales) ' , @and = ' and '

--Add a where clauase if necessary
if len(@where) > 0 
begin
	select @sql = @sql + ' where ' +  @where 
end

--Run the final query
exec sp_executesql @sql

--Drop temp tables
drop table #Divs
drop table #Sales
END

Open in new window

canuckconsultingAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Dynamic SQL by itself won't give you poor performance.  In fact, with so many comparison options, dynamic SQL is required to get even reasonable performance from the final query.

But, using any functions for computations on columns in the WHERE clause, such as:
 (Discount * Quantity) <=
could cause performance problems.

Other than that, it comes down to proper indexing.  You need to look at missing index and index usage stats on all the tables involved, or potentially involved, in the query.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Be careful with what you are doing - you may be subject to SQL injection.
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Just a thought:  Other than the above comments and the obvious stuff like data modeling / reducing number of columns returned / design, I really don't think it's realistic to optimize a SP where there are 50 parameters, and an infinite number of combinations.  

You can do query hints on common selections, such as
OPTIMIZE @BusinessUnit = 'Accounting'

Open in new window


Perhaps a better idea is to look at how the SP will frequently be called, and then create separate SP's that are optimized specifically for the most common selections, then have your front-end determine which SP to call.   That's (how I'm told) many search engines return data quickly when there's a wompload of user-selectable search criteria.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I have several SQL Server 2012 stored procedures which take nearly 50 optional parameters and dynamically build a SQL statement based on what parameters have been given.
You might want to review the strategy. What does the SP? Why it will need 50 optional parameters? How hard/easy is to maintaining it?
0
 
canuckconsultingAuthor Commented:
Thanks guys and sorry for the late reply...just back from hols.

Jim Horn &  Vitor Montalvão - this query is used by a SSRS report which a user can specify many parameters for via a web interface.  The backend is dedicated to this reporting application so is refreshed each morning and read-only  so  I have a large number of indices on each relevant table.  If there was a better way of dealing with it I'd been keen to try it.

To give an idea of the application, below is the panel where the users have selected the desired report and are presented with the filters relevant to the report.  Most of these map to the parameters in the above stored procedure.

screenshot
0
 
Anthony PerkinsConnect With a Mentor Commented:
As Scott has mentioned Dynamic SQL may be your best option, just focus on the indexing, that is the part that will make or break you.
0
 
canuckconsultingAuthor Commented:
Thanks again for the comments.  Sorry for delay; I fell off the experts exchange wagon.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.