Does anyone have any experience using Prepared Statements on SQL Server, specifically through .NET?
Most pertinent question I have is whether a prepared statement (executing .Prepare() before running the SQL command) is good for performance if the connections are not kept opened.
In other words, are prepared statements re-used ACROSS connections, or do they only provide a performance benefit if the connection is kept open, and multiple prepared statements are sent through the same connection?
If I try using them and run a trace I see the following:
sql_unprepare 34
sql_reset_connection
after each and every single query. The sp_reset_connection is expected as the connection is closed and returned to the pool. But what I'm not sure is good for performance is the sql_unprepare which sounds like the prepared statement is thrown out/prepared each time I run the query.
Any ideas? Not much about this on the net.
Thanks