Post reply

Prepared Statements on SQL Server

  • March 20, 2004 at 2:57 pm

    #84048

    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

     

  • March 22, 2004 at 9:14 am

    #499811

    In my experience, the prepare statement is not used by multiple connections or applications. When a prepare was sent to the server, a Handle was assigned to the application and connection. This required additional network traffic and could degrade performance if not reused. We used it when we were running the same stored procedure with multiple sets of parameters from the same application and connection. I have not used this recently.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply