October 30, 2008 at 10:48 am
I have 10 stored procedures with the same 4 input parameters. These input parameters correspond to optional, user-selected dynamic filter choices (i.e., WHERE clause conditions). I plan to use dynamic SQL to create the SQL statement and sp_executesql for execution.
These 10 stored procedures share the exact same "body" of SQL. The only difference is the SELECT statement. Depending on which stored procedure calls it, it may return scalar or table values. For example, stored procedure A, B, and C use the same "logic" to construct the SQL statement. However, SP A returns AVG(price), SP B returns COUNT(products), and SP C returns DISTINCT product_id, product_color, and product_size.
How should I construct each stored procedure? Should each stored procedure:
(1) contain and execute the entire SQL statement construction
(2) call a "general" stored procedure which receives the SELECT statement, construct and execute the SQL statement, and return the scalar/table results to the calling stored procedure
(3) calls a "general" stored procedure which receives the SELECT statement, constructs the SQL statement, returns the SQL to the calling stored procedure; and then the calling stored procedure manipulates the "general" stored procedure's SQL according to its own individual needs and executes the SQL
With option #2, some stored procedures may need to store results in a temporary table and perform further calculations. For example
Generic stored procedure #1 - returns product_id, product_color, product_size
stored procedure A - calls stored procedure #1, and returns result to application
stored procedure B - calls stored procedure #1, stores result in temp table, does COUNT(*) on temp table, returns result to application
October 31, 2008 at 4:13 am
Hi,
I plan to use dynamic SQL to create the SQL statement and sp_executesql for execution.
If your parameters are optional, do not create dynamic sql, but construct WHERE clause this way:
WHERE (@parameter1 is null or table.Column1 = @parameter1) and (@parameter2 is null or table.Column2 = @parameter2) and ...
I would also advise you to create one general select statement, but not as a stroed procedure, rather a table-valued inline function, and use that function from other stored procedures and functions. Table valued function will eliminate the need to store temporary results for other functions and procedures manipulating its result.
For example, stored procedure A, B, and C use the same "logic" to construct the SQL statement. However, SP A returns AVG(price), SP B returns COUNT(products), and SP C returns DISTINCT product_id, product_color, and product_size.
Implement A and B as a scalar-valued functions and C as stored procedure or table valued function, all three using your general function.
(1) contain and execute the entire SQL statement construction
This would be hard to maintain. Try and reuse your code.
(2) call a "general" stored procedure which receives the SELECT statement, construct and execute the SQL statement, and return the scalar/table results to the calling stored procedure
(3) calls a "general" stored procedure which receives the SELECT statement, constructs the SQL statement, returns the SQL to the calling stored procedure; and then the calling stored procedure manipulates the "general" stored procedure's SQL according to its own individual needs and executes the SQL
Construct your select statement based on table valued function, so you won't need to store temporary result.
If I get you right you are manipulating purchase order data. So your general function would return [purchase order number], [purchase order line], product id, product color, product size, product price. And then your stored procedure C could select distinct product attributes from it.
Let me know if you have further questions. Regards,
October 31, 2008 at 1:06 pm
1976 (10/31/2008)
Hi,I plan to use dynamic SQL to create the SQL statement and sp_executesql for execution.
If your parameters are optional, do not create dynamic sql, but construct WHERE clause this way:
WHERE (@parameter1 is null or table.Column1 = @parameter1) and (@parameter2 is null or table.Column2 = @parameter2) and ...
In "Inside Microsoft SQL Server 2005: T-SQL Programming", I read that using the above technique will lead to performance problems. Specifically, the execution plan from the first stored procedure invocation will be used throughout even though the first invocation's parameters might never be used again. I don't have a scanner, otherwise I would scan it for you. I found a website that elaborates on it as well:
http://www.developerfusion.com/article/7305/dynamic-search-conditions-in-tsql/1
Do you still recommend the WHERE clause construction?
Thanks again for your input!
November 4, 2008 at 4:43 am
Thank you for the link to the article on dynamic and static queries. I need to update/educate myself on sql query performance tuning:Whistling:. I am using a lot the syntax I recommended to you earlier - after making sure I created all necessary indexes I have no problem with performance.
Personally I am thinking of using 'Umachandar's Bag of Tricks': http://www.developerfusion.com/article/7305/dynamic-search-conditions-in-tsql/8/
Cheers,
November 4, 2008 at 7:23 am
I use a variation on that syntax but found that the performance issue depends a lot on the table sizes that the parameters are used against, i.e
If param 1 references a (indexed) column in a table with 10000 rows which is (properly) joined to a table of 10000000 (10 million) rows the performance differnce is margin. If you are searching a column on the second table....well that's where you're gonna see some obvious performance issues.
Basically the index seek or scan on Table A does not take a signifcant time difference.
Just my experience....Test your situation
November 4, 2008 at 7:38 am
1976 (10/31/2008)
Hi,I plan to use dynamic SQL to create the SQL statement and sp_executesql for execution.
If your parameters are optional, do not create dynamic sql, but construct WHERE clause this way:
WHERE (@parameter1 is null or table.Column1 = @parameter1) and (@parameter2 is null or table.Column2 = @parameter2) and ...
Be careful with that construct of where clause. Firstly, the optimiser doesn't handle it all that well. Second, depending on the parameters passed, the optimal plan changes dramatically. It can cause some really bad cases of parameter sniffing.
Normally, for that I'd suggest separate queries if the number of parameters is small (1 or 2) or dynamic SQL if the number of parameters is larger.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 4, 2008 at 7:39 am
AnzioBake (11/4/2008)
I use a variation on that syntax but found that the performance issue depends a lot on the table sizes that the parameters are used against
Which variation on the syntax are you referring to?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply