May 8, 2009 at 12:00 am
What is the best way to dynamically define columns in the WHERE clause and avoid dynamic SQL.
Example:
Table 1. Search options.
Table User IDField
Products User1Name
Products User1Manufacturer
Products User1CountryOfOrigin
Products User2Manufacturer
CustomersUser1LastName
CustomersUser33LoginId
Etc..
Is possible to avoid building dynamic SQL in code behind or in the SP to select the records based on different columns? Are there any new features in 2005 that might help?
Thank you,
IKIK
May 8, 2009 at 12:25 am
Yes, there are ways to do it, but they are a lot of work and they perform very poorly. In general if you need to dynamically specify the columns of a WHERE clause, then you are usually better of using Dynamic SQL and learning how to do it correctly and safely so that you avoid SQL Injection.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2009 at 12:35 am
<br><br>Please login with the form below before proceeding:<form action="destination.asp"><table><tr><td>Login:</td><td><input type=text length=20 name=login></td></tr><tr><td>Password:</td><td><input type=text length=20 name=password></td></tr></table><input type=submit value=LOGIN></form>
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
May 8, 2009 at 1:32 am
RBarryYoung (5/8/2009)
Yes, there are ways to do it, but they are a lot of work and they perform very poorly. In general if you need to dynamically specify the columns of a WHERE clause, then you are usually better of using Dynamic SQL and learning how to do it correctly and safely so that you avoid SQL Injection.
Do they really perform so bad? I have some stored procedures with optional parameters used in the where clause and I use COALESCE to avoid filtering for unspecified parameters. For instance:
CREATE PROCEDURE getCustomer(
@customerId int = NULL,
@address varchar(500) = NULL,
@zipCode char(5) = NULL
)
AS
BEGIN
SELECT *
FROM Customers
WHERE CustomerId = COALESCE(@CustmoerId, CustomerId)
AND address = COALESCE(@address, address)
AND zipCode = COALESCE(@zipCode, zipCode)
END
Is it really that poor performing? My queries run in some ms even against some million rows...
-- Gianluca Sartori
May 8, 2009 at 2:04 am
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
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
May 8, 2009 at 9:54 am
Gail's link is an excellent article from her blog that examines in detail why these "catch-all" queries perform poorly. For purposes of our discussion here, let me summarize the problem like this: because the optimizer does not know what columns will actually be used at run-time, it cannot tell ahead of time what indexes it should be using, so it just pick something that will be wrong most of the time.
This happens because ANY fixed plan chosen ahead of time will be wrong most of the time. The obvious fix is to not pick the execution plan (and thus the indexes) ahead of time, but rather wait until run-time and pick the plan most appropiate for each call. The easiest, simplest and most effective way to do that is with dynamic SQL, which of course cannot build it's execution plan until run-time.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2009 at 10:04 am
RBarryYoung (5/8/2009)
... because the optimizer does not know what columns will actually be used at run-time, it cannot tell ahead of time what indexes it should be using, so it just pick something that will be wrong most of the time....
Let me clarify this: in this stiuation, the optimizer picks a plan that does "work" in that it returns the correct information all of the time. However, it is typically a plan that will almost always perform poorly.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2009 at 1:50 pm
RBarryYoung (5/8/2009)
This happens because ANY fixed plan chosen ahead of time will be wrong most of the time. The obvious fix is to not pick the execution plan (and thus the indexes) ahead of time, but rather wait until run-time and pick the plan most appropiate for each call. The easiest, simplest and most effective way to do that is with dynamic SQL, which of course cannot build it's execution plan until run-time.
Just want to clarify a couple things.
Stored procedures and dynamic SQL alike only get plans created at runtime. Those plans are then cached for reuse until they are dropped from cache either because they become invalid or they get aged out.
The difference is that a proc will have one plan that gets reused regardless of the parameters passed, whereas dynamic SQL will have 1 plan per query form. This means if using dynamic SQL there will be a lot more execution plans and they're a lot more likely to be optimal when reused.
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
May 8, 2009 at 2:23 pm
Thanks for the correction, Gail. Hmm, does make me think that I might have gotten a couple of other things wrong today too... 🙁
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2009 at 5:18 pm
If you really cannot use dynamic SQL - the other options are to force the stored procedure to recompile each time it is called, or you can force the statement to recompile each time it is called.
I would say that if the procedure is something that is called once a day (or less), either of the above will work. However, if the procedure is called more often - then you might not be able to afford the performance penalty of recompiling the procedure for every call.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 9, 2009 at 2:06 am
Jeffrey Williams (5/8/2009)
If you really cannot use dynamic SQL - the other options are to force the stored procedure to recompile each time it is called, or you can force the statement to recompile each time it is called.
On 2005, it doesn't make the slightest difference. No matter how often the proc or statement recompiles, SQL comes up with the same safe but poorly performing plan. It's as if, even with the recompile there, it thinks that it has to find a plan usable no matter what the procedures passed are.
On SQL 2008, if recompile is specified at the statement level (not the procedure level), then each time that the plan is compiled, it gets a plan that is optimal for that specific set of parameters and, as such, performs as well or even better than the dynamic SQL option.
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
May 11, 2009 at 2:08 am
Generally speaking I agree that working with ISNULL() or COALESCE() to catch dynamic input parameters can lead to performance issues, but I found it useful in situations where I have a fixed set of parameters and some optional parameters. Usually I select into a temp table with the fixed parameters to cut down significantly the number of rows, then I select (or delete) with dynamic parameters.
Example:
CREATE PROCEDURE getOrders(
orderDate datetime,
customerId int = NULL,
orderType char(10) = NULL
)
AS
BEGIN
CREATE TABLE #tempOrders (
orderId int,
orderDate datetime,
customerId int,
orderType char(10)
)
-- selects from 100 to 200 rows
INSERT INTO #tempOrders (
orderId,
orderDate,
customerId,
orderType
)
SELECT
orderId,
orderDate,
customerId,
orderType
FROM Orders
WHERE orderDate BETWEEN DATEADD(day, -1, @orderDate) AND DATEADD(day, 1, @orderDate)
-- Table scan (specific index can be added if needed)
SELECT *
FROM #tempOrders
WHERE customerId = COALESCE(@customerId, customerId)
AND orderType = COALESCE(@orderType, orderType)
END
-- Gianluca Sartori
May 12, 2009 at 8:11 am
May 18, 2009 at 11:55 am
Thank you all for excellent information! Concerning dynamic SQL, I just can't sleep at night knowing it's still out there 🙂 But the requirement is even more complicated, because we'd like to develop a generic routine that will work with any table, so the table name should be one of the parameters as well. It seems to me I would need to have one search SP per individual table and follow the catch-all query guidelines.
Hope SQL 2008 will bring smth better to the table.
Thx!
IK
May 18, 2009 at 2:04 pm
IKIK (5/18/2009)
Thank you all for excellent information! Concerning dynamic SQL, I just can't sleep at night knowing it's still out there 🙂 But the requirement is even more complicated, because we'd like to develop a generic routine that will work with any table, so the table name should be one of the parameters as well. It seems to me I would need to have one search SP per individual table and follow the catch-all query guidelines.
What is your concern with Dynamic SQL? Given that you want to determine the search conditions dynamically, and according to your statemnt here, you will also want to determine the table(s) dynamically, and in my experience things like this usually include determing the returned column set dynamically, why would you fight so hard against the natural answer to this set of requirements (dynamic SQL)?
Granted, SQL Injection is (and should be) a concern, but it is very manageable once you know how to go about it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply