sql - What's the best way to dynamically generate a where clause? -
sql - What's the best way to dynamically generate a where clause? -
i writing stored procedure works heavy select statement. stored procedure takes in 15 parameters deed filter, of nullable.
there 2 things parameters - check if x between high , low or check if column value in y.
my main concern how write clause.
example: dynamic sql notoriously slow, don't want write clause , pass exec.
i don't want if high = null high = max
because i'll still have between statement takes processing powerfulness , has no use.
i don't want (if high = null or x <= high)
because null check still processed every row , heard rumors mess indexes.
in short, i'm looking guidance in best practice takes performance account.
dynamic sql used slow because execution plans dynamically generated sql weren't cached. no longer case , execution plans dynamic sql queries cached long query text identical. means should:
use parameters avoid parameter values altering query text try sort clauses appear in same orderas long query plans should cached (one each possible query variation) , dynamic sql won't slower normal query.
your other suggestions (setting various parameters null) avoided , may in fact perform quite badly - statements can have 1 cached query plan, optimal query plan depend on parameters , may different depending on values supplied.
for illustration 1 set of parameters might result in of table beingness returned, in case table scan might optimal. set of parameters might result in single row beingness returned in case row lookup might optimal. sql server must take cache 1 of these 2 plans (probably optimal 1 based on parameters supplied first time query run), whatever plan chooses query perform badly in opposite scenario. (this on simplification, have seen variations of happen , can have important performance impact).
ultimately mean either:
most of time query plan chosen wont optimal, or you can forcefulness sql server generate new plan each execution results in reasonable execution plan, eliminates advantages of plan cachingthe other disadvantage of alternative aproaches result in more complex query, can create hard query optimiser optimise query properly.
for these reasons i'd dynamic sql improve choice.
sql performance sql-server-2008
Comments
Post a Comment