Solved

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

Posted on 2014-12-19
7
121 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 50

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

739 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