none
How Command.Prepare() works RRS feed

  • Question

  •  

    HI,

    Can somebody explaing how ADO.NET Command.Preapre() call works exactly?

    Following are the precise questions.

    - How datasource distinguish between usual Command call and Prepared Command Call?
    - Whether the compiled query is stored per connection or for the whole databasae?

    - Is it provider specifiec implementation?

     

     

     

     

    Regards,

    Anish

     

     

    Tuesday, October 7, 2008 11:55 AM

Answers

  • - From the datasource point of view there is no difference if command is prepared or not. Basically Prepare method goes through all the parameters and prepares all the required information to execute command faster at a later time when it is actually executed

    - It is not related to connection, but to the command itself.

    - Yes, it is and some providers amy not have it implemented, but call to Prepare in this case should do nothing.

     

    • Marked as answer by Michael Ani Friday, April 17, 2009 6:27 AM
    Tuesday, October 7, 2008 9:18 PM
    Moderator
  • Hi Mazur,

    Thanks for your reply.

     

    It seems to be calling prepare compiles the query at the database. In msdn it clearly says that

    Use of the Prepare Method

    The Command.Prepare method can improve the performance of parameterized commands that are repeated at your data source. Prepare instructs the data source to optimize the specified command for multiple calls. To use Prepare effectively, you need to thoroughly understand how your data source responds to the Prepare call. For some data sources such as SQL Server 2000, commands are implicitly optimized and a call to Prepare is unnecessary. For other data sources such as SQL Server 7.0, Prepare can be effective.

     

    What I am confused here is the statement "Prepare instructs the data source to optimize the specified command for multiple calls". I would like to know how Command object instructs the datasource.

     

    And I couldn't find out any article that describe this clearly.

     

    Regards,

    Anish

     

    • Marked as answer by Michael Ani Friday, April 17, 2009 6:27 AM
    Wednesday, October 8, 2008 10:18 AM

All replies

  • - From the datasource point of view there is no difference if command is prepared or not. Basically Prepare method goes through all the parameters and prepares all the required information to execute command faster at a later time when it is actually executed

    - It is not related to connection, but to the command itself.

    - Yes, it is and some providers amy not have it implemented, but call to Prepare in this case should do nothing.

     

    • Marked as answer by Michael Ani Friday, April 17, 2009 6:27 AM
    Tuesday, October 7, 2008 9:18 PM
    Moderator
  • Hi Mazur,

    Thanks for your reply.

     

    It seems to be calling prepare compiles the query at the database. In msdn it clearly says that

    Use of the Prepare Method

    The Command.Prepare method can improve the performance of parameterized commands that are repeated at your data source. Prepare instructs the data source to optimize the specified command for multiple calls. To use Prepare effectively, you need to thoroughly understand how your data source responds to the Prepare call. For some data sources such as SQL Server 2000, commands are implicitly optimized and a call to Prepare is unnecessary. For other data sources such as SQL Server 7.0, Prepare can be effective.

     

    What I am confused here is the statement "Prepare instructs the data source to optimize the specified command for multiple calls". I would like to know how Command object instructs the datasource.

     

    And I couldn't find out any article that describe this clearly.

     

    Regards,

    Anish

     

    • Marked as answer by Michael Ani Friday, April 17, 2009 6:27 AM
    Wednesday, October 8, 2008 10:18 AM
  • Hi, am just curious as prepare command does not throw exception in visual studio 20120 and above is there any other command alternative to prepare that can be used. Am trying to using it instead of execution.

    Tuesday, August 6, 2013 8:20 PM