Solved

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

Posted on 2014-12-19
7
119 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
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 47

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Addition to SQL for dynamic fields 6 37
This query failed in sql 2014 5 29
sql server computed columns 11 29
sql how to count case when 4 14
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

786 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