Solved

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

Posted on 2014-12-19
7
118 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:
ScottPletcher 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

910 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now