Disadvantages of Dynamic Query in Sqlserver 2005?
I use a lot of dynamic queries in my database for procedures because my filter is not fixed, so I took the @filter parameter as a parameter and passed the procedure.
Declare @query as varchar(8000)
Declare @Filter as varchar(1000)
set @query = 'Select * from Person.Address where 1=1 and ' + @Filter
exec(@query)
Like my filter contains any field from the table to compare.
Will it affect my work or not? is there an alternative way to achieve this type of thing
a source to share
For performance, the only question is whether the database can reuse the existing plan or not.
In simple terms, you can see this as the database caches the query plan using the sql statement as the key. As soon as you change the sql statement it will not be in the cache and a new plan should be generated.
So, we create dynamic expressions like
"SELECT * FROM table WHERE param = @paramvalue"
is more likely to be in the cache than
"SELECT * FROM table WHERE param = '" + variable + "'"
You must also add the schema name to tablenames in the query (like dbo.table). Otherwise, the plan will not be reused if it is executed by different logins.
a source to share
As long as no other joins need to be done dynamically to check the value of a parameter that may have a value, and the only dynamic part is the WHERE clause, it can also be a static query that has all the possible parameters. Thus, you have the following scenarios:
If you want to check for values that can be anything (negative / zeros / zeros / positive / blank strings / etc.), you need to use an auxiliary parameter like @ signifficant_param1 and also the original @ param1 value.
[...]
WHERE
(@signifficant_param1=0 or (@param1 is null and field1 is null) or @param1=field1)
AND (@signifficant_param2=0 or (@param2 is null and field2 is null) or @param2=field2)
//etc
[...]
This is the most versatile article I could imagine. This will basically check the value @signifficant_param
. If this parameter is to be taken into account, it will be equal to 1, the first part of the condition will be false, and the second part (parameter check) will take place. In the second step, if @param
it is NULL, you are looking for all null values field
, and you cannot compare null to null because they are not equal. It then checks for the correct non-empty values.
If, on the other hand, the values in field
cannot be null or can not be negative, you do not need it @signifficant_param
, because you can make a rule, for example, if @param
it is null, then this value is not significant (in the previous case, you would need look for all null values), you could use the following:
[...]
WHERE
field1=case when @param1 is null then field1 else @param1 end --first way with case statement
and (@param2 is null or field2=@param2) --second way with boolean logic
[...]
a source to share
There really is nothing wrong with dynamic queries. But the way you're going to do it is scary. This implies that your parameters will be part of @Filter, which simply requests a SQL injection attack. It also means that your query plan is unlikely to be reused, which can result in high CPU and low throughput due to redundant re-compilation of queries.
You need to make sure that the dynamic SQL you are building is parameterized correctly. You also need to make sure that when you access it using ADO.NET code (or any other data access technology that you can use), you are using a SqlParameter object (or equivalent).
a source to share