Performance of parameterized queries

I've come up with a query which allows me to perform fairly dynamic
queries depending on which parameters are set when a stored procedure
is called, but I'm not sure if what I'm doing is a performance no-no.
Here is an example:
CREATE PROCEDURE p_TradesGetForProcessing
@.tradeID int = null,
@.underlyingSymbol varchar(20) = null,
@.startTradeDate datetime = null,
@.endTradeDate datetime = null,
@.expirationDate datetime = null
AS
SELECT TradePL.TradeID, TradeID.OptionID,
FROM OptionTradeViewForSelectingTradesToProce
ss
WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
and (@.underlyingSymbol IS NULL or UnderlyingSymbol = @.underlyingSymbol)
and (@.startTradeDate is NULL or TradeDay >= @.startTradeDate)
and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
In essence, this allows me to pass in some or all of the filter
parameters. I was hoping that this would be fairly optimal in that the
optimizer has a change to compile the query. What I don't know is if
there is going to be a huge amount of overhead and I'm better off doing
it some other way.Whether some other method will be performant can be determined only by
testing the various methods. However, for a variety options, see:
http://www.sommarskog.se/dyn-search.html
Anith|||bpeikes (ben@.peikes.com) writes:
> I've come up with a query which allows me to perform fairly dynamic
> queries depending on which parameters are set when a stored procedure
> is called, but I'm not sure if what I'm doing is a performance no-no.
> Here is an example:
> CREATE PROCEDURE p_TradesGetForProcessing
> @.tradeID int = null,
> @.underlyingSymbol varchar(20) = null,
> @.startTradeDate datetime = null,
> @.endTradeDate datetime = null,
> @.expirationDate datetime = null
> AS
> SELECT TradePL.TradeID, TradeID.OptionID,
> FROM OptionTradeViewForSelectingTradesToProce
ss
> WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
> and (@.underlyingSymbol IS NULL or UnderlyingSymbol = @.underlyingSymbol)
> and (@.startTradeDate is NULL or TradeDay >= @.startTradeDate)
> and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
> and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
> In essence, this allows me to pass in some or all of the filter
> parameters. I was hoping that this would be fairly optimal in that the
> optimizer has a change to compile the query. What I don't know is if
> there is going to be a huge amount of overhead and I'm better off doing
> it some other way.
In SQL 2000, the optimizer have no idea of the values, and will arrange
for a one-size-fits all plan. Which in this case is mostly like to
be a table scan, because of the >= conditions. Had you only had =
conditions and all columns had been indexed, it's possible that SQL
Server would pick index concatenation.
For SQL 2005 you can add the hint OPTION (RECOMPILE) to force statement
recompilation - SQL Server will then look at the actual values and use
them for the plan.
I have a longer article on the topic of dynamic searches on my web
site: http://www.sommarskog.se/dyn-search.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx|||bpeikes wrote:
> SELECT TradePL.TradeID, TradeID.OptionID,
> FROM OptionTradeViewForSelectingTradesToProce
ss
> WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
> and (@.underlyingSymbol IS NULL or UnderlyingSymbol =
> @.underlyingSymbol) and (@.startTradeDate is NULL or TradeDay >=
> @.startTradeDate)
> and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
> and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
You're not likely to get index optimization using this method. The only
way to tell for sure is to check the execution plans for each version of
the query, but I'm fairly sure you'll see a lot of index scans (assuming
indexes are in place). What you might want to do is have this procedure
call other procedures depending on the parameters passed. I don't know
if you want to allow all NULL values passed in. If so, the query could
do away with the entire WHERE clause. Once you determine the valid
versions of the query, you can validate the parameters in the main proc
and then design the necessary indexes to support those queries.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi,
That is a performance no-no, you'll not get good if any index usage on that
query because of the IS NULL and OR stuff.
For search stuff, like below, you should look at using either a ton of IF
ELSE statements or preferably dynamic parameterised SQL...
Build the SQL only for the parameters that are passed, if tradeID is the
only parameter passed then do this...
SET @.nsql = '
select...
from ..
where TradeID = @.tradeID'
exec sp_executesql @.nsql,
N'@.tradeID int',
@.tradeID
The execution plan will be cached and is reusable so it isn't compiled each
time.
Make sure you use parameters as i've done rather than hard code values and
you won't have a problem with injection.
The one draw back is that you will need to permission access to the base
tables/views used in the dynamic SQL rather than execute permission on the
proc; you can get round this problem of security using application roles in
your application.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"bpeikes" <ben@.peikes.com> wrote in message
news:1131574058.424530.205360@.g43g2000cwa.googlegroups.com...
> I've come up with a query which allows me to perform fairly dynamic
> queries depending on which parameters are set when a stored procedure
> is called, but I'm not sure if what I'm doing is a performance no-no.
> Here is an example:
> CREATE PROCEDURE p_TradesGetForProcessing
> @.tradeID int = null,
> @.underlyingSymbol varchar(20) = null,
> @.startTradeDate datetime = null,
> @.endTradeDate datetime = null,
> @.expirationDate datetime = null
> AS
> SELECT TradePL.TradeID, TradeID.OptionID,
> FROM OptionTradeViewForSelectingTradesToProce
ss
> WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
> and (@.underlyingSymbol IS NULL or UnderlyingSymbol = @.underlyingSymbol)
> and (@.startTradeDate is NULL or TradeDay >= @.startTradeDate)
> and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
> and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
> In essence, this allows me to pass in some or all of the filter
> parameters. I was hoping that this would be fairly optimal in that the
> optimizer has a change to compile the query. What I don't know is if
> there is going to be a huge amount of overhead and I'm better off doing
> it some other way.
>|||Tony Rogerson wrote:
> Hi,
> That is a performance no-no, you'll not get good if any index usage
> on that query because of the IS NULL and OR stuff.
> For search stuff, like below, you should look at using either a ton
> of IF ELSE statements or preferably dynamic parameterised SQL...
> Build the SQL only for the parameters that are passed, if tradeID is
> the only parameter passed then do this...
> SET @.nsql = '
> select...
> from ..
> where TradeID = @.tradeID'
> exec sp_executesql @.nsql,
> N'@.tradeID int',
> @.tradeID
>
To add to what Tony has described, you can use dynamic SQL and send in
all parameters, regardless of whether those parameters exist in the SQL
statement. Assuming your database can support dynamic SQL, you could use
something like the following even if the SQL statement only contains
@.Param1:
Exec sp_executesql @.nvcSql, N'@.Param1 INT, @.Param2 INT, @.Param3 INT',
@.Param1, @.Param2, @.Param3
David Gugick
Quest Software
www.imceda.com
www.quest.com