Alternative to executing dynamic sql

Question

I currently have a 'Filter' object which corresponds to a business object. This object has properties that relate to the different ways that I want to be able to filter/search a list of such business objects. Currently these Filter objects have a method that builds the contents of a where-clause that is then passed to a SQL Server 2000 stored procedure where it is concatendated with the rest of the select query. The final string is then executed using Exec.

Currently this works fine except I worry about the performance issue with the lack of execution plan caching. In some research I have seen the use of calling sp_executesql; is this a better solution or are there better conventions for what I am doing?

Update: I think part of the issue with using sp_executesql is that based on a collection in my filter I need to generate a list of OR statements. I am not sure that the 'parameterized' query would be my solution.

example

    var whereClause = new StringBuilder();
    if (Status.Count > 0)
    {
       whereClause.Append("(");
       foreach (OrderStatus item in Status)
       {
          whereClause.AppendFormat("Orders.Status = {0} OR ", (int)item);
       }          

       whereClause.Remove(whereClause.Length - 4, 3);
       whereClause.Append(") AND ");
    }

Headroom

Answer 1

sp_executesql is better than exec because of plan reuse, and you can use parameters which help against sql injection. sp_executesql also won't cause procedure cache bloat if used correctly

take a look at these two articles

Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec

Changing exec to sp_executesql doesn't provide any benefit if you are not using parameters correctly

Fuss

Answer 2

Yes, sp_executesql will "cache" the execution plan of the query it executes.

Alternatively, instead of passing part of the query to the stored procedure, building the full query there, and executing dynamic SQL, you could build entire query on .NET side and execute it using ADO.NET command object. All queries executed through ADO.NET are getting "cached" by default.

Rochester

only will cache it is is used properly, some developers change exec to sp_executesql and don't use params inside sp_executesql and that won't help..see here blogs.lessthandot.com/index.php/DataMgmt/DataDesign/… – Fuss

+1: support building SQL statement completely out of SQL Server. better tracing/logging, better debugging, much better readability of the code. – Opulence

Answer 3

You should be using sp_executesql, simply because, as you say, the query plan is stored and future executions will be optimized. It also generally seems to handle dynamic sql better than execute.

Edra

Answer 4

Modern RDBMS'es (can't really say whether to consider SQL Server 2000 a "modern" one) are optimized for ad-hoc queries, so there's a negligible performance hit (if any). What's bothering me is that you're using sproc to construct dynamic SQL: this is a huge debugging/support PITA.

Lists

that is not true since an ad hoc query will get a different plan every single time while when you use sp_executesql with parameters you get plan reuse and the procedure cache doesn't get bloated either. You won't get conversions either...if you have nvarchar columns and execute where Col = 'bla' you get a conversion because 'bla' is varchar – Fuss

I agree with you in regards to the debugging issue however the main select query string is a fairly simple select of columns and the dynamic section is passed from an object whose responsibility is to sanitize the data and construct the sql. Therefore for most debugging issues I have one place to look which is in the Filter object. – Headroom

I think Anton meant to say parameterized ad-hoc queries which definitely do get cached and have performance similar to stored procs. – Agnes

@Wyatt Sure, parameterized only: that's kinda default nowadays, isn't it? – Lists

Having a Sproc construct dynamic SQL (safely, which is possible) is highly preferable to having client code do the same thing, which all non-RPC requests basically are. – Foal

Answer 5

sp_executesql is the better option. Have you considered not using a stored procedure for this or at least taking out some of the dynamics? I think it would be much safer from any kind of injection. I write filters much like you are talking about but i try to take care of the input in my code as opposed to in a stored procedure. I really like dynamic sql but maybe it's safer to go the extra mile sometimes.

Eberto

I would really like to use an linq-sql :0) - sadly not an option right now with this application. – Headroom