Is LINQ To SQL's use of sp_executesql performant?

As far as I know, if I put some where clause there, I can see the SQL Server Profiler shows the statement was using sq_executesql; is that a good way?

The reason I am asking is because my supervisor believes开发者_C百科 sp_executesql reuses the SQL statements and minimize performance issue.

How do I address her concern about performance issue on LINQ To SQL?

Linq-to-Sql is not good for high performance: it optimizes for convenience. If you need high performance, go with the standard ADO.NET Connection, Command, and DataReader.

Having said that, performance is irrelevant for the majority of applications, so Linq-to-Sql is veyr useful. :)

If you are asking if sq_executesql is performant, then yes it is. From MSDN's article on sp_executesql:

sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

It is a bit tough to answer this as you're asking two things. Even if all things were equal, Linq to SQL is not as flexible as creating POCO - which once you pay the price, is the better performing way to access your data (most lightweight, fast, and flexible). This is what Andomar suggests with a DataReader. If you're asking about using sq_executesql vs concatenating a string to send to your command, then EddieGroves has that answer. I'd only like to add that sq_executesql is more secure since you'll add your variables as parameters to the call - less chance for a SQL injection. Concatenating the strings would also force SQL to compile the statement every time (so it's slower). If you're comparing sq_executesql to CRUD stored procedures, then you're right. CRUD would perform better and also take care of the potential SQL injections. SQL server would compile the stored procedures once.