Solved

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

Posted on 2014-12-19
7
120 Views
Last Modified: 2015-02-16
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

0
Comment
Question by:canuckconsulting
7 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40509471
Be careful with what you are doing - you may be subject to SQL injection.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 167 total points
ID: 40509507
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
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 167 total points
ID: 40509883
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40514734
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
 

Author Comment

by:canuckconsulting
ID: 40521841
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 166 total points
ID: 40522980
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
 

Author Closing Comment

by:canuckconsulting
ID: 40612222
Thanks again for the comments.  Sorry for delay; I fell off the experts exchange wagon.
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question