OleDbCommand.Prepare makes my INSERTs slower RRS feed

  • Question

  • Hello,

    since I'm doing a lot of INSERTs per second I tried the OleDbCommand.Prepare method to see if it makes a difference. I initialize the OleDbCommand once in the constructor of my data writer, including connection, statement and parameters, and then call Prepare on it. After this I just change the respective OleDbParameter.Value properties with each call to my Write method and then call ExecuteNonQuery. The (repeatedly tested) result so far: about 500 rows/second _with_ the call to Prepare and over 1000 rows/second _without_.

    I just don't understand why I get much faster results without the Prepare. Any ideas?

    EDIT: Oh, I'm using SQL Server Express 2005 and the .NET framework 3.5 SP1.
    Thursday, November 20, 2008 2:59 PM

All replies

  • If you want to improve performance, you should use .NET Managed provider for SQL Server (SqlCommand in SqlClient namespace), not OleDbCommand. OLEDB introduces an additional layer between .NET OLEDB provider and database. In a case of SQL Server Managed provider, it uses SQL Server specific API and code should work much faster.

    Friday, November 21, 2008 11:28 AM
  • Hi,

    thanks for your answer. Changing from OLEDB to the MS SQL client doesn't change my throughput very much (it is around 1,3-1,5k entries/second). What's interesting is that using Prepare with the SqlClient doesn't affect the performance while calling it on the OleDbClient decreases it to the before stated 300-400 entries/second. So still my question is: Is that some kind of bug in the OleDbClient? It just doesn't make any sense to me.
    Friday, November 21, 2008 1:43 PM
  • It could be a bug in actual OLEDB provider or there is no support for Prepare in OPLEDB that .NET Managed provider uses to communicate with the database. Which OLEDB provider are you using in your case; can you post your connection string to show it?

    Monday, November 24, 2008 11:24 AM
  • Hm, if it was not supported it at least shouldn't slow down, right? I'm using "File=myUdl.udl" as connection string where the UDL file contains the following:

    ; Everything after this line is an OLE DB initstring
    Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=GP8;Data Source=\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=DN-CTUSCH;Use Encryption for Data=False;Tag with column collation when possible=False;

    The UDL file is BTW given, that's also why I'm using the OLEDB classes.
    Friday, November 28, 2008 12:29 PM
  •  I am looking for help and you seem to know what you are doing, can you please help. Links to code samples, any thing you think will be welcome.


    So far I am using the built-in drag and drop function of VB 2005 witch is fine wile connecting a single set of forms controls to one Table in my Data Base.

    Witch brings me to my question:

    How can I with a Click  Event change the Binding of the Controls from one to another Table in the same Data base?


    Please could you help me to accomplice this?

    Feel free to E-mail me directly to (donotspam)

    Sunday, November 30, 2008 8:57 AM