none
Accessing Oracle from .Net - Best provider and technique RRS feed

  • Question

  • Hi All,

    1. what is the best provider and technique to use when developing a .Net application which will access an Oracel DB read/write (ADO.Net or Oracle ODAC )?

    2. is it recommended to use the oracel transaction (oracleconnection.begintransaction) in the code when updating records?

    3. what is the best technique to update multiple records at the same time, i used :

        1) the IN caluse (update table set column = value where ID in ('100','101',....))

        2) update the recored row by row

       but it is very slow in both techniques !!

    4. is it recommended to write my code as the follwoing:

                    string[] strIDsList =  {"100,101,102"};

                    OracleConnection conn = new OracleConnection(strConnectionString);
                    conn.Open();
                    OracleTransaction trans = conn.BeginTransaction();
                    OracleCommand cmd = conn.CreateCommand();
                    cmd.Transaction = trans;

                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "UpdateSubscribers";

                    foreach (string id in strIDsList)
                    {                   
                        cmd.Parameters.AddWithValue("@ID", id);
                        cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }

                    trans.Commit();

    please advise ?

    Wednesday, August 17, 2011 3:23 PM

Answers

All replies

  • Use ODP.NET for the Oracle Provider:

    http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

    A transaction isn't generally necessary unless you have multiple INSERT, UPDATE or DELETE statements that are logically related to one another.

    If you can update all rows in a single statement (using IN or just a standard WHERE clause) then that would be the most efficient method.

    With respect to question #4, you probably do not need a transaction since there is only one call to the database to execute a stored procedure. I can't see your stored procedure code so I can't really comment on that. If you have a fixed number of parameters then I would use separate parameter arguments rather than a list. This is usually more efficient in Oracle since it performs some caching of the statements. If the number of parameters is dynamic then using a list and parsing in the stored procedure would be fine, although not as efficient.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, August 17, 2011 5:10 PM
  • Thanks Paul P Clement IV.

    the stored procedure code is the same: update table set column = value where ID = @ID

    i used this logic to avoid using the IN clause (update table set column = value where ID IN (ID1, ID2,...) ) because as i know the IN clause is killing the performance and its slow.

    about my parametrs, they are dynamic (the count and the values of the parameters)

    appreciated

    Wednesday, August 17, 2011 9:21 PM
  • Thanks Paul P Clement IV.

    the stored procedure code is the same: update table set column = value where ID = @ID

    i used this logic to avoid using the IN clause (update table set column = value where ID IN (ID1, ID2,...) ) because as i know the IN clause is killing the performance and its slow.

    about my parametrs, they are dynamic (the count and the values of the parameters)

    appreciated


    Regarding this question, there are many ways to achieve it. Normally, you can pass this value from the UI layer. Eg. TextBox.Text, Combox.Selected or others.

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 19, 2011 2:26 PM
  • It is not about how to pass the value, its all about the performance.

    I used the ADO.NET (Oracle Client) with IN Caluse but it is very slow (it takes 5-6 minutes to update 1000 records), i am looking for the best performance (shorter time) way

    waiting for a definitive answer :)

    thank you

    Saturday, August 20, 2011 10:23 PM
  • Hello again,

    Honestly, there are no bible in the development. But there are many expeirence and suggestions. I think you can check them during development. E.G. IN/NOT IN maybe a little peformance as sub-query statement. I suggest you can check EXISTS/NOT EXISTS keyword and try it again. I suggest you can check SQL Tips. http://harriyott.com/2006/01/sql-server-performance-tips.aspx

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Larcolais Gong Thursday, August 25, 2011 9:50 AM
    Sunday, August 21, 2011 12:09 PM
  • Updating 1,000 rows would be considered a batch operation. I would not expect a task such as this to be particularly fast.

    That being said, you may be able to significantly improve performance by using an ODP.NET Associative Array parameter:

    http://weblogs.asp.net/ricardoperes/archive/2009/05/14/odp-net-associative-arrays.aspx

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Larcolais Gong Thursday, August 25, 2011 9:50 AM
    Monday, August 22, 2011 3:29 PM